| Cursors, dynamic PL/SQL [message #38701] |
Tue, 07 May 2002 02:36  |
jerome
Messages: 13
Registered: May 2000
|
Junior Member |
|
|
Hi, I need some help to achieve this procedure
***************************************************
create or replace procedure ps_trs_archive(str_tab_source in varchar2,
str_tab_dest in varchar2,
str_where in varchar2) is
TYPE cTempTyp IS REF CURSOR;
cTemp cTempTyp;
recTemp cTemp%rowtype;
str_liste1 varchar2(5000);
str_liste2 varchar2(5000);
str_insert1 varchar2(5000);
str_insert2 varchar2(5000);
str_cursor varchar2(5000);
str_temp varchar2(5000);
str_column varchar2(5000);
num_count number;
cursor cColumns is
select column_name
from user_tab_columns
where table_name = str_tab_source
order by column_id;
begin
-- I build my list of columns for my insert statement
open cColumns;
loop
fetch cColumns into str_column;
exit when cColumns%NOTFOUND;
str_liste1 := str_liste1 || ',' || str_column;
str_liste2 := str_liste2 || ',recTemp.' || str_column;
end loop;
close cColumns;
str_insert1 := 'insert into ' || str_tab_dest || '(' || substr(str_liste1,2,length(str_liste1) - 1) || ') ';
str_cursor := substr(str_liste1,2,length(str_liste1) - 1);
str_insert2 := ' values (' || substr(str_liste2,2,length(str_liste2) - 1) || ')';
open cTemp for 'select :a from :b where :c' using str_cursor,str_tab_source,str_where;
loop
fetch cTemp into recTemp;
exit when cTemp%notfound;
execute immediate str_insert1||str_insert2;
num_count := num_count + 1;
if num_count >=10000
then
commit;
num_count := 0;
end if;
end loop;
close cTemp;
end;
/
******************************************************
I just need inserting a record from a table (str_tab_source) into another table (str_tab_dest).
I have to commit every X records, and I don't want to access my table str_tab_source too much often.
So, I use open-for, execute immediate.
My problem is the "recTemp cTemp%rowtype;".
SQL> show error
Errors for PROCEDURE PS_TRS_ARCHIVE:
LINE/COL ERROR
-------- -----------------------------------------------------------------
10/15 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
10/15 PL/SQL: Item ignored
46/3 PL/SQL: SQL Statement ignored
46/20 PLS-00320: the declaration of the type of this expression is
incomplete or malformed
***************************************************
Has anyone any idea? How could I declare recTemp?
Is there a solution with the dbms_sql package instead?
Thanks for your answers.
Sorry for my poor english :o)
|
|
|
|
| Re: Cursors, dynamic PL/SQL [message #38704 is a reply to message #38701] |
Tue, 07 May 2002 09:30  |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
This is a horribly inefficient way to move data from one table to another. If you size your rollback segments properly, you won't have to commit every 10K rows. Just do:
execute immediate 'insert into ' || str_tab_dest || ' select * from ' || str_tab_source || ' where
|| str_where;
and then commit once at the end of the transaction.
You won't be able to use bind variables for anything here.
|
|
|
|
| Re: Cursors, dynamic PL/SQL [message #38742 is a reply to message #38701] |
Fri, 10 May 2002 00:24 |
jerome
Messages: 13
Registered: May 2000
|
Junior Member |
|
|
We're very short of free space on this DB. That's the reason why we use a "X-row commit".
It's too bad there's no full dynamic way(cursor, record type, statements execution) to do that.
I think I'll try another way...
Thanks
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
