Home » SQL & PL/SQL » SQL & PL/SQL » Compilation Issue (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production)
Compilation Issue [message #575034] Fri, 18 January 2013 03:25 Go to next message
sgollapudi
Messages: 9
Registered: January 2013
Location: HYDERABAD
Junior Member
Hello All,

When I executed update on scott.emp on other schema say ELIXIR schema it is getting executed properly but
when I write that statement inside a procedure in ELIXIR schema it is failing.Then I verified on sys priveleges for ELIXIR
it has update any table privilege is there but it has not privileges on emp table. then what is the reason for that failing inside procedure but not
while executing update statement.


SQL> SHO USER
USER is "ELIXIR "
SQL>
SQL> update scott.emp set sal=100 where deptno=20;

5 rows updated.

SQL>
SQL> create or replace procedure sp1 as
2 begin
3 update scott.emp set sal=100 where deptno=10;
4 commit;
5 end;
6 /

Warning: Procedure created with compilation errors.

SQL>
SQL> SHO ERR
Errors for PROCEDURE SP1:

LINE/COL ERROR
-------- -----------------------------------------------------------------
3/1 PL/SQL: SQL Statement ignored
3/14 PL/SQL: ORA-01031: insufficient privileges
SQL>
SQL> select * from dba_sys_privs where privilege like '%TABLE%' AND GRANTEE='DBO';

GRANTEE PRIVILEGE ADM
------------------------------ ---------------------------------------- ---
ELIXIR SELECT ANY TABLE NO
ELIXIR DROP ANY TABLE NO
ELIXIR COMMENT ANY TABLE NO
ELIXIR UNLIMITED TABLESPACE YES
ELIXIR CREATE ANY TABLE NO
ELIXIR ALTER ANY TABLE NO

6 rows selected.


SQL> select * from dba_tab_privs where table_name='SCOTT'
2 ;

no rows selected

Thanks in advance.
sgollapudi.

Re: Compilation Issue [message #575035 is a reply to message #575034] Fri, 18 January 2013 03:29 Go to previous messageGo to next message
Littlefoot
Messages: 19823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Grants acquired via roles won't work in PL/SQL - user SCOTT has to grant UPDATE privileges to user ELIXIR directly.
Re: Compilation Issue [message #575036 is a reply to message #575034] Fri, 18 January 2013 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 59816
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topics:

cookiemonster wrote on Tue, 15 January 2013 14:51
Please read and follow How to use [code] tags and make your code easier to read?
...


sgollapudi wrote on Thu, 17 January 2013 16:27
...
*BlackSwan added {code} tags. Please do so yourself in the future.


Regards
Michel

[Updated on: Fri, 18 January 2013 03:41]

Report message to a moderator

Re: Compilation Issue [message #575037 is a reply to message #575035] Fri, 18 January 2013 03:50 Go to previous message
sgollapudi
Messages: 9
Registered: January 2013
Location: HYDERABAD
Junior Member
Hi Littlefoot, you have provide me a very good informative....

Thank you so much !!!

Regards,
sgollapudi.
Previous Topic: about queries
Next Topic: bulk collect
Goto Forum:
  


Current Time: Fri Nov 28 09:49:16 CST 2014

Total time taken to generate the page: 0.05401 seconds