Home » SQL & PL/SQL » SQL & PL/SQL » ora_sql_txt function errors
ora_sql_txt function errors [message #272948] Mon, 08 October 2007 05:57 Go to next message
arcangulac
Messages: 16
Registered: October 2006
Location: izmir
Junior Member

CREATE OR REPLACE TRIGGER NEWDBA.SQL_BUL2
BEFORE UPDATE
OF HASTA_TUR_AD
ON NEWDBA.ALI
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLARE
sql_text ora_name_list_t;
v_n number;
BEGIN
v_n:=ora_sql_txt(sql_text);
dbms_output.put_line(v_n);
FOR i IN 1..ora_sql_txt(sql_text) LOOP
INSERT INTO
ali_log(user_name,ipaddress,audit_time,schema_user,schema_object,sql_text)

VALUES(sys_context('userenv','session_user'),sys_context('userenv','ip_address'),SYSDATE,

ora_dict_obj_owner,ora_dict_obj_name,sql_text(i));
END LOOP;

COMMIT;
EXCEPTION
WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20333,'There is exception!' );
end sql_bul;
/
ora_sql_txt returns null how can i solved this problem
Re: ora_sql_txt function errors [message #272950 is a reply to message #272948] Mon, 08 October 2007 06:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.
Please always post your Oracle version (4 decimals).

Post an example of what you did/tried/checked/verified...

Regards
Michel
Re: ora_sql_txt function errors [message #272957 is a reply to message #272948] Mon, 08 October 2007 06:30 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Two problems.
1) You've just cut and paste this code from somewhere else on the internet (I fould the exact same piece of code) and the claim on that site that it works is a lie.
If you read the documentation for ORA_SQL_TEXT, you'll see that a better way to use it is this:
CREATE OR REPLACE TRIGGER upd_test
AFTER insert ON upd_1
declare
  sql_text ora_name_list_t;
  n        pls_integer;
BEGIN
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
    insert into log_table values (sql_text(i));
  END LOOP;
end test;


2) As far as I can remember, Ora_sql_txt only works in system event triggers anyway.
Previous Topic: a procedure taking long time while running it
Next Topic: Employee Supervisor problem
Goto Forum:
  


Current Time: Sun Dec 11 00:20:54 CST 2016

Total time taken to generate the page: 0.07757 seconds