Re: Using variables in FROM clause?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1998/01/09
Message-ID: <34ba8d0b.16770865_at_inet16>#1/1

On 9 Jan 1998 19:14:58 GMT, "Mike Guerrero" <mguerrer_at_mail.sdsu.edu> wrote:

>In PL/SQL can you use a variable in the FROM clause of a 'select' statement
>or do you have to use the DBMS_SQL package to do this. For example, the
>following shows what I'd like to do:
>
>tab1 varchar(30) ;
>c1 number(5);
>today varchar(10) := 'monday';
>
>if today = 'monday' then
> tab1 := 'mytab' /* mytab and yourtab a different tables */
>else
> tab1 := 'yourtab';
>
>select count(*) into c1 from tab1;
>
>
>
>What are my options, if any? Thanks.
>
>Mike

dbms_sql is it. It will 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
Bethesda MD  

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 Jan 09 1998 - 00:00:00 CET