CODEWITHSUNDEEP
oracle-databaseplsqltriggers
zhanzezhu
zhanzezhu

Reputation: 121

ORA-06502: PL/SQL: numeric or value error by ora_sql_txt(ora_name_list_t)

table:

create table EMPLOYEES_LOG
(
  who    VARCHAR2(300),
  when   DATE,
  action VARCHAR2(2000)
)

trigger:

create or replace trigger aiud_employees_copy
  after insert or update or delete
  on employees_copy 
declare
    l_action employees_log.action%type;
    l_sql_text ora_name_list_t;
begin
    l_action := 'The statement that causes the change was:' || chr(10);

    for i in 1..ora_sql_txt(l_sql_text) loop
        l_action := l_action || l_sql_text(i);
    end loop;
    insert into employees_log(who, action, when)
        values(user, l_action, sysdate);
end aiud_employees_copy;

execute:

update employees_copy set salary=salary*1.05 where department_id=20;

gives me the error:

ORA-06502: PL/SQL: numeric or value error at for i in 1..ora_sql_txt(l_sql_text) loop

thanks.

Upvotes: 1

Views: 2512

Answers (1)

Alex Poole
Alex Poole

Reputation: 191275

That function has an OUT parameter and returns a pls_integer number of elements. That document shows it being assigned to a variable:

    l_n := ora_sql_txt(l_sql_text);
    for i in 1..l_sql_text.count loop

So:

create or replace trigger aiud_employees_copy
  after insert or update or delete
  on employees_copy 
declare
    l_action employees_log.action%type;
    l_sql_text ora_name_list_t;
    l_n pls_integer;
begin
    l_action := 'The statement that causes the change was:' || chr(10);

    l_n := ora_sql_txt(l_sql_text);
    for i in 1..l_sql_text.count loop
        l_action := l_action || l_sql_text(i);
    end loop;
    insert into employees_log(who, action, when)
        values(user, l_action, sysdate);
end aiud_employees_copy;
/

... although referring to it directly should work.

But ora_sql_txt() is for system event triggers; for a DML trigger it will return null (apart from some versions of 9i, which seem to have been unintentional; see bug 4230721). The seciotn of the documentation the function is described in is "Triggers for Publishing Events", and it makes it clear that they only apply to certain database and client events.

By invoking system-defined event attribute functions in Table 9-4, a trigger can retrieve certain attributes of the triggering event. Not all triggers can invoke all event attribute functions—for details, see "Event Attribute Functions for Database Event Triggers" and "Event Attribute Functions for Client Event Triggers".

So ora_sql_txt(l_sql_text) will be null, which is the value error you're getting. If you assign it to a variable first you'll still get an error. You can nvl() it to zero, but since it will always be null (and hence zero rows once you do that) it's a bit pointless.

Upvotes: 2

Related Questions