How to capture the query throwing ORA-00900: invalid SQL statement
ORA-00900 error when running a long SQL script during deployment. In this video I show the steps to easily identify the running SQL using the "after servererror on database" trigger.
CREATE TABLE "SYS"."DBA_SERVERERROR"
( "ID" NUMBER GENERATED ALWAYS AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER NOCYCLE NOKEEP NOSCALE NOT NULL ENABLE,
"ERROR_DATE" DATE NOT NULL ENABLE,
"ORA_SYSEVENT" VARCHAR2(128 BYTE),
"ORA_LOGIN_USER" VARCHAR2(128 BYTE),
"ORA_SERVER_ERROR" VARCHAR2(4000 BYTE),
"SID" NUMBER,
"HOST" VARCHAR2(256 BYTE),
"IP" VARCHAR2(15 BYTE),
"MODULE" VARCHAR2(4000 BYTE),
"SERVERHOST" VARCHAR2(256 BYTE),
"SQL" CLOB,
"OS_USER" VARCHAR2(255 BYTE),
"SESSION_USER" VARCHAR2(255 BYTE)
);
create or replace NONEDITIONABLE TRIGGER SYS.TRIG_AUDIT_ORA_00900
after servererror on database
declare
v_sql_text ora_name_list_t;
v_sql clob;
v_n number;
SQL_CODE VARCHAR2(100);
SQL_ERRM VARCHAR2(200);
SQLID VARCHAR2(20);
begin
--If you find a huge number of irrelevant errors, you might want to filter them out here.
--ORA-00900 invalid SQL statement when Explain Plan(F10) in SQL Developer
--ALTER TABLE sample ADD (CONSTRAINT sample_pk PRIMARY KEY (id));
if ( is_servererror(900) ) then
v_n := ora_sql_txt(v_sql_text);
for i in 1 .. v_n loop
v_sql := v_sql || v_sql_text(i);
end loop;
SYS.DBMS_SYSTEM.KSDWRT(2,'ORA-30000 '||NVL(sys_context('USERENV', 'AUTHENTICATED_IDENTITY'),SYS_CONTEXT( 'USERENV', 'OS_USER' ))||' ORA-00900 invalid SQL statement FROM '||SYS_CONTEXT( 'USERENV', 'OS_USER' )||' '||SYS_CONTEXT( 'USERENV', 'HOST' ));
insert into DBA_servererror(error_date, ora_sysevent, ora_login_user, ora_server_error, sid, host, ip, module, serverhost, sql, os_user, session_user)
values
(
sysdate,
ora_sysevent,
ora_login_user,
ora_server_error(1),
sys_context ('USERENV','SID'),
sys_context ('USERENV','HOST'),
sys_context ('USERENV','IP_ADDRESS'),
sys_context ('USERENV','MODULE'),
sys_context ('USERENV','SERVER_HOST'),
v_sql,
SYS_CONTEXT( 'USERENV', 'OS_USER' ),
SYS_CONTEXT( 'USERENV', 'SESSION_USER' )
);
commit;
end if;
--Never raise an exception from trig_servererror.
--No matter what happens we don't want recursive errors.
exception when others then
NULL;
end;
Watch video How to capture the query throwing ORA-00900: invalid SQL statement online without registration, duration hours minute second in high quality. This video was added by user Ken Kim 23 July 2023, don't forget to share it with your friends and acquaintances, it has been viewed on our site 478 once and liked it 4 people.