Re: Tablename as a paramter

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 12 Oct 1998 17:24:05 GMT
Message-ID: <36253b0b.18211006@192.86.155.100>

A copy of this was sent to NBronke_at_t-online.de (Nicolas Bronke) (if that email address didn't require changing) On 12 Oct 1998 16:05:27 GMT, you wrote:

>Is it possible to place a tablename into the parameter list of a
>procedure/funtion?
>
>e.g.
>
>create function (tablename varchar2)
>return number
>as
> n number;
>begin
> select count(*) into n from tablename;
> return n;
>end;
>
>Kind regards
>Nicolas Bronke
>

In order to do this, you must use dynamic sql -- you can only bind values, not identifiers, in 'static' sql... It might look like:

create or replace function countem( p_tname in varchar2 ) return number is

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   number default NULL;
    l_status        integer;

begin

    dbms_sql.parse( l_theCursor,

                    'select count(*) from ' || p_tname,
                     dbms_sql.native );

    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
        dbms_sql.column_value( l_theCursor, 1, l_columnValue );

    end if;

    dbms_sql.close_cursor(l_theCursor);

    return l_columnValue;
end countem;
/  

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 Mon Oct 12 1998 - 12:24:05 CDT