Home » SQL & PL/SQL » SQL & PL/SQL » PL/SQL: ORA-00942: table or view does not exist (Oracle 11g, 11.2.0.3.0)
PL/SQL: ORA-00942: table or view does not exist [message #654902] Tue, 16 August 2016 12:57 Go to next message
wtolentino
Messages: 340
Registered: March 2005
Senior Member
create or replace trigger snet_tiubr_fnd_app_form
  before insert or update on snet_fnd_app_form
  for each row
declare
  vUserName  varchar2(40);
begin
  if INSERTING then
    if :new.created_date is null then
      :new.created_date := sysdate;
    end if;

    if :new.app_form_id is null then
      select wtolentino.S_fnd_app_form.nextval
        into :new.app_form_id
        from dual;
    end if;

    if :new.created_by is null then
      begin
        select ctut_id
          into :new.created_by
          from tdbxdata.account_reference
         where account_id = sys_context('USERENV','SESSION_USER');
      exception
        when no_data_found then
          null;
      end;
    end if;
  end if;

  if UPDATING then
    if :new.updated_date is null then
      :new.updated_date := sysdate;
    end if;

    if :new.updated_by is null then
      begin
        select ctut_id
          into :new.updated_by
          from tdbxdata.account_reference
         where account_id = sys_context('USERENV','SESSION_USER');
      exception
        when no_data_found then
          null;
      end;
    end if;
  end if;
end;
/

i am attempting to compile a trigger and was getting an error

SQL> show errors;
Errors for TRIGGER SNET_TIUBR_FND_APP_FORM:

LINE/COL ERROR
-------- -----------------------------------------------
19/25 PL/SQL: ORA-00942: table or view does not exist
17/9 PL/SQL: SQL Statement ignored
37/25 PL/SQL: ORA-00942: table or view does not exist
35/9 PL/SQL: SQL Statement ignored

when i attempt to remove this line of code from the trigger it works
      begin
        select ctut_id
          into :new.created_by
          from tdbxdata.account_reference
         where account_id = sys_context('USERENV','SESSION_USER');
      exception
        when no_data_found then
          null;
      end;
and
      begin
        select ctut_id
          into :new.updated_by
          from tdbxdata.account_reference
         where account_id = sys_context('USERENV','SESSION_USER');
      exception
        when no_data_found then
          null;
      end;


i have a select grants to the tdbxdata.account_reference thru a role. do i need a direct select grants? please advise thanks.
SQL> select count(*) from tdbxdata.account_reference;

  COUNT(*)
----------
     13841




Re: PL/SQL: ORA-00942: table or view does not exist [message #654903 is a reply to message #654902] Tue, 16 August 2016 13:02 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>do i need a direct select grants?
Yes, privileges acquired via ROLE do NOT apply within named PL/SQL procedures
Re: PL/SQL: ORA-00942: table or view does not exist [message #654906 is a reply to message #654902] Tue, 16 August 2016 14:09 Go to previous messageGo to next message
John Watson
Messages: 8559
Registered: January 2010
Location: Global Village
Senior Member
Synchronicity! I put up a blog about this only last week which might explain. If it doesn't please comment -
http://www.orafaq.com/node/3003
Re: PL/SQL: ORA-00942: table or view does not exist [message #654949 is a reply to message #654906] Wed, 17 August 2016 06:45 Go to previous message
wtolentino
Messages: 340
Registered: March 2005
Senior Member
thank you blackswan and John Watson
Previous Topic: Find unique and sorted field value
Next Topic: Renaming a table
Goto Forum:
  


Current Time: Sun Jun 13 14:32:39 CDT 2021