Go up

Next thread:	Where to look about procedure progress
Prev thread:	Database Link Creation

Message	Score	Author	Date
Hi, I have procedure to analyze all the tables ......		kishore pagadala	Mar 11, 2010
why not you go for "alter system set events '155......		Gajendra Bhende	Mar 11, 2010
Hi, looks like you didn't get my point. I want ......		kishore pagadala	Mar 11, 2010
Try Exception When Others Then Regards, Nikh......		nikhita sivasree	Mar 11, 2010
Hi, I don't want to use WHEN OTHERS because I w......		kishore pagadala	Mar 11, 2010
Hi Kishore, The problem is in the cursor. If yo......		Philip Wisse	Mar 11, 2010
Hi Philip, Sorry to bother. Could you please ex......		kishore pagadala	Mar 11, 2010
Hi Kishore, LOOP Open the cursor. Fetch the f......		Philip Wisse	Mar 11, 2010

Follow up by mail

Click here

---

Subject:	Handling ORA-01555: in a procedure
Author:	kishore pagadala, India
Date:	Mar 11, 2010, 182 days ago
Os info:	IBM AIX 5.0
Oracle info:	10g
Message:	Hi, I have procedure to analyze all the tables in schema based on frequency column value. while executing this procedure I am getting few errors apart from ORA-01555. Now I want to catch only ORA-01555 error and insert those details into a log table. My aim is when I get ORA-01555 error, skip that particular record and continue for next record. I have added exception part for this, but somehow it is not working. Please help me to resolve this. below is my procedure code. CREATE OR REPLACE PROCEDURE analyze_script AS current_analyze_date DATE; ana_tbl_list_rec ana_tbl_list%ROWTYPE; CURSOR cur_analize_list(p_ana_date DATE) IS SELECT * FROM ana_tbl_list WHERE next_current_analyze_date <= p_ana_date; snapshot_too_old EXCEPTION; PRAGMA EXCEPTION_INIT (snapshot_too_old, -1555); BEGIN current_analyze_date := trunc(sysdate); --Write start statement to status tab FOR ana_tbl_list_rec IN cur_analize_list(current_analyze_date) LOOP BEGIN IF ana_tbl_list_rec.partitioned = 'Y' THEN DBMS_STATS.gather_table_stats(ownname => ana_tbl_list_rec.owner, tabname => ana_tbl_list_rec.table_name, CASCADE => TRUE, granularity => 'PARTITION', DEGREE => ana_tbl_list_rec.DEGREE, estimate_percent => ana_tbl_list_rec.estimate_size); ELSIF ana_tbl_list_rec.partition_name IS NULL THEN DBMS_STATS.gather_table_stats(ownname => ana_tbl_list_rec.owner, tabname => ana_tbl_list_rec.table_name, CASCADE => TRUE, estimate_percent => ana_tbl_list_rec.estimate_size, DEGREE => ana_tbl_list_rec.DEGREE); ELSE DBMS_STATS.gather_table_stats(ownname => ana_tbl_list_rec.owner, tabname => ana_tbl_list_rec.table_name, partname => ana_tbl_list_rec.partition_name, estimate_percent => ana_tbl_list_rec.estimate_size, DEGREE => ana_tbl_list_rec.DEGREE); END IF; UPDATE ana_tbl_list SET last_analyzed = current_analyze_date, next_anal_date = trunc(sysdate + ana_tbl_list_rec.ana_frequence) WHERE anatable_id = ana_tbl_list_rec.anatable_id; EXCEPTION WHEN SNAPSHOT_TOO_OLD THEN INSERT INTO analyze_table_log(table_name,sql_errorcode,sql_errormsg) VALUES (ana_tbl_list_rec.table_name,'1555','SNAPSHOT_TOO_OLD'); END; END LOOP; COMMIT; END analyze_script; /
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: Handling ORA-01555: in a procedure
Author:	Gajendra Bhende, India
Date:	Mar 11, 2010, 182 days ago
Message:	why not you go for "alter system set events '1555 trace name errorstack level 3';" so that it is generated ora-0155 error in trace file and you can easly find out that error. Regards Gajendra B.
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: Handling ORA-01555: in a procedure
Author:	kishore pagadala, India
Date:	Mar 11, 2010, 182 days ago
Message:	Hi, looks like you didn't get my point. I want the procedure to continue even after throwing the ORA-01555 error. In order to catch that error (only 01555 not WHEN OTHERS), I need an exception block to log this error into log_table and crontinue to next record. Let me know if u need additional info.
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: Handling ORA-01555: in a procedure
Author:	nikhita sivasree, India
Date:	Mar 11, 2010, 182 days ago
Message:	Try Exception When Others Then Regards, Nikhita.
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: Handling ORA-01555: in a procedure
Author:	kishore pagadala, India
Date:	Mar 11, 2010, 182 days ago
Message:	Hi, I don't want to use WHEN OTHERS because I want this exception block only for ORA-01555 not any other exceptions.
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: Handling ORA-01555: in a procedure
Author:	Philip Wisse, Netherlands
Date:	Mar 11, 2010, 182 days ago
Message:	Hi Kishore, The problem is in the cursor. If you close this after fetching '1555' exceptions will not occur any more. Regards, Philip
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: Handling ORA-01555: in a procedure
Author:	kishore pagadala, India
Date:	Mar 11, 2010, 182 days ago
Message:	Hi Philip, Sorry to bother. Could you please explain in detail. I didn't get your point. /*If you close this after fetching '1555' exceptions will not occur any more. */ please let me know how to fetch the '01555' error.
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here

---

Subject:	Re: Handling ORA-01555: in a procedure
Author:	Philip Wisse, Netherlands
Date:	Mar 11, 2010, 182 days ago
Message:	Hi Kishore, LOOP Open the cursor. Fetch the first record into local memory. If no first record, exit loop. Then close the cursor. Then process the record. END LOOP Regards, Philip
Your rating?:	This reply is Good Excellent
Goto:	Reply - Top of page

If you think this item violates copyrights, please click here