Re: Passing tablenamens as parameters to procedures

klaus.kloeser_at_bku.db.de wrote:
>
> 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;

Fun with DBMS_SQL 101 ;)

declare

   hc integer := dbms_sql.open_cursor;
   rc integer;
   v1 varchar2(20) := 'tablename';
   v2 integer;
begin

   dbms_sql.parse(hc, 'select count(*) from ' || v1, dbms_sql.v7);    dbms_sql.define_column(hc, 1, v2);
   rc := dbms_sql.execute(hc);
   dbms_output.put_line(to_char(v2));
end;

Not tested, but should (?) be OK.

-Tony- Received on Sat Aug 15 1998 - 22:39:25 CDT