Re: dynamic sql and oracle forms

Martin Burkert wrote:  

> I would like to print a dynamic sql-Statement to a file. The problem is
> following....
>
> =============================================
>
> SELECT 'INSERT INTO tableVALUES
> ('''||table_element1||''','''||table_element2||''','''||table_element3||''')
> ;' into zus_rev_String
> FROM table;
>
> "But when the sql-Statments returns more than one line ==> error, because
> zus_rev_String is a varchar2.
> but text_io.put_line needs a string or varchar."
>
> out_file := Text_IO.Fopen(filename, 'w');
>
> Text_IO.Put_line(out_file, zus_rev_String);
> Text_IO.Fclose (out_file);
>
> How is it possible to write a dynamic sql-statment to a file (with more than
> one line).

[Quoted] Why so complicated? Just start SQL*Plus, do the needed settings (like "set head off" etc)
[Quoted] and spool the output to a file (spool filename). If you have to do it with PL/SQL, you have to open a cursor and loop through the [Quoted] result set, fetching every row into zus_rev_String and write it to the file.

Regards,

Knut Received on Tue Jun 25 2002 - 12:29:18 CEST