Home » SQL & PL/SQL » SQL & PL/SQL » executing a procedure containg a global temporary rable
executing a procedure containg a global temporary rable [message #445681] Wed, 03 March 2010 06:10 Go to next message
priyasinha396
Messages: 15
Registered: November 2009
Location: Delhi
Junior Member
create or replace procedure test
as
stmt varchar2(2000);
begin
EXECUTE IMMEDIATE 'CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS';

stmt := 'INSERT INTO tt_Local SELECT cardnumber FROM cards';
execute immediate stmt;
end;


-- when am trying to execute this

begin
test;
end;

-- showing ora-01031, insufficient privileges.

can anybody please help?


Re: executing a procedure containg a global temporary rable [message #445682 is a reply to message #445681] Wed, 03 March 2010 06:13 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Which line are you getting the error on?
Re: executing a procedure containg a global temporary rable [message #445686 is a reply to message #445682] Wed, 03 March 2010 06:33 Go to previous messageGo to next message
priyasinha396
Messages: 15
Registered: November 2009
Location: Delhi
Junior Member
line number 2
Re: executing a procedure containg a global temporary rable [message #445687 is a reply to message #445681] Wed, 03 March 2010 06:33 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
Could you execute the following from sql prompt, copy and paste the output here.

sql> set role none
sql> CREATE GLOBAL TEMPORARY TABLE tt_Local(ID VarChar2(38)) ON COMMIT PRESERVE ROWS;


Least said, why do you want to create objects on the fly. Very very bad design. If possible by all means avoid it.

Regards

Raj
Re: executing a procedure containg a global temporary rable [message #445688 is a reply to message #445681] Wed, 03 March 2010 06:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/ You must have the CREATE TABLE privilege granted to you DIRECTLY not via a role

2/ You must NOT execute a query inside "execute immediate" unless it is dynamic, your insert is static

3/ you must NOT create table on the fly, your GTT MUST be created before. This is Oracle not Sybase

Regards
Michel
Re: executing a procedure containg a global temporary rable [message #445694 is a reply to message #445688] Wed, 03 March 2010 06:50 Go to previous messageGo to next message
priyasinha396
Messages: 15
Registered: November 2009
Location: Delhi
Junior Member
yes , You are right . I created the table seperately (not at run time) and the issue is resolved.

Thanks
Re: executing a procedure containg a global temporary rable [message #445696 is a reply to message #445688] Wed, 03 March 2010 06:54 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Actually thats not the issue....(as yours is a privilege issue)
Its a bad design...If you create that as run time (with the same name) definately you will get
Quote:
ORA-00955: name is already used by an existing object
.

sriram Smile

[Updated on: Wed, 03 March 2010 06:54]

Report message to a moderator

Re: executing a procedure containg a global temporary rable [message #445701 is a reply to message #445688] Wed, 03 March 2010 07:09 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
Michel Cadot wrote on Wed, 03 March 2010 12:34

2/ You must NOT execute a query inside "execute immediate" unless it is dynamic, your insert is static

To be fair the insert has to be dynamic (in this instance) because the create table is. I would have just skipped to point 3.
Re: executing a procedure containg a global temporary rable [message #445704 is a reply to message #445696] Wed, 03 March 2010 07:13 Go to previous messageGo to next message
cookiemonster
Messages: 12403
Registered: September 2008
Location: Rainy Manchester
Senior Member
ramoradba wrote on Wed, 03 March 2010 12:54
Actually thats not the issue....(as yours is a privilege issue)
Its a bad design...If you create that as run time (with the same name) definately you will get
Quote:
ORA-00955: name is already used by an existing object
.

sriram Smile


Plus doing everything dynamically doesn't do the performance any good.

@priyasinha396 - I don't know why you're using a temp table but I seriously doubt you need it. Real uses for temp tables in oracle is exceedingly rare. We don't need them to improve performance like some other DBs, ususally they just make performance worse. See if you can do whatever you're doing without the temp table. It'll probably be easier to code and will perform better.
Re: executing a procedure containg a global temporary rable [message #445709 is a reply to message #445701] Wed, 03 March 2010 07:36 Go to previous message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
cookiemonster wrote on Wed, 03 March 2010 14:09
Michel Cadot wrote on Wed, 03 March 2010 12:34

2/ You must NOT execute a query inside "execute immediate" unless it is dynamic, your insert is static

To be fair the insert has to be dynamic (in this instance) because the create table is. I would have just skipped to point 3.

I should logically swap point 2 and 3, but I wanted to put the points in the order of how important it is (imo).

Regards
Michel

[Updated on: Wed, 03 March 2010 07:37]

Report message to a moderator

Previous Topic: Analytic Function
Next Topic: suitable set filter
Goto Forum:
  


Current Time: Fri Dec 02 14:31:40 CST 2016

Total time taken to generate the page: 0.36480 seconds