Question: I am using the PL/SQL execute
immediate to generate dynamic SQL, but I don't know how to rap an
error from dynamic SQL. I know that it is important to check for
any non zero error code from PL/SQL when sending SQL to the database
for execution, but I don't know the syntax to check for error in a
PL/SQL execute immediate statement.
Answer:
Inside PL/SQL you can trap and display any SQL error by testing the
SQLCODE. It is a best practice to always use the "when others" so
that you trap any possible unexpected error from the execute
immediate in your PL/SQL.
See my notes
here, with working examples of execute immediate.
To trap an error when executing PL/SQL in a function or stored
procedure, use the "when others" clause.
In a single statement, you can trap a SQL error with
execute immediate for both DML, DDL and SQL statements. Here we
execute a DDL statement using execute immediate in PL/SQL and trap
any non zero SQLCODE from the database:
begin
execute immediate 'drop table TABLE1';
exception when others then null;
end;
In a PL/SQL cursor loop you can capture execute immediate
errors like this:
for i in mycir
loop
begin
execute immediate mystring
(i);
exception
when others then myerror_proc mymessage, sqlcode, sqlerrm)
end;
end loop;
Follow the link for more
information on
Late Binding and Runtime Binding in PL/SQL
For more information, see
Dr. Hall's book on Advanced PL/SQL and download the working
examples.
