Home » SQL & PL/SQL » SQL & PL/SQL » Error/Message handling
Error/Message handling [message #217087] Wed, 31 January 2007 11:19 Go to next message
yerics
Messages: 89
Registered: August 2006
Member
In a package I have 2 insert statements. Both are independent of each other. I want to have a custom error handling whereby
if there are no rows found (sql%rowcount) in either of the inserts then display the message and continue processing the next insert statement. However I am at loss at how to do it.

1) I used raise_application_error and if the 1st table does not have any rows to insert, it stops there and does not proceed to next.
2) I do not want to use dbms_output.put_line because package is scheduled using an external scheduler at midnight.
3) I can just log it without raising it but someone has to look at the table to see whether package ran successfully for both inserts and it would mean delays in acting upon it.

Any leads?

Regards,
Re: Error/Message handling [message #217088 is a reply to message #217087] Wed, 31 January 2007 11:26 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
You could write a message to the error table and then have another job which checks the error table. The other job could then call raise_application_error.
Re: Error/Message handling [message #217089 is a reply to message #217087] Wed, 31 January 2007 11:29 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Would pragma exception_init work..
I am trying it out as I post...

Regards,
Re: Error/Message handling [message #217094 is a reply to message #217087] Wed, 31 January 2007 11:56 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
It did not work as expected.. here is the code.. If any can check..

create or replace PROCEDURE get_parm_table(v_table_name IN varchar2)
IS
v_ins_stmt varchar2(1000);
v_qtr varchar2(2);
e_norows exception;
e_user_norows exception;
-- pragma exception_init(e_norows,-20001);
-- pragma exception_init(e_user_norows,-20002);
b_userids_stat boolean;
b_users_stat boolean;
BEGIN
BEGIN
v_ins_stmt :=
'INSERT INTO a_bkp_'|| v_table_name||'
(
x,y,z
SELECT
x,y,sysdate
FROM
sec_userids';
execute immediate v_ins_stmt;
b_userids_stat := sql%rowcount > 0;

IF ( not b_userids_stat ) THEN
raise e_norows;
END IF;

EXCEPTION
WHEN e_norows THEN
proc_logerror('Pkg-- start_archive / Error-- No Rows Found in SEC_USERIDS '||sql%rowcount ||' rows inserted');
rollback;
WHEN others THEN
proc_logerror('Pkg-- start_archive / Error-- '||SQLERRM);
raise;
END;
BEGIN
v_ins_stmt :=
'INSERT INTO b_bkp_'|| v_table_name||'
(
n,m,o,p
)
SELECT
n,m,o,sysdate
FROM
sec_users';
execute immediate v_ins_stmt;
b_users_stat := sql%rowcount > 0;
commit;

IF ( not b_users_stat ) THEN
raise e_user_norows;
END IF;

EXCEPTION
WHEN e_norows THEN
proc_logerror('Pkg-- start_archive / Error-- No Rows Found in SEC_USERS '||sql%rowcount ||' rows inserted');
rollback;
WHEN others THEN
proc_logerror('Pkg-- start_archive / Error-- '||SQLERRM);
raise;
END;
END;
Re: Error/Message handling [message #217096 is a reply to message #217087] Wed, 31 January 2007 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You do not pay by the character to post here.
>It did not work as expected.
I refuse to try to guess what may or may not be a problem.
Here is a picture of my car. It does not work as expected.
Please tell me what is wrong with it.
Re: Error/Message handling [message #217098 is a reply to message #217087] Wed, 31 January 2007 12:07 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
I don't know how having a named exception handler would make any difference. It is still doing a "raise", so it will still terminate the program with an error.
Re: Error/Message handling [message #217099 is a reply to message #217094] Wed, 31 January 2007 12:13 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Senior Member..

I think I list down the expectations way on top my first post."
I want to have a custom error handling whereby
if there are no rows found (sql%rowcount) in either of the inserts then display the message and continue processing the next insert statement"

Even with this code, It still stops at the first insert when the rowcount is 0 and does not proceed to the 2nd one.Now I have substituted raise_application_error with logging into a table.so it goes off silently. But I want to display the message without using dbms_output as this procedure is scheduled.

About your car though.. i dont know what the problem could be Smile
Re: Error/Message handling [message #217110 is a reply to message #217087] Wed, 31 January 2007 12:53 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
You could create two new procedures; one for each INSERT.
You could email when no rows get inserted instead of raising error.
Re: Error/Message handling [message #217224 is a reply to message #217087] Thu, 01 February 2007 04:17 Go to previous messageGo to next message
Arun Srinath
Messages: 12
Registered: January 2005
Junior Member
Are you sure the control is going to correct handler.There may be some other error which could lead the control to goto when others handler where you have raise statement.This will propogatee the exception to next block and your 2nd insert wont work.

Hope this helps

Thanks
Arun
Re: Error/Message handling [message #217288 is a reply to message #217224] Thu, 01 February 2007 08:39 Go to previous messageGo to next message
yerics
Messages: 89
Registered: August 2006
Member
Thanks..
For now I will go along with logging the errors into an error table.

Regards,
Re: Error/Message handling [message #217523 is a reply to message #217087] Fri, 02 February 2007 11:35 Go to previous message
nvs_krishna
Messages: 3
Registered: February 2007
Location: Atlanta
Junior Member

Try to store the record count in a variable and keep the exception at the last depending on the values in the variables.

for example...I stored the SQL%rowcount in var_err1 and var_err2

declare
var_err1 varchar2(1000);
var_err2 varchar2(1000);
e_norows exception;
begin
SELECT 'Error1' into var_err1 FROM DUAL;
var_err1 := sql%rowcount;
SELECT 'Error2' into var_err2 FROM DUAL;
var_err2 := sql%rowcount;
if (var_err1 = 1 or var_err2 = 1) then
raise e_norows;
end if;
EXCEPTION
WHEN e_norows THEN
dbms_output.put_line('No rows rows inserted');
WHEN others THEN
dbms_output.put_line('The following Erro has occured ' || SQLERRM);
end;
Previous Topic: How to create a directory object dynamically (Merged)
Next Topic: Minimal row that is not locked
Goto Forum:
  


Current Time: Sat Dec 03 07:55:04 CST 2016

Total time taken to generate the page: 0.13342 seconds