Re: dynamic sql and oracle forms

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.

[Quoted] What I meant with my "Ooops..." was that he can't use the first suggestion but has (AFAIK) to use the second one. Is there any other way than looping through the result set and utl_file.putline the record? Anything easier?

Regards,

Knut Received on Tue Jun 25 2002 - 17:45:14 CEST