Re: dynamic sql and oracle forms

From: Knut Talman <knut.talman_at_mytoys.de>
Date: Tue, 25 Jun 2002 12:30:33 +0200
Message-ID: <3D184649.538FD2D5_at_mytoys.de>


Knut Talman wrote:
>
> 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).
>
> Why so complicated? Just start SQL*Plus, do the needed settings (like "set head
> off" etc)
> 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
> result set, fetching every row into zus_rev_String and write it to the file.

[Quoted] Ooops, I am sorry, I didn't see that you are using Forms.

Regards,

Knut Received on Tue Jun 25 2002 - 12:30:33 CEST

Original text of this message