Re: More help on Dynamic SQL

From: Thomas kyte <tkyte_at_us.oracle.com>
Date: 1997/03/25
Message-ID: <333854f1.8180262@newshost>#1/1

You'll want to use a varchar2(2000) to hold an arbitrary result, something like:

create or replace procedure show_a_field( p_fieldname in varchar2 ) is

    l_theCursor     integer;
    l_columnValue   varchar2(2000);
    l_status        integer;

begin  

    l_theCursor := dbms_sql.open_cursor;  

    dbms_sql.parse( l_theCursor,

                    'select ' || p_fieldname || ' from emp',
                     dbms_sql.native );
 

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );  

    l_status := dbms_sql.execute(l_theCursor);  

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        dbms_sql.column_value( l_theCursor, 1, l_columnValue );
        dbms_output.put_line( l_columnValue );

    end loop;
    dbms_sql.close_cursor(l_theCursor);
end show_a_field;
/

If you compile this in scott's schema, you can:

SQL> set serveroutput on
SQL> exec show_a_field( 'ename' );
SQL> exec show_a_field( 'hiredate' );
SQL> exec show_a_field( 'mgr' );
SQL> exec show_a_field('to_char(hiredate,''dd-mon-yyyy hh24:mi:ss'')')

and so on....

If you want it a little more generic, so you don't know how many columns you are getting either, you can

create or replace procedure show_a_field( p_fieldname in varchar2 ) is

    l_theCursor     integer;
    l_columnValue   varchar2(2000);
    l_status        integer;
    l_colCnt        number default 0;

begin  

    l_theCursor := dbms_sql.open_cursor;  

    dbms_sql.parse( l_theCursor,

                    'select ' || p_fieldname || ' from emp',
                     dbms_sql.native );
 
    for i in 1 .. 255 loop
        begin
            dbms_sql.define_column( l_theCursor, i, l_columnValue,
2000 );
            l_colCnt := l_colCnt + 1;
        exception
            when others then
                if ( sqlcode = -1007 ) then
                    exit;
                else
                    raise;
                end if;
        end;

    end loop;  

    dbms_sql.define_column( l_theCursor, 1, l_columnValue, 2000 );  

    l_status := dbms_sql.execute(l_theCursor);  

    loop

        exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
        for i in 1 .. l_colCnt loop
            dbms_sql.column_value( l_theCursor, i, l_columnValue );
            dbms_output.put( 'field ' || to_char(i) || ': '  );
            dbms_output.put_line( l_columnValue );
        end loop;
        dbms_output.put_line( '----------------' );

    end loop;
    dbms_sql.close_cursor(l_theCursor);
end show_a_field;
/

Then you can:

SQL> exec show_a_field( 'ename, hiredate, sal' );

and so on. Consider the define_column routine to be a type conversion routine as opposed to a real 'bind'. Since pl/sql doesn't have addresses, the define_column only looks at the - datatype
- max length

The column_value routine is expected to send down a variable of the same type and same length (doesn't have to be the same variable used int eh define_column call, just the same type and length)..

On 25 Mar 1997 19:45:51 GMT, bcheng_at_cs.buffalo.edu (Biao Cheng (Sam)) wrote:

>Hi,
>
>Several people responded my previous post regarding passing
>a field name to a stored procedure. And all said that I should
>use DBMS-SQL package. I have tried this package, if I pass a
>table name, I have no problem, But when I try passing fieldname,
>before you have the statement
>
> dbms_sql.execute(mycursor);
>
>You need to call
>
> dbms_sql.define_column(...),
>
>since the field is unknown, its type is unknown, so how should
>I go about call define_column?
>
>Thanks for your help. If you get confused about my words,
>here is my original post.
>
>Biao Cheng (Sam) wrote:
>>
>> Hi,
>>
>> I have a question to ask:
>>
>> I need to create a stored procedure which contains a string
>> type parameter, say
>>
>> CREATE OR REPLACE proc_1(fieldname CHAR)
>> ....
>>
>> Now, suppose the actual parameter I am passing is a field
>> name in a table, my question is How I am able to run a
>> SQL statement, something like this:
>>
>> SELECT "fieldname" from table1;
>>
>> Is there any way to do this? Any input is highly appreciated.
>
>

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Tue Mar 25 1997 - 00:00:00 CST