Re: variables in cursors

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 30 May 1998 00:25:33 GMT
Message-ID: <356f5119.838786@192.86.155.100>

A copy of this was sent to Kal Khatib <kkhatib_at_cisco.com> (if that email address didn't require changing) On Fri, 29 May 1998 13:06:09 -0700, you wrote:

>I'm new to PLSQL..
>
>How do I do this:
>I'm trying to write a procedure that takes in 2 variables, and puts out
>a PL/SQL table
>something like
>
>procedure generic_get_distinct (field in varchar2, table in varchar2,
>OUT someting)
>IS
>cursor is select distinct field from table; // where field and
>table are variables passed into the proc.
>
>begin
>.... some code
>End generic_get_distinct;
>
>
>How do I get the cursor to use the values of field and table passed to
>it?
>
>thank you
>

You can use a cursor and do it as above. You have to use dynamic sql (dbms_sql) to do this.

It might look like:

create or replace procedure demo( p_fieldname in varchar2,

                                  p_tablename 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 ' || p_tablename,
                     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 demo;
/  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

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

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Fri May 29 1998 - 19:25:33 CDT