Re: dynamic sql and oracle forms

Knut Talman wrote:

> 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.

>

>

Your answer still holds. Just change SPOOL to ... use the UTL_FILE built-in package.

Daniel Morgan Received on Tue Jun 25 2002 - 17:08:21 CEST