executing a procedure containg a global temporary rable [message #445681] |
Wed, 03 March 2010 06:10  |
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 #445687 is a reply to message #445681] |
Wed, 03 March 2010 06:33   |
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 #445709 is a reply to message #445701] |
Wed, 03 March 2010 07:36  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
cookiemonster wrote on Wed, 03 March 2010 14:09Michel 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
|
|
|