Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Error (Oracle 10.2.0.4.0 )
Trigger Error [message #401576] Tue, 05 May 2009 10:43 Go to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
i have built a simple trigger to populate who columns for auditing purpose. the schema that i am using has all the privileges grants to do a dml on the objects. i run into this problem that when this trigger is compile it throws an error.

SQL> create or replace trigger LPS.TIUBR_LPS_PYMT_TRANS_INTERFACE
  2    before insert or update on LPS.LPS_PYMT_TRANS_INTERFACE
  3    for each row
  4  begin
  5    if INSERTING then
  6      select LPS.S_PYMT_TRANS_INTERFACE_ID_SEQ.nextval
  7        into :new.pymt_trans_interface_id
  8        from dual;
  9  
 10      if :new.process_flag is null then
 11         :new.process_flag := 'PENDING';
 12      end if;
 13  
 14      if :new.created_by is null then
 15        for user_info_rec in (select ctut_id from tdbxdata.account_reference
 16                               where account_id = sys_context('USERENV','SESSION_USER'))
 17          loop
 18          :new.created_by := user_info_rec.ctut_id;
 19        end loop;
 20      end if;
 21  
 22    end if;
 23  end;
 24  /

Warning: Trigger created with compilation errors

SQL> show error;
Errors for TRIGGER LPS.TIUBR_LPS_PYMT_TRANS_INTERFACE:

LINE/COL ERROR
-------- -------------------------------------------------------------
12/57    PL/SQL: ORA-00942: table or view does not exist
12/28    PL/SQL: SQL Statement ignored
15/28    PLS-00364: loop index variable 'USER_INFO_REC' use is invalid
15/9     PL/SQL: Statement ignored


and i checked all the object has privileges granted to the schema that i am using to create the trigger:

SQL> describe LPS.LPS_PYMT_TRANS_INTERFACE;
Name                    Type         Nullable Default Comments 
----------------------- ------------ -------- ------- -------- 
PYMT_TRANS_INTERFACE_ID NUMBER                                 
ATTRIBUTE1              VARCHAR2(80) Y                         
ATTRIBUTE2              VARCHAR2(80) Y                         
ATTRIBUTE3              VARCHAR2(80) Y                         
ATTRIBUTE4              VARCHAR2(80) Y                         
ATTRIBUTE5              VARCHAR2(80) Y                         
ATTRIBUTE6              VARCHAR2(80) Y                         
ATTRIBUTE7              VARCHAR2(80) Y                         
ATTRIBUTE8              VARCHAR2(80) Y                         
ATTRIBUTE9              VARCHAR2(80) Y                         
ATTRIBUTE10             VARCHAR2(80) Y                         
ATTRIBUTE11             VARCHAR2(80) Y                         
ATTRIBUTE12             VARCHAR2(80) Y                         
ATTRIBUTE13             VARCHAR2(80) Y                         
ATTRIBUTE14             VARCHAR2(80) Y                         
ATTRIBUTE15             VARCHAR2(80) Y                         
ATTRIBUTE16             VARCHAR2(80) Y                         
ATTRIBUTE17             VARCHAR2(80) Y                         
ATTRIBUTE18             VARCHAR2(80) Y                         
ATTRIBUTE19             VARCHAR2(80) Y                         
ATTRIBUTE20             VARCHAR2(80) Y                         
ATTRIBUTE21             VARCHAR2(80) Y                         
ATTRIBUTE22             VARCHAR2(80) Y                         
ATTRIBUTE23             VARCHAR2(80) Y                         
ATTRIBUTE24             VARCHAR2(80) Y                         
ATTRIBUTE25             VARCHAR2(80) Y                         
ATTRIBUTE26             VARCHAR2(80) Y                         
ATTRIBUTE27             VARCHAR2(80) Y                         
ATTRIBUTE28             VARCHAR2(80) Y                         
ATTRIBUTE29             VARCHAR2(80) Y                         
ATTRIBUTE30             VARCHAR2(80) Y                         
PROCESS_FLAG            VARCHAR2(20) Y                         
COMMENTS                VARCHAR2(80) Y                         
CREATED_BY              NUMBER       Y                         
CREATED_DATE            DATE         Y                         
UPDATED_BY              NUMBER       Y                         
UPDATED_DATE            DATE         Y                         

SQL> select LPS.S_PYMT_TRANS_INTERFACE_ID_SEQ.nextval from dual;

   NEXTVAL
----------
        89

SQL> select ctut_id from tdbxdata.account_reference
  2   where account_id = sys_context('USERENV','SESSION_USER');

      CTUT_ID
-------------

SQL> select sys_context('USERENV','SESSION_USER') from dual;

SYS_CONTEXT('USERENV','SESSION
--------------------------------------------------------------------------------
LPS

SQL> 


i could not figure what might i have been missing.

thanks,
warren
Re: Trigger Error [message #401577 is a reply to message #401576] Tue, 05 May 2009 10:49 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>i could not figure what might i have been missing.
Privileges acquired via ROLE do not apply within PL/SQL procedures.

Re: Trigger Error [message #401578 is a reply to message #401577] Tue, 05 May 2009 10:53 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
thanks. our database group has standards that they give priveleges thru roles only and they do not give direct grants. is there a workaround?
Re: Trigger Error [message #401579 is a reply to message #401578] Tue, 05 May 2009 10:55 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
wtolentino wrote on Tue, 05 May 2009 08:53
thanks. our database group has standards that they give priveleges thru roles only and they do not give direct grants. is there a workaround?

Oracle has its own security model.
It is what it is.
Any "work around" would violate Oracle's security model.

Rock<->YOU<->Hard Spot

Good Luck!
Re: Trigger Error [message #401580 is a reply to message #401578] Tue, 05 May 2009 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
 15        for user_info_rec in (select ctut_id from tdbxdata.account_reference
 16                               where account_id = sys_context('USERENV','SESSION_USER'))
 17          loop
 18          :new.created_by := user_info_rec.ctut_id;
 19        end loop;

Your code is deterministic ONLY if the query returns at most one row.
In this case, create a procedure in the table owner schema that returns the possible value and grant execute privilege to this procedure to the trigger owner role.

Regards
Michel
Re: Trigger Error [message #401581 is a reply to message #401579] Tue, 05 May 2009 11:01 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
do you suggest that to make the issue go away is to give it a direct grants?
Re: Trigger Error [message #401582 is a reply to message #401581] Tue, 05 May 2009 11:09 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
wtolentino wrote on Tue, 05 May 2009 09:01
do you suggest that to make the issue go away is to give it a direct grants?

Direct GRANT when procedure is owned by a different user is required.
Alternatively have procedure owned by same user as who owns table.
Re: Trigger Error [message #401583 is a reply to message #401581] Tue, 05 May 2009 11:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
wtolentino wrote on Tue, 05 May 2009 18:01
do you suggest that to make the issue go away is to give it a direct grants?

Did you see my post?
Quote:
...create a procedure in the table owner schema that returns the possible value and grant execute privilege to this procedure to the trigger owner role.

Regards
Michel
Re: Trigger Error [message #401585 is a reply to message #401583] Tue, 05 May 2009 11:59 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member

yes i did see your post and as of this momment creating the procedures.
Re: Trigger Error [message #401586 is a reply to message #401583] Tue, 05 May 2009 12:02 Go to previous messageGo to next message
wtolentino
Messages: 196
Registered: March 2005
Senior Member
i did try using the same schema but it throws the same error when creating the function.

SQL> create or replace function lps.fn01_lps_app_audit_cols(p_username IN VARCHAR2)
  2    return number is
  3  begin
  4    for user_info_rec in (select ctut_id from tdbxdata.account_reference
  5                           where account_id = sys_context('USERENV','SESSION_USER'))
  6      loop
  7      return(user_info_rec.ctut_id);
  8    end loop;
  9    return (null);
 10  end;
 11  /

Warning: Function created with compilation errors

SQL> show errors
Errors for FUNCTION LPS.FN01_LPS_APP_AUDIT_COLS:

LINE/COL ERROR
-------- -------------------------------------------------------------
4/53     PL/SQL: ORA-00942: table or view does not exist
4/24     PL/SQL: SQL Statement ignored
7/12     PLS-00364: loop index variable 'USER_INFO_REC' use is invalid
7/5      PL/SQL: Statement ignored

SQL> 

Re: Trigger Error [message #401587 is a reply to message #401576] Tue, 05 May 2009 12:06 Go to previous messageGo to next message
BlackSwan
Messages: 25032
Registered: January 2009
Location: SoCal
Senior Member
>i did try using the same schema but it throws the same error when creating the function.

The procedure/function needs to be owned by the tdbxdata schema.
Re: Trigger Error [message #401591 is a reply to message #401586] Tue, 05 May 2009 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64098
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Reread my post:
Quote:
create a procedure in the table owner schema that returns the possible value and grant execute privilege to this procedure to the trigger owner role.

Regards
Michel

Edit: argh! once again I didn't see your post, Ana, my apologies.

[Updated on: Tue, 05 May 2009 12:35]

Report message to a moderator

Re: Trigger Error [message #402331 is a reply to message #401591] Fri, 08 May 2009 15:42 Go to previous message
wtolentino
Messages: 196
Registered: March 2005
Senior Member

thanks everyone. this solved by assigning the role to the LPS schema.
Previous Topic: SQL Query
Next Topic: Convert string to time of day
Goto Forum:
  


Current Time: Fri Dec 02 12:05:37 CST 2016

Total time taken to generate the page: 0.09795 seconds