Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Problem Using DBMS_SQL in trigger.

Problem Using DBMS_SQL in trigger.

From: Asif H. rajwani <asif.rajwani_at_mci2000.com>
Date: 1998/09/22
Message-ID: <3608386D.199935FA@mci2000.com>#1/1

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. )




CREATE OR REPLACE TRIGGER "AU_TABLE1_B_R_UID" BEFORE INSERT OR UPDATE OR DELETE
ON IB_PIA.AU_TABLE1
REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW DECLARE
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;




Instead of assigning the new value of String1 the literal :new.string1 is assigned to the variable Auditing.pv_String. Is there any way to tell dbms_sql that the word starting with : is not the placeholder. Some kind of escape character etc.

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US