Home » SQL & PL/SQL » SQL & PL/SQL » Insufficient Privilege (Oracle 10g)
Insufficient Privilege [message #413525] Thu, 16 July 2009 04:44 Go to next message
subbu_tce
Messages: 98
Registered: July 2007
Location: pune
Member
Hi ,

Create Or Replace Procedure Ctc_Rpt
As
SqlString Varchar2(100):='Select * from abc';
Begin
Execute Immediate 'Create Table xyz As' || SqlString ;
End;
/

When i execute the Ctc_Rpt proc its going for insufficient privilege and its not creating the table.

Declare
SqlString Varchar2(100):='Select * from abc';
Begin
Execute Immediate 'Create Table xyz As' || SqlString ;
End;
/
but when am trying the same code in ananymas block its creating table without any errors.

Am executing both the p/sql in same schema.please advice me.
Re: Insufficient Privilege [message #413532 is a reply to message #413525] Thu, 16 July 2009 04:55 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You might have granted the create table privilege using role.
Try using normal grant.

By
Vamsi
Re: Insufficient Privilege [message #413533 is a reply to message #413525] Thu, 16 July 2009 05:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a FAQ, please search BEFORE posting.

Please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Insufficient Privilege [message #413575 is a reply to message #413525] Thu, 16 July 2009 08:39 Go to previous messageGo to next message
ganludong
Messages: 5
Registered: July 2009
Location: SICHUAN NORMAL UNIVERSITY...
Junior Member

you can try to grant create any table to username....


Re: Insufficient Privilege [message #413577 is a reply to message #413575] Thu, 16 July 2009 08:42 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
ganludong wrote on Thu, 16 July 2009 14:39

you can try to grant create any table to username....




Not necessary and not a good idea. Privileges with any in their name should generally be avoided.

All that's required here is a direct grant of create table.
Re: Insufficient Privilege [message #413581 is a reply to message #413575] Thu, 16 July 2009 08:56 Go to previous message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
ganludong wrote on Thu, 16 July 2009 15:39

you can try to grant create any table to username....

You can also put the procedure in SYS schema.

(Just a joke, don't do it).

Regards
Michel

Previous Topic: How to read a tab delimete file and load in to table using utl_file package
Next Topic: IS NULL and ='NULL'
Goto Forum:
  


Current Time: Tue Dec 06 14:06:51 CST 2016

Total time taken to generate the page: 0.22630 seconds