Dynamic return type for dynamic cursors possible??
Date: 19 Feb 2001 16:05:06 GMT
Message-ID: <96rg7i$gk4g8$1_at_hades.rz.uni-sb.de>
Hello,
I am trying to implement a generic pl/sql procedure which creates an sql file given a table name.
More specific: I wanna dump the content of a table in a file. The file should - when executed - delete the content of this table and insert the dumped values again. In order to keep this function generic, the name of the table is a parameter and not hard coded in the function.
So far there was no problem with accessing the meta-data like column-name or column-type, also the dynamic sql statement is easy... but a problem occurs with the declaration of the return type of the statement. Is there any way to keep it dynamic?
Answers please by email too. Thanks for any help in advance. The code i am taking about is below.
Regards,
Christoph Endres
procedure export_table(
tab in varchar2
)
is
tablename varchar2(128);
insert_prefix varchar2(2000);
TYPE CurTyp IS REF CURSOR;
cursorvar CurTyp;
-- results ?????%ROWTYPE; -- what to declare here????
begin
tablename := upper(tab);
insert_prefix := 'INSERT INTO ' || tablename || '(';
dbms_output.put_line('TRUNCATE '||tablename);
for field in (select column_name as name, data_type as typ from user_tab_columns where table_name=tablename order by column_id)
loop
insert_prefix := insert_prefix || field.name || ',';
end loop;
insert_prefix := substr(insert_prefix,0,length(insert_prefix)-1)||') VALUES ';
dbms_output.put_line(insert_prefix);
begin
open cursorvar for 'SELECT * FROM '||tablename;
loop
- how to declare the variable results here????
fetch cursorvar into results;
exit when cursorvar%NOTFOUND;
end loop;
close cursorvar;
exception when others then null;
end;
end;
--
email: endres_at_dfki.de / True love never dies. \ URL : http://www.dfki.de/~endres/ \ It kills! /