Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Compile Error
Trigger Compile Error [message #211463] Thu, 28 December 2006 17:26 Go to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member
Hi guys,

I need help to generate dynamic trigger.

First problem is : why this trigger is not getting compile?
it says table or view does not exist, Is that means I don't have permission to create tigger on that table ? or some there problem.

Here is trigger :
SQL> CREATE OR REPLACE TRIGGER TRG_AU_TBLCARRIER
  2  AFTER UPDATE
  3  ON TBLCARRIER
  4  FOR EACH ROW
  5  DECLARE
  6    V_USER  VARCHAR2(30) :=NULL;
  7    V_ROLE  VARCHAR2(30) :=NULL;
  8    V_PROG  VARCHAR2(48) :=NULL;
  9    CURSOR cur_RoleCheck IS
 10      SELECT drp.grantee, drp.granted_role, s.program
 11      FROM   dba_role_privs drp,v$session s
 12      WHERE  drp.grantee = s.username
 13      AND    drp.grantee = USER;
 14    MyClob    CLOB;
 15  BEGIN
 16    OPEN cur_RoleCheck;
 17    LOOP
 18           FETCH cur_RoleCheck into V_USER,V_ROLE,V_PROG;
 19      EXIT WHEN cur_RoleCheck%NOTFOUND;
 20      IF cur_RoleCheck%ROWCOUNT = 1 then
 21  MyClob := '<root>' ||
 22  '<row type="old">' ||
 23  '<column name="CARID" value="' || :old.CARID || '"/>' ||
 24  '<column name="CARNAME" value="' || :old.CARNAME || '"/>' ||
 25  '<column name="EXTERNALCODEOWNERID" value="' || :old.EXTERNALCODEOWNERID || '"/>' ||
 26  '<column name="CREATEDBY" value="' || :old.CREATEDBY || '"/>' ||
 27  '<column name="CREATEDDATETIME" value="' || :old.CREATEDDATETIME || '"/>' ||
 28  '<column name="UPDATEDBY" value="' || :old.UPDATEDBY || '"/>' ||
 29  '<column name="UPDATEDDATETIME" value="' || :old.UPDATEDDATETIME || '"/>' ||
 30  '<column name="OPERATIONCENTERCODE" value="' || :old.OPERATIONCENTERCODE || '"/>' ||
 31  '<column name="INVOICEPRINTLOCATION" value="' || :old.INVOICEPRINTLOCATION || '"/>' ||
 32  '<column name="PRINTINTERNALIND" value="' || :old.PRINTINTERNALIND || '"/>' ||
 33  '<column name="PREPAREINVOICEIND" value="' || :old.PREPAREINVOICEIND || '"/>' ||
 34  '<column name="TAXID" value="' || :old.TAXID || '"/>' ||
 35  '<column name="INVOICEMARKUPPERCENTAGE" value="' || :old.INVOICEMARKUPPERCENTAGE || '"/>' ||
 36  '</row>'
 37  ||
 38  '<row type="new">' ||
 39  '<column name="CARID" value="' || :new.CARID || '"/>' ||
 40  '<column name="CARNAME" value="' || :new.CARNAME || '"/>' ||
 41  '<column name="EXTERNALCODEOWNERID" value="' || :new.EXTERNALCODEOWNERID || '"/>' ||
 42  '<column name="CREATEDBY" value="' || :new.CREATEDBY || '"/>' ||
 43  '<column name="CREATEDDATETIME" value="' || :new.CREATEDDATETIME || '"/>' ||
 44  '<column name="UPDATEDBY" value="' || :new.UPDATEDBY || '"/>' ||
 45  '<column name="UPDATEDDATETIME" value="' || :new.UPDATEDDATETIME || '"/>' ||
 46  '<column name="OPERATIONCENTERCODE" value="' || :new.OPERATIONCENTERCODE || '"/>' ||
 47  '<column name="INVOICEPRINTLOCATION" value="' || :new.INVOICEPRINTLOCATION || '"/>' ||
 48  '<column name="PRINTINTERNALIND" value="' || :new.PRINTINTERNALIND || '"/>' ||
 49  '<column name="PREPAREINVOICEIND" value="' || :new.PREPAREINVOICEIND || '"/>' ||
 50  '<column name="TAXID" value="' || :new.TAXID || '"/>' ||
 51  '<column name="INVOICEMARKUPPERCENTAGE" value="' || :new.INVOICEMARKUPPERCENTAGE || '"/>' ||
 52  '</row>'
 53  ||
 54  '</root>';
 55        INSERT INTO  audit_sox(AUDSEQNUM,instancename,databasename,objectname,
 56                              objectowner,objecttype,username,workstationid,
 57                              programname,eventtime,eventaction,auditdata)
 58                       VALUES(AUDIT_SOX_SEQ.NEXTVAL,sys_context('userenv','DB_NAME'),
 59                             sys_context('userenv','DB_NAME'),'TBLCARRIER',sys_context('userenv','CURRENT_SCHEMA'),
 60                             sys.dictionary_obj_type,sys_context('userenv','SESSION_USER'),sys_context('userenv','HOST'),
 61                             V_PROG,sysdate,decode(V_ROLE,'APP_USER','U_APP_USER_OTHER','UPDATE'),MyClob);
 62
 63      END IF;
 64
 65   END LOOP;
 66
 67     CLOSE cur_RoleCheck;
 68
 69  EXCEPTION
 70    WHEN NO_DATA_FOUND THEN
 71      NULL;
 72    WHEN OTHERS THEN
 73      dbms_output.put_line('Other Error - TRG_AU_TBLCARRIER');
 74      DBMS_OUTPUT.PUT_LINE(SQLERRM || '  ' || SQLCODE);
 75  END TRG_AU_TBLCARRIER;
 76  /

Warning: Trigger created with compilation errors.

SQL> SHOW ERR
Errors for TRIGGER TRG_AU_TBLCARRIER:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/5      PL/SQL: SQL Statement ignored
7/31     PL/SQL: ORA-00942: table or view does not exist
SQL>



Second Problem is : how to write this trigger as dynamicaly to generate triggers for all tables on a schema :

I have tried doing it this way :


CREATE OR REPLACE PROCEDURE create_trigg_1 (v_owner all_tables.owner%TYPE)
AUTHID CURRENT_USER
IS
   CURSOR c1
   IS
      SELECT owner, table_name
        FROM all_tables
       WHERE owner = v_owner;

   r1            c1%ROWTYPE;
   v_trig_type   VARCHAR2 (10);

   PROCEDURE gentrig (
      tabname     IN   all_tables.table_name%TYPE,
      sch_name    IN   all_users.username%TYPE,
      trig_type   IN   VARCHAR2 DEFAULT 'UPDATE'
   )
   IS
   BEGIN
      EXECUTE IMMEDIATE    '  CREATE OR REPLACE TRIGGER '||SCH_NAME||'.TRG_AU_'||TABNAME
                        || ' AFTER '
                        || trig_type
                        || ' ON '
                        || sch_name
                        || '.'
                        || tabname
                        || ' FOR EACH ROW  '
                        || ' DECLARE  '
                        || '   V_USER  VARCHAR2(30) :=NULL;  '
                        || '   V_ROLE  VARCHAR2(30) :=NULL;  '
                        || '   V_PROG  VARCHAR2(48) :=NULL;  '
                        || '   CURSOR cur_RoleCheck IS       '
                        || '     SELECT drp.grantee, drp.granted_role, s.program   '
                        || '     FROM   dba_role_privs drp,v$session s  '
                        || '     WHERE  drp.grantee = s.username   '
                        || '     AND    drp.grantee = USER   '
                        || '     AND    ((drp.granted_role = ''APP_USER'' AND UPPER(s.program) NOT IN (''CLAIM98.EXE'',''INTAKE98.EXE'',''PROVIDER98.EXE''))  '
                        || '     OR     (granted_role = ''POWER_USER''));   '
                        || '   MyClob    CLOB;   '
                        || ' BEGIN    '
                        || '   OPEN cur_RoleCheck;   '
                        || '   LOOP   '
                        || '     FETCH cur_RoleCheck into V_USER,V_ROLE,V_PROG;   '
                        || '     EXIT WHEN cur_RoleCheck%NOTFOUND;   '
                        || '     IF cur_RoleCheck%ROWCOUNT = 1 then    '
                        || ' MyClob := ''<root>''     ||   '
                        || ' ''<row type="old">''     ||  '
                        || ' ''<column name="CARID" value="'' || :old.CARID || ''"/>'' ||  '
                        || ' '' <column name="CARNAME" value="'' || :old.CARNAME || ''"/>'' ||  '
                        || ' '' <column name="EXTERNALCODEOWNERID" value="'' || :old.EXTERNALCODEOWNERID || ''"/>'' ||  '
                        || ' ''<column name="CREATEDBY" value="'' || :old.CREATEDBY || ''"/>'' ||  '
                        || ' ''<column name="CREATEDDATETIME" value="'' || :old.CREATEDDATETIME || ''"/>'' ||  '
                        || ' ''<column name="UPDATEDBY" value="'' || :old.UPDATEDBY || ''"/>'' ||  '
                        || ' ''<column name="UPDATEDDATETIME" value="'' || :old.UPDATEDDATETIME || ''"/>'' ||  '
                        || ' ''<column name="OPERATIONCENTERCODE" value="'' || :old.OPERATIONCENTERCODE || ''"/>'' ||  '
                        || ' ''<column name="INVOICEPRINTLOCATION" value="'' || :old.INVOICEPRINTLOCATION || ''"/>'' ||  '
                        || ' ''<column name="PRINTINTERNALIND" value="'' || :old.PRINTINTERNALIND || ''"/>'' ||  '
                        || ' ''<column name="PREPAREINVOICEIND" value="'' || :old.PREPAREINVOICEIND || ''"/>'' ||  '
                        || ' ''<column name="TAXID" value="'' || :old.TAXID || ''"/>'' ||  '
                        || ' ''<column name="INVOICEMARKUPPERCENTAGE" value="'' || :old.INVOICEMARKUPPERCENTAGE || ''"/>'' ||  '
                        || ' ''</row>'' '
                        || ' ||  '
                        || ' ''<row type="new">'' ||  '
                        || ' ''<column name="CARID" value="'' || :new.CARID || ''"/>'' ||  '
                        || ' ''<column name="CARNAME" value="'' || :new.CARNAME || ''"/>'' ||  '
                        || ' ''<column name="EXTERNALCODEOWNERID" value="'' || :new.EXTERNALCODEOWNERID || ''"/>'' ||  '
                        || ' ''<column name="CREATEDBY" value="'' || :new.CREATEDBY || ''"/>'' ||  '
                        || ' ''<column name="CREATEDDATETIME" value="'' || :new.CREATEDDATETIME || ''"/>'' ||  '
                        || ' ''<column name="UPDATEDBY" value="'' || :new.UPDATEDBY || ''"/>'' ||  '
                        || ' ''<column name="UPDATEDDATETIME" value="'' || :new.UPDATEDDATETIME || ''"/>'' ||  '
                        || ' ''<column name="OPERATIONCENTERCODE" value="'' || :new.OPERATIONCENTERCODE || ''"/>'' ||  '
                        || ' ''<column name="INVOICEPRINTLOCATION" value="'' || :new.INVOICEPRINTLOCATION || ''"/>'' ||  '
                        || ' ''<column name="PRINTINTERNALIND" value="'' || :new.PRINTINTERNALIND || ''"/>'' ||  '
                        || ' ''<column name="PREPAREINVOICEIND" value="'' || :new.PREPAREINVOICEIND || ''"/>'' ||  '
                        || ' ''<column name="TAXID" value="''  || :new.TAXID || ''"/>'' ||  '
                        || ' ''<column name="INVOICEMARKUPPERCENTAGE" value="'' || :new.INVOICEMARKUPPERCENTAGE || ''"/>'' ||  '
                        || ' ''</row>''  '
                        || ' ||  '
                        || ' ''</root>''; '
                        || '       INSERT INTO  audit_sox(AUDSEQNUM,instancename,databasename,objectname,  '
                        || '                             objectowner,objecttype,username,workstationid,  '
                        || '                             programname,eventtime,eventaction,auditdata)   '
                        || '                      VALUES(AUDIT_SOX_SEQ.NEXTVAL,sys_context(''userenv'',''DB_NAME''),  '
                        || '                            sys_context(''userenv'',''DB_NAME''),'
                        || tabname
                        || ',sys_context(''userenv'',''CURRENT_SCHEMA''),  '
                        || '                            sys.dictionary_obj_type,sys_context(''userenv'',''SESSION_USER''),sys_context(''userenv'',''HOST''),  '
                        || '                            V_PROG,sysdate,decode(V_ROLE,''APP_USER'',''U_APP_USER_OTHER'','
                        || trig_type
                        || ' ''),MyClob);  '
                        || '    '
                        || '     END IF;  '
                        || '    '
                        || '  END LOOP;  '
                        || '    '
                        || '    CLOSE cur_RoleCheck;  '
                        || '     '
                        || ' EXCEPTION    '
                        || '   WHEN NO_DATA_FOUND THEN   '
                        || '     NULL;    '
                        || '   WHEN OTHERS THEN   '
                        || '     dbms_output.put_line(''Other Error - TRG_AU_'
                        || tabname
                        || '  );  '
                        || '     DBMS_OUTPUT.PUT_LINE(SQLERRM || ''  '' || SQLCODE);  '
                        || ' END TRG_AU_'
                        || tabname;
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Failed to Generate Trigger.');
   END gentrig;
BEGIN
   OPEN c1;

   LOOP
      FETCH c1
       INTO r1;

      EXIT WHEN c1%NOTFOUND;
      gentrig (r1.table_name, r1.owner);
   END LOOP;

   CLOSE c1;
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      r1.table_name := NULL;
      r1.owner := NULL;
   WHEN OTHERS
   THEN
      NULL;
END create_trigg_1;
/


The above did not work for other table,naturally
because the following part of the trigger is having columns name for TBLCARRIER only and hence this require some dynamic way to select tables colums also, so how do I achieve this ?


                        || ' MyClob := ''<root>''     ||   '
                        || ' ''<row type="old">''     ||  '
                        || ' ''<column name="CARID" value="'' || :old.CARID || ''"/>'' ||  '
                        || ' '' <column name="CARNAME" value="'' || :old.CARNAME || ''"/>'' ||  '
                        || ' '' <column name="EXTERNALCODEOWNERID" value="'' || :old.EXTERNALCODEOWNERID || ''"/>'' ||  '
                        || ' ''<column name="CREATEDBY" value="'' || :old.CREATEDBY || ''"/>'' ||  '
                        || ' ''<column name="CREATEDDATETIME" value="'' || :old.CREATEDDATETIME || ''"/>'' ||  '
                        || ' ''<column name="UPDATEDBY" value="'' || :old.UPDATEDBY || ''"/>'' ||  '
                        || ' ''<column name="UPDATEDDATETIME" value="'' || :old.UPDATEDDATETIME || ''"/>'' ||  '
                        || ' ''<column name="OPERATIONCENTERCODE" value="'' || :old.OPERATIONCENTERCODE || ''"/>'' ||  '
                        || ' ''<column name="INVOICEPRINTLOCATION" value="'' || :old.INVOICEPRINTLOCATION || ''"/>'' ||  '
                        || ' ''<column name="PRINTINTERNALIND" value="'' || :old.PRINTINTERNALIND || ''"/>'' ||  '
                        || ' ''<column name="PREPAREINVOICEIND" value="'' || :old.PREPAREINVOICEIND || ''"/>'' ||  '
                        || ' ''<column name="TAXID" value="'' || :old.TAXID || ''"/>'' ||  '
                        || ' ''<column name="INVOICEMARKUPPERCENTAGE" value="'' || :old.INVOICEMARKUPPERCENTAGE || ''"/>'' ||  '
                        || ' ''</row>'' '
                        || ' ||  '
                        || ' ''<row type="new">'' ||  '
                        || ' ''<column name="CARID" value="'' || :new.CARID || ''"/>'' ||  '
                        || ' ''<column name="CARNAME" value="'' || :new.CARNAME || ''"/>'' ||  '
                        || ' ''<column name="EXTERNALCODEOWNERID" value="'' || :new.EXTERNALCODEOWNERID || ''"/>'' ||  '
                        || ' ''<column name="CREATEDBY" value="'' || :new.CREATEDBY || ''"/>'' ||  '
                        || ' ''<column name="CREATEDDATETIME" value="'' || :new.CREATEDDATETIME || ''"/>'' ||  '
                        || ' ''<column name="UPDATEDBY" value="'' || :new.UPDATEDBY || ''"/>'' ||  '
                        || ' ''<column name="UPDATEDDATETIME" value="'' || :new.UPDATEDDATETIME || ''"/>'' ||  '
                        || ' ''<column name="OPERATIONCENTERCODE" value="'' || :new.OPERATIONCENTERCODE || ''"/>'' ||  '
                        || ' ''<column name="INVOICEPRINTLOCATION" value="'' || :new.INVOICEPRINTLOCATION || ''"/>'' ||  '
                        || ' ''<column name="PRINTINTERNALIND" value="'' || :new.PRINTINTERNALIND || ''"/>'' ||  '
                        || ' ''<column name="PREPAREINVOICEIND" value="'' || :new.PREPAREINVOICEIND || ''"/>'' ||  '
                        || ' ''<column name="TAXID" value="''  || :new.TAXID || ''"/>'' ||  '
                        || ' ''<column name="INVOICEMARKUPPERCENTAGE" value="'' || :new.INVOICEMARKUPPERCENTAGE || ''"/>'' ||  '
                        || ' ''</row>''  '
                        || ' ||  '
                        || ' ''</root>''; '
                        


Your help is appreciated.

Re: Trigger Compile Error [message #211464 is a reply to message #211463] Thu, 28 December 2006 17:34 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
Privs acquired via ROLE do NOT apply within PL/SQL procedures.
Re: Trigger Compile Error [message #211465 is a reply to message #211464] Thu, 28 December 2006 17:38 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member

Thank you Anacedent for you reply.

So what is way work around for this problem?

And any idea about dynamic solution ?

Thanks
Re: Trigger Compile Error [message #211467 is a reply to message #211463] Thu, 28 December 2006 17:46 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
>So what is way work around for this problem?
If privs via ROLE don't work, then obviously GRANT must be issued directly to USER.
And any idea about dynamic solution ?
"Dynamic Solution" should be banned from every application as a non-scalable kludge.
Re: Trigger Compile Error [message #211584 is a reply to message #211467] Fri, 29 December 2006 13:10 Go to previous messageGo to next message
Akshar
Messages: 116
Registered: May 2006
Senior Member


Thanks for you reply.

This is not a solution I am looking for.

If not possible this way , there should be some other technique
to achieve this.




Re: Trigger Compile Error [message #211872 is a reply to message #211463] Tue, 02 January 2007 15:53 Go to previous messageGo to next message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
A procedure may NOT access the dba_role_privs view unless explicitly granted the ability to do so. If you want your XML generator to work, then grant the privilege. This is what the other users were referring to. Also why are you saving a before and after image for every role that the user has. What is your reason for doing this?
Re: Trigger Compile Error [message #211875 is a reply to message #211463] Tue, 02 January 2007 16:27 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
> If not possible this way , there should be some other technique to achieve this.
Honestly, I am not sure I understand exactly what "this" is.
Rather the throw code at the problem, please state as completely as you can what problem your are really trying to solve & how an independent observer would conclude if/when the problem has actually be successfully solved.

[Updated on: Tue, 02 January 2007 16:27] by Moderator

Report message to a moderator

Previous Topic: Creation of multiple temp tables
Next Topic: Triggers
Goto Forum:
  


Current Time: Wed Dec 07 22:08:17 CST 2016

Total time taken to generate the page: 0.07125 seconds