Home » SQL & PL/SQL » SQL & PL/SQL » creating table in procedure
creating table in procedure [message #185318] Tue, 01 August 2006 03:49 Go to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Hi,
I am creating a procedure as:

CREATE OR REPLACE PROCEDURE RBI1.DYNSQL AS
  cur integer;
  rc  integer;
BEGIN
  cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(cur, 'CREATE TABLE X (Y DATE)', DBMS_SQL.NATIVE);
  rc := DBMS_SQL.EXECUTE(cur);
  DBMS_SQL.CLOSE_CURSOR(cur);
END;


But this is giving an error :

ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SYS_SQL", line 826
ORA-06512: at "SYS.DBMS_SQL", line 32
ORA-06512: at "RBI1.DYNSQL", line 6
ORA-06512: at line 2

Please advice.
Re: creating table in procedure [message #185327 is a reply to message #185318] Tue, 01 August 2006 04:26 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
You have insufficient privileges to create that table in the shema the procedure is run in.

Things you could do :

- Check the default schema of the user.
- Try to create the table RBI1.X instead of just X
- Give the user running the procedure the rights to create the table.

Also note :

Permissons aquired through roles don't work for procedures in some cases.



Re: creating table in procedure [message #185329 is a reply to message #185327] Tue, 01 August 2006 04:33 Go to previous messageGo to next message
monasingh
Messages: 229
Registered: May 2006
Location: Mumbai
Senior Member

Then what should I do?
Re: creating table in procedure [message #185337 is a reply to message #185329] Tue, 01 August 2006 04:49 Go to previous messageGo to next message
JSI2001
Messages: 1016
Registered: March 2005
Location: Scotland
Senior Member
Quote:

Then what should I do?

Did you even read the post?

Quote:

- Check the default schema of the user.
- Try to create the table RBI1.X instead of just X
- Give the user running the procedure the rights to create the table.

Re: creating table in procedure [message #185345 is a reply to message #185318] Tue, 01 August 2006 05:17 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

its really a googly to me...
You can very well create a table using execute immediate command inside the procedure...
Dont know why r u using cursor for it....

Let me know if there is any specific advantage ...So let me know if i am wrong with my concepts..


Naveen

Re: creating table in procedure [message #185366 is a reply to message #185345] Tue, 01 August 2006 06:44 Go to previous messageGo to next message
hobbes
Messages: 173
Registered: January 2006
Senior Member
navkrish wrote on Tue, 01 August 2006 05:17

You can very well create a table using execute immediate command inside the procedure...
Dont know why r u using cursor for it....

Let me know if there is any specific advantage

Perhaps the OP is on an older Oracle version?

I recall from my Forms development days that Forms 6i did not support EXECUTE IMMEDIATE: the front-end was on PL/SQL 8.0.6.3. Had to use DBMS_SQL for dynamic SQL.

Otherwise, I do think that EXECUTE IMMEDIATE would be simpler/more efficient for executing this kind of DDL.

Re: creating table in procedure [message #185372 is a reply to message #185318] Tue, 01 August 2006 07:07 Go to previous messageGo to next message
navkrish
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

Hobbes,
Thanks for the info..

Naveen
Re: creating table in procedure [message #185384 is a reply to message #185372] Tue, 01 August 2006 08:33 Go to previous messageGo to next message
joy_division
Messages: 4644
Registered: February 2005
Location: East Coast USA
Senior Member
This begs the question though, why would you want to create a table in a procedure? I don't see a DROP in there, therefore it will fail if you ever run it a second time. But there should be no need to create a table in a procedure.
Re: creating table in procedure [message #185423 is a reply to message #185384] Tue, 01 August 2006 12:08 Go to previous message
Kprattip
Messages: 15
Registered: January 2006
Location: INDIA
Junior Member
I agree with ThomasG, You need to check creation of procedure previliges in the Schema RBI1.

Thanks.
Previous Topic: Get data from more tables which have no direct relationship
Next Topic: Range value
Goto Forum:
  


Current Time: Sun Dec 11 02:34:50 CST 2016

Total time taken to generate the page: 0.04099 seconds