Hi,
I have one stored procedure when I am passing one input parameter. With that input parameter, if there is no_data_found then I am raising the exception trying to store no_data_found error in that error logs table but I am not able to do it. Please find my code below,
Stored Procedure:
create or replace procedure dumm_proc (p_opportunity_number cct_opportunity.opportunity_number%type)
as
v_oppo_id varchar2(50);
l_message varchar2(50) := sqlerrm;
l_code varchar2(50) := sqlcode;
begin
select opportunity_id into v_oppo_id
from cct_opportunity
where opportunity_number = p_opportunity_number;
exception
when no_data_found then
xxcct_error_logs(cct_error_seq.nextval,null,v_oppo_id,null,null,l_message,l_code,'dumm_proc',null,sysdate,user,sysdate,user);
raise;
when others then
xxcct_error_logs(cct_error_seq.nextval,null,v_oppo_id,null,null,l_message,l_code,'dumm_proc',null,sysdate,user,sysdate,user);
raise;
end;
Storing the values into the table using another error log procedure (xxcct_error_logs) but always it stores with normal, successful completion.
Please suggest how can I store no_data_found error by calling error_logs procedure.
Thaks.
You assign the values to l_code and l_message at the start of the procedure. When everything's good. So when you reference these in the exception handler, they still have these values!
To get the error information, you need to call sqlerrm and sqlcode in the exception block itself:
declare
l_message varchar2(50) := sqlerrm;
l_code varchar2(50) := sqlcode;
begin
dbms_output.put_line(l_code || ' ' || l_message);
raise no_data_found;
exception
when no_data_found then
dbms_output.put_line(l_code || ' ' || l_message);
dbms_output.put_line(sqlcode || ' ' || sqlerrm);
end;
/
0 ORA-0000: normal, successful completion
0 ORA-0000: normal, successful completion
100 ORA-01403: no data found