Re: dynamic sql and oracle forms

Safeer wrote:

> why don't you use a cursor and then "execute immediate"?
>
> "Knut Talman" <knut.talman_at_mytoys.de> wrote in message
> news:3D1845FE.7B96BB89_at_mytoys.de...> 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.
> >
> > Regards,
> >
> > Knut

Because that won't create a file external to the database which is what was asked.

Daniel Morgan Received on Tue Jun 25 2002 - 17:30:58 CEST