OraFAQ Forum: SQL & PL/SQL » ORA-01031: insufficient privileges error in Oracle

Home » SQL & PL/SQL » SQL & PL/SQL » ORA-01031: insufficient privileges error in Oracle (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

ORA-01031: insufficient privileges error in Oracle [message #600931]

Thu, 14 November 2013 02:22

subhadip.chanda
Messages: 64
Registered: May 2007

Member

Hi,

I am receiving "ORA-00903: invalid table name" error while running a stored procedure. Body of the procedure is as follows :

create or replace procedure test as
begin

execute immediate
'CREATE or replace view test12
as select 1 a from dual ';

end;
When running the same code part from an anonymous block, it is working fine.
Could anybody tell me what is the reason?

Regards,

[Updated on: Thu, 14 November 2013 02:25]

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600934 is a reply to message #600931]

Thu, 14 November 2013 02:38

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

Can you explain why the post title lists one error, but you list a different one in the post itself?

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600935 is a reply to message #600934]

Thu, 14 November 2013 02:41

subhadip.chanda
Messages: 64
Registered: May 2007

Member

Yeah .. Title is wrong .. Please consider the body of post.

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600936 is a reply to message #600935]

Thu, 14 November 2013 02:42

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

post the complete error stack.

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600938 is a reply to message #600936]

Thu, 14 November 2013 02:45

subhadip.chanda
Messages: 64
Registered: May 2007

Member

Complete Error Stack :

ORA-01031: insufficient privileges
ORA-06512: at "USFSR_LOAD.TEST", line 4
ORA-06512: at line 1

[Updated on: Thu, 14 November 2013 02:46]

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600941 is a reply to message #600931]

Thu, 14 November 2013 02:48

dariyoosh
Messages: 538
Registered: March 2009
Location: France

Senior Member

It is not a good programming practice to have DDL within EXECUTE IMMEDIATE.

As far as the error's concerned, it doesn't seem to be an error at the code. It works for me

create or replace procedure test
authid definer
as
begin
    execute immediate 'create or replace view test12 as select 1 a from dual';
end;
/
show errors;
--
--
execute test;
--
--
select a from test12;

Which gives the following output

Procedure created.

No errors.

Procedure created.

No errors.

PL/SQL procedure successfully completed.


         A
----------
         1

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600942 is a reply to message #600938]

Thu, 14 November 2013 02:49

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

So the title is right and the post body is wrong.

AskTom on roles and procedures

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600950 is a reply to message #600942]

Thu, 14 November 2013 03:11

subhadip.chanda
Messages: 64
Registered: May 2007

Member

Yes.

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600951 is a reply to message #600941]

Thu, 14 November 2013 03:15

subhadip.chanda
Messages: 64
Registered: May 2007

Member

So, What could be the possible reason, by which I am getting stuck.

Report message to a moderator

Re: ORA-01031: insufficient privileges error in Oracle [message #600954 is a reply to message #600951]

Thu, 14 November 2013 03:38

John Watson
Messages: 8931
Registered: January 2010
Location: Global Village

Senior Member

That link to AskTom has the exact test you need to run. The test will confirm whether the problem is because your privileges have been directly granted, or granted only through a role. This is usually the problem.

Report message to a moderator

Previous Topic:	To find all the tables and their columns where the data has changed in a table
Next Topic:	Date comparison issue

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 15:02:25 CDT 2024