Dynamic SQL and error handling
I have dynamic SQL that I am executing in a PL/SQL block. The problem I am facing is when I get a NO_DATA_FOUND error in the first one, it hits the EXCEPTION block directly. When I get the first NO_DATA_FOUND, I want to continue to the second dynamic SQL and so on.
Idea1: I can go to the EXCEPTION block, catch the NO_DATA_FOUND error and code there but it seems convoluted to me. I can create a label and use GO TO the label from the EXCEPTION block but how do I distinguish between the NO_DATA_FOUND errors in the first and second statement.
Idea2: I can put each dynamic SQL within a block and put a EXCEPTION in the block something like this:
begin
execute immediate dynamiSQL;
exception
when no_data_found then
null;
end;
I would like to hear some better ideas.
--------------------------
declare
vOne varchar2(1) :='';
vCriteria := 'A';
begin
execute immediate 'select Col1 from Tbl1 where Criteria1= ' || vCriteria into vOne;
if trim(vOne) is null then
execute immediate 'select Col2 from Tbl1 where Criteria2= ' || vCriteria into vOne;
end if;
if trim(vOne) is null then
execute immediate 'select Col3 from Tbl1 where Criteria3= ' || vCriteria into vOne;
end if;
if trim(vOne) is null then
dbms_output.put_line('Xyz'
else
dbms_output.put_line(vOne)
end if;
end;
exception
when others then
raise_application_error(sq
end;
--------------------------
Idea1: I can go to the EXCEPTION block, catch the NO_DATA_FOUND error and code there but it seems convoluted to me. I can create a label and use GO TO the label from the EXCEPTION block but how do I distinguish between the NO_DATA_FOUND errors in the first and second statement.
Idea2: I can put each dynamic SQL within a block and put a EXCEPTION in the block something like this:
begin
execute immediate dynamiSQL;
exception
when no_data_found then
null;
end;
I would like to hear some better ideas.
--------------------------
declare
vOne varchar2(1) :='';
vCriteria := 'A';
begin
execute immediate 'select Col1 from Tbl1 where Criteria1= ' || vCriteria into vOne;
if trim(vOne) is null then
execute immediate 'select Col2 from Tbl1 where Criteria2= ' || vCriteria into vOne;
end if;
if trim(vOne) is null then
execute immediate 'select Col3 from Tbl1 where Criteria3= ' || vCriteria into vOne;
end if;
if trim(vOne) is null then
dbms_output.put_line('Xyz'
else
dbms_output.put_line(vOne)
end if;
end;
exception
when others then
raise_application_error(sq
end;
--------------------------
ASKER
Yea, I already implemented it by putting each SQL statement in its own block. I was just wondering if there are better/cleaner ways.
That is the best way: put each SQL statement or query in its own block, and handle each exception locally. Don't just depend on one "catch all" exception block at the end of your procedure.
Yes, nested PL/SQL blocks is the way to do this.
You can nest as many as you want.
You can nest as many as you want.
declare
junk varchar2(10);
begin
select 'Hello' into junk from dual where 1=1;
dbms_output.put_line(junk);
--no_data_found
begin
select 'Hello' into junk from dual where 1=2;
exception when no_data_found then
dbms_output.put_line('No data');
end;
--continue on to here
select 'World' into junk from dual where 1=1;
dbms_output.put_line(junk);
end;
/ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
And I agree that other implementations seem rather convoluted.