Re: Tablename as a paramter

From: Christopher Beck <clbeck_at_us.oracle.com>
Date: Mon, 12 Oct 1998 17:31:44 GMT
Message-ID: <36233a68.11167838@dcsun4.us.oracle.com>

On 12 Oct 1998 16:05:27 GMT, NBronke_at_t-online.de (Nicolas Bronke) wrote:

>Is it possible to place a tablename into the parameter list of a
>procedure/funtion?

Yes. If you use dynamic SQL.

eg.

create or replace
function getCount( tableName varchar2 ) return number is

  n number;
  c number;
  s number;

begin
  c := dbms_sql.open_cursor;
  dbms_sql.parse( c,

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

  dbms_sql.define_column( c, 1, n );
  s := dbms_sql.execute( c );
  s := dbms_sql.fetch_rows( c );
  dbms_sql.column_value( c, 1, n );
  dbms_sql.close_cursor( c );
  return n;
end getCount;

>
>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
>
Received on Mon Oct 12 1998 - 12:31:44 CDT