| creation of temp table [message #39959] |
Wed, 28 August 2002 03:11  |
mani
Messages: 105
Registered: September 1999
|
Senior Member |
|
|
Hi,
I have one procedure which accepts table name as parameter.
inside that procedure,i have to create the table with name as the passed parameter appended with temp_.
for ex.if the parameter value is 'TTA',then my temp table will be TEMP_TTA.
And here am doing some insertion and updation in that table.finally am dropping that table inside the proc itself.
For ex.I need something like below:
procedure proc1 (table_name IN varchar2(3))
is
create table temp_table_name
as
select * from table_name;
insert into temp_table_name(.........)
<<my update statements>>
drop temp_table_name:
any idea of doing this???
Thanks
Victoria
|
|
|
|
| Re: creation of temp table [message #39960 is a reply to message #39959] |
Wed, 28 August 2002 05:11  |
Amit
Messages: 166
Registered: February 1999
|
Senior Member |
|
|
u cannot fire a ddl inside a pl/sql script. U have to use either "Execute Immediate" or dbms_parsing statement for creating at run time.
The code will be like that:
procedure proc1 (table_name IN varchar2(3))
is
----
----
begin
Execute Immediate 'create table temp_table_name
as select * from table_name';
end;
begin
Execute immediate 'insert into temp_table_name(.........)'
end;
begin
Execute immediate 'drop table table_name';
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
