ora_sql_txt function
527056Aug 7 2006 — edited Aug 8 2006Hello,
We have recently upgraded from an Oracle 9i database to Oracle 10g. In 9i the sql_txt function we have been using was working. But in 10g, we are getting a numeric value error which is returning a null value. We have checked the documentation and feel we are following the event attributes for our trigger correctly. Can someone please help. Thanks.
Here is a sample of our trigger:
DECLARE
sql_text ora_name_list_t;
n NUMBER;
origStmt VARCHAR2(4000);
BEGIN
n:=ora_sql_txt(sql_text); // in 9i we had this as dbms_standard.sql_txt(sql_text)
FOR i IN 1.. LOOP //this is where the error is occurring because ora_sql_txt return null
origStmt:=origStmt || sql_text(i);
END LOOP;
END;