Re: Tablename variable. How To?

Try using the dbms_sql package. You have to dynamically create the sQL. You cannot have (pseudo programming)
create or replace tab_count(tab_name as varchar2) begin
select count(*) from :tab_name;
end;

You have to:
create or replace tab_count(tab_name as varchar2) declare
cursor as integer;
sql_text as varchar2;
res as number;
begin --create a cursor
cursor:=dbms_sql. ( );
sql_text:='select count(*) from '||tab_name; dbms_sql.execute(cursor,sql_text);
dbms_sql.Get Value or column 0; -- I don't have my manual with me but you get the idea.
end;

Jim
Howard Lee Harkness <hlh_nospam_at_excite.com> wrote in message news:B1C591717AFFF4B3.8F4C22AC644D601E.6DBD9241EDABDCC1_at_lp.airnews.net...
> On Thu, 22 Jul 1999 10:16:29 +0200, Helmut Hahn
> <helmut.hahn_at_bitoek.uni-bayreuth.de> wrote:
>
> >You could build a function gettablename returning this char and then try
> >select * from (select gettablename() from dual);
> >or direct
> >select * from (select tt from dual); (Don't know if this works!)
> >
>
> I asked a similar question in another thread (Q: table name as
> parameter to PL/SQL procedure), but didn't get any response.
> Apparently, what I wanted is not possible, but if I understand what
> you are saying, I can come close.
>
> However, I was not able to make your suggestions work. I suspect it
> is because I am not familiar enough with PL/SQL and Oracle, and I am
> not understanding something crucial.
>
> This is what I tried:
>
> SQL> create or replace function test_gettable
> 2 return varchar2
> 3 is
> 4 begin
> 5 return 'sec_tree';
> 6 end;
> 7 /
>
> Function created.
>
> SQL> select * from (select test_gettable() from dual);
> select * from (select test_gettable() from dual)
> *
> ERROR at line 1:
> ORA-00919: invalid function
>
> SQL>
>
> Please enlighten me.
> hlh_NOSPAM_at_excite.com is a valid, unmunged address!
> It is also so full of spam(!) that I don't read it.
Received on Tue Jul 27 1999 - 18:19:02 CDT