DDL
dbms_sql.last_error_position can be used to find out where a DDL statement has an error:
set serveroutput on
declare
c integer;
sql_text varchar2(1000) := q'[
create table tq84_dup_col (
a varchar2(10),
b number ( 4),
c date ,
d integer ,
c varchar2( 5),
f number ( 2)
)
]';
begin
c := dbms_sql.open_cursor();
dbms_sql.parse(c, sql_text, dbms_sql.native);
exception when others then
--
-- Print the first 20 characters after the error
--
dbms_output.put_line(substr(sql_text, dbms_sql.last_error_position, 20));
end;
/
DML
For DML statements, dbms_sql.execute needs to be called as well:
declare
c integer;
r integer;
sql_text varchar2(1000) := q'[insert into tq84_ora_01438_test values(1, 12, 123, 1234, 12345)]';
begin
c := dbms_sql.open_cursor();
dbms_sql.parse(c, sql_text, dbms_sql.native);
r := dbms_sql.execute(c);
exception when others then
dbms_output.put_line(dbms_sql.last_error_position);
dbms_output.put_line(substr(sql_text, dbms_sql.last_error_position, 20));
end;
/