StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Rather than hit the system views, as in Demge's answer, there is an ora_sql_txt function that gives the relevant statement.</p> <pre><code>create or replace TRIGGER log_err after servererror on schema DECLARE v_stack VARCHAR2(2000) := substr(dbms_utility.format_error_stack,1,2000); v_back VARCHAR2(2000);-- := substr(dbms_utility.format_error_backtrace,1,2000); v_num NUMBER; v_sql_text ora_name_list_t; procedure track(p_text in varchar2) is begin insert into .... values (p_text); end; begin v_stack := translate(v_stack,'''','"'); track(v_stack); v_back := translate(v_back,'''','"'); if v_back is not null then track(v_back); end if; v_num := ora_sql_txt(v_sql_text); BEGIN FOR i IN 1..v_num LOOP track(to_char(i,'0000')||':'||v_sql_text(i)); END LOOP; EXCEPTION WHEN VALUE_ERROR THEN NULL; END; end; </code></pre> <p>In my own environment, I actually have 'TRACK' as a separate procedure that uses an autonomous transaction, rather than a block as above.</p> <pre><code>create or replace procedure track (p_text IN VARCHAR2) IS PRAGMA AUTONOMOUS_TRANSACTION; cursor c_user is select sys_context('USERENV','CLIENT_INFO') client_info, sys_context('USERENV','CURRENT_SCHEMA') curr_schema, sys_context('USERENV','CURRENT_USER') curr_user, sys_context('USERENV','DB_NAME') db_name, sys_context('USERENV','HOST') host, sys_context('USERENV','IP_ADDRESS') ip, sys_context('USERENV','OS_USER') osuser, sys_context('USERENV','SESSIONID') sessid, sys_context('USERENV','SESSION_USER') sess_user, sys_context('USERENV','TERMINAL') terminal from dual; user_rec c_user%rowtype; v_mod VARCHAR2(48); v_act VARCHAR2(32); v_cli_info varchar2(64); begin open c_user; fetch c_user into user_rec; close c_user; DBMS_APPLICATION_INFO.READ_MODULE (v_mod, v_act); --DBMS_APPLICATION_INFO.READ_CLIENT_INFO(v_cli_info); insert into track_detail (id, track_time, detail, client_info, curr_schema, curr_user, db_name, host, ip, osuser, sessid, sess_user, terminal, module, action) values (track_seq.nextval, systimestamp, p_text, user_rec.client_info, user_rec.curr_schema, user_rec.curr_user, user_rec.db_name, user_rec.host, user_rec.ip, user_rec.osuser, user_rec.sessid, user_rec.sess_user, user_rec.terminal, v_mod, v_act); commit; end; </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload