| Creation of temp table [message #2981] |
Wed, 28 August 2002 03:13  |
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 #2983 is a reply to message #2981] |
Wed, 28 August 2002 04:58  |
Raj
Messages: 411
Registered: November 1998
|
Senior Member |
|
|
HI,
u have to use dynamic sql for creation/insertion and dropping of table which is not known at design time.
following example clears u'r doubts.
create procedure testing_763(name in varchar2)
is
v_stmnt varchar2(100);
l_cur BINARY_INTEGER := dbms_sql.open_cursor;
v_cnt PLS_INTEGER := -1;
begin
v_stmnt :='create table temp_'||name||' as select * from test_76';
EXECUTE IMMEDIATE v_stmnt;
v_cnt:=sql%rowcount;
IF DBMS_SQL.IS_OPEN (l_cur) THEN
DBMS_SQL.CLOSE_CURSOR (l_cur);
END IF;
dbms_output.put_line('the number of rows in temp_name is ' ||v_cnt);
end;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
