ORA-06502: PL/SQL: numeric or value error by ora_sql

I have table for ddl logs. And in this table I also want to see which code was exactly done. How can I put it into sql_tt column? Can you prompt me with this?

CREATE TABLE AAUDIT_DDL (
d date,
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255),
IP_ADDRESS   VARCHAR2(100 CHAR),
TERMINAL varchar2(255),
owner varchar2(30),
OBJECT_TYPE varchar2(30),
OBJECT_NAME varchar2(30),
DDL_TYPE varchar2(30),
SQL_TT varchar(100));

and trigger

create or replace trigger aaudit_ddl after ddl on DATABASE

begin
if (ora_sysevent='REVOKE')
then
null; -- I do not care about REVOKE
else
insert into aaudit_ddl(d, osuser,current_user,host,ip_address,terminal,owner,OBJECT_TYPE,OBJECT_NAME,DDL_TYPE,SQL_TT)
values(
  sysdate,
  sys_context('USERENV','OS_USER') ,
  sys_context('USERENV','CURRENT_USER') ,
  sys_context('USERENV','HOST') ,
  SYS_CONTEXT('USERENV','IP_ADDRESS'),
  sys_context('USERENV','TERMINAL') ,
  ora_dict_obj_owner,
  ora_dict_obj_type,
  ora_dict_obj_name,
  ora_sysevent,
  ora_sql_txt
  );
  end if;
  end;
 /

CodePudding user response：

Documentation suggests different approach to ora_sql_txt: as function expects IN parameter (which is an integer, representing number of elements in PL/SQL table), you should

CREATE TABLE event_table (col VARCHAR2(2030));

DECLARE
  sql_text   ora_name_list_t;
  n          PLS_INTEGER;
  v_stmt     VARCHAR2(2000);
BEGIN
  n := ora_sql_txt(sql_text);

  FOR i IN 1..n LOOP
    v_stmt := v_stmt || sql_text(i);
  END LOOP;

  INSERT INTO event_table VALUES ('text of triggering statement: ' || v_stmt);
END;

CodePudding user response：

This is how I have mine setup and it works fine.


 --Table will be created by sys user with tbs_audit as tablespace as default tablespace for audit table AUDIT_DDL

CREATE TABLE AUDIT_DDL ( 
DDL_DATE date, 
OSUSER varchar2(255),
CURRENT_USER varchar2(255),
HOST varchar2(255), 
TERMINAL varchar2(255), 
IP_ADDRESS VARCHAR2(100), 
module varchar2(100),
owner varchar2(30), 
type varchar2(30), 
name varchar2(30), 
sysevent varchar2(30),
sql_txt varchar2(4000) ) 
tablespace tbs_audit ;

--- Trigger will be created at sys user, so that all statements will be captured in any schema in the database

create or replace trigger
sys.audit_ddl_trg after ddl on database
declare
sql_text ora_name_list_t;
stmt VARCHAR2(4000) := '';
n number;
begin
n:=ora_sql_txt(sql_text);
for i in 1..n
loop
stmt:=substr(stmt||sql_text(i),1,4000);
end loop;
insert into audit_ddl(
DDL_DATE,osuser,
current_user,host,
terminal,
ip_address,module,
owner,
type,name,sysevent,sql_txt)
values(
sysdate,
sys_context('USERENV','OS_USER') ,
sys_context('USERENV','CURRENT_USER') ,
sys_context('USERENV','HOST') ,
sys_context('USERENV','TERMINAL') ,
UTL_INADDR.get_host_name('USERENV'),
sys_context('USERENV','MODULE') ,
ora_dict_obj_owner,
ora_dict_obj_type,
ora_dict_obj_name,
ora_sysevent,
stmt
);
end;
/

-- Sql Query can be used to lookup the captured ddl in the audit_ddl table.
col type format a10
col name format a10
col host format a16
col IP_ADDRESS format a10
col terminal format a20
col owner format a15
col sql_txt format a20
col SYSEVENT format a10
col CURRENT_USER format a10
col osuser format a10
select * from AUDIT_DDL;