Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Problem Using DBMS_SQL in trigger.
Hi,
I am trying to use the procedure DBMS_SQL to dynamically process
the few columns from the database trigger. This will basically be used to audit the database. I am having problem processing the columns in the trigger using DBMS_SQL as all the variable :new.field_name and :old.field_name starts with : and dbms_sql considers anything starting with : as the place holder. To work around I tried the following:
(Relevant portion of the trigger, trigger code has hard-coded field name
for testing. )
v_cursor Auditing.Auditable_Col_Info_Cur; v_column v_cursor%ROWTYPE; v_SqlStmt varchar2(2000); v_CursorHandle INTEGER; v_RowsModified INTEGER; v_Table_Name varchar(50) := 'AU_TABLE1'; v_Primary_Key varchar(50) := 'TABLE1_PK'; v_newString varchar2(50); v_oldString varchar2(50); v_oldparm varchar(50); v_newparm varchar(50); BEGIN v_CursorHandle := DBMS_SQL.OPEN_CURSOR;
/*Get the columns which needs to be processed, column names are returned
in the cursor v_cursor by procedure call */
Auditing.Get_Auditable_Col(v_Table_Name, v_cursor); LOOP FETCH v_cursor into v_column; EXIT WHEN v_cursor%NOTFOUND; v_oldParm := ':old.String1'; /*String1 is the field in the table. */ v_newParm := ':new.String1'; /*I also tried v_SqlStmt := 'BEGIN Auditing.pv_String := :new.String1 ; END; '; But then DBMS_SQL expect me to bind the place holder :new.String1*/ v_SqlStmt := 'BEGIN Auditing.pv_String := :val ; END; '; DBMS_OUTPUT.PUT_LINE('v_SqlStmt ' || v_SqlStmt); DBMS_SQL.PARSE (v_CursorHandle, v_SqlStmt, DBMS_SQL.V7); DBMS_SQL.BIND_VARIABLE(v_CursorHandle, ':val',v_newParm);
DBMS_SQL.DEFINE_COLUMN (v_cursorHandle, 2, v_oldString, 50);*/ v_RowsModified := DBMS_SQL.EXECUTE(v_CursorHandle); DBMS_OUTPUT.PUT_LINE('Value of Auditing.pv_String ' ||Auditing.pv_String );
END LOOP; END;
I will appreciate any help or pointer to process the columns dynamically from trigger.
Asif
===
Received on Tue Sep 22 1998 - 00:00:00 CDT