Re: dynamic sql and oracle forms

Knut Talman wrote:

> Daniel Morgan wrote:
> >
> > 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.
> > >
> > > Ooops, I am sorry, I didn't see that you are using Forms.
> > >
> > > Regards,
> > >
> > > Knut
> >
> > Your answer still holds. Just change SPOOL to ... use the UTL_FILE built-in package.

>

>

Sounds easy enough to me. I'm not sure how it could be made any easier.

Daniel Morgan Received on Tue Jun 25 2002 - 18:03:54 CEST