Re: Can PL/SQL do this???

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: 2000/05/23
Message-ID: <8ge41h$gjv$1@nnrp1.deja.com>#1/1

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.

Received on Tue May 23 2000 - 00:00:00 CDT