Re: Can PL/SQL do this???

Declare a cursor, use open and fetch statements. You can put the fetch statement in a loop to browse through the data that the cursor fetches.

Hope this answers your question.

Thanks,
Sreenivas

In article <8ge41h$gjv$1_at_nnrp1.deja.com>,   Thomas J. Kyte <tkyte_at_us.oracle.com> wrote:
> In article <5nhW4.20444$T41.482807_at_newsread1.prod.itd.earthlink.net>,
> "Buyatkik" <buyatkik-NOSPAM_at_letgo.com> wrote:
> > Hi,
> >
> > I was wondering if this is possible in PL/SQL?
> >
> > In VB I could do this with a recordset:
> >
> > i = 2
> > value = oRecordset.Fields("COLUMN" + i).value
> >
> > Now, value will contain the the COLUMN2 column value. Could this be
 done in
> > PL/SQL?
> >
> > Thanks
> >
> >
>
> Yes but you would use dynamic sql to do this (dbms_sql specificially).
> here is a small example of using dbms_sql:
>
> create or replace function dynquery( strCname1 in varchar2,
> strTname in varchar2,
> strCname2 in varchar2,
> strValue in integer ) return
> number
> is
> l_theCursor integer default dbms_sql.open_cursor;
> l_columnValue number;
> l_status integer;
> l_query varchar2(1000) default 'select ' || strCname1 || '
> from ' || strTname || '
> where ' || strCname2 || '
> = :x';
> begin
> dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
> dbms_sql.bind_variable( l_theCursor, ':x', strValue );
> dbms_sql.define_column( l_theCursor, 1, l_columnValue );
>
> l_status := dbms_sql.execute(l_theCursor);
>
> if ( dbms_sql.fetch_rows(l_theCursor) <= 0 ) then
> l_columnValue := NULL;
> else
> dbms_sql.column_value( l_theCursor, 1, l_columnValue );
> end if;
> dbms_sql.close_cursor(l_theCursor);
>
> return l_columnValue;
> exception
> when others then
> if ( dbms_sql.is_open( l_theCursor ) ) then
> dbms_sql.close_cursor( l_theCursor );
> end if;
> RAISE;
> end ;
> /
>
> exec dbms_output.put_line( dynquery( 'sal', 'emp', 'empno', 7902 ) );
>
> --
> Thomas Kyte (tkyte_at_us.oracle.com) Oracle Service Industries
> Howtos and such: http://osi.oracle.com/~tkyte/index.html
> Oracle Magazine: http://www.oracle.com/oramag
> Opinions are mine and do not necessarily reflect those of Oracle Corp
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue May 23 2000 - 00:00:00 CDT