Re: Passing tablenamens as parameters to procedures

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 12 Aug 1998 15:27:55 GMT
Message-ID: <35d1b46b.9567577@192.86.155.100>

A copy of this was sent to klaus.kloeser_at_bku.db.de (if that email address didn't require changing) On Wed, 12 Aug 1998 11:05:50 GMT, you wrote:

>Hi all,
>
>I want to pass strings as parameters to procedures / functions, is that
>possible ?
>
>With Transact SQL you can simply say EXECUTE "any sql statement" and it works,
>how is it done with Oracle ?
>
>Example:
>
>declare
>v1 varchar2(20) := 'tablename';
>v2 int;
>
>begin
>
> select count(*) into v2 from v1;
>
> dbms_output.put_line (to_char(v2));
>
>end;
>
>thx in advance
>

with dbms_sql yes it is....

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;
/

>Klaus Kloeser
>klaus.kloeser_at_bku.db.de
>
>-----== Posted via Deja News, The Leader in Internet Discussion ==-----
>http://www.dejanews.com/rg_mkgrp.xp Create Your Own Free Member Forum
 

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 Wed Aug 12 1998 - 10:27:55 CDT