Re: COUNT(*) in DYNAMIC SQL

From: Hickend <David.Hicken_at_unforgettable.com>
Date: 1997/05/19
Message-ID: <01bc647b$c57d75e0$4e36adcf@hickend>#1/1

CREATE OR REPLACE
   FUNCTION get_count (v_table_name VARCHAR2)

      RETURN NUMBER
   is

      v_cursor INTEGER; 
      v_rows_processed INTEGER; 
      v_rows_fetched INTEGER; 
       
      out_count INTEGER; 
      exec_string VARCHAR2(1000) := 'select count(*) from ' ||

v_table_name;

   BEGIN

      v_cursor := DBMS_SQL.OPEN_CURSOR; 
      DBMS_SQL.PARSE (v_cursor, exec_string, DBMS_SQL.V7); 
      DBMS_SQL.DEFINE_COLUMN (v_cursor, 1, out_count); 
      v_rows_processed := DBMS_SQL.EXECUTE (v_cursor); 
      v_rows_fetched := DBMS_SQL.FETCH_ROWS(v_cursor); 
      DBMS_SQL.COLUMN_VALUE (v_cursor, 1, out_count); 
      DBMS_SQL.CLOSE_CURSOR(v_cursor); 
      RETURN out_count;

   END;
/

Usage:
select table_name, get_count(table_name) from user_tables;

Konstantin V Sartakov <skv_at_kpbank.ru> wrote in article <335E9F6A.5F7F_at_kpbank.ru>...
> jagdeeps_at_hotmail.com wrote:
> >
> > Hi all,
> >
> > I am sorry if this question has been already answered earlier.
> > I am trying to do a 'SELECT COUNT(*) ' from a table, whose name is
> > generated dynamically'. How do I do this ?
> >
> > Thanks
>
> Use DBMS_SQL package.
> Regards
> -------------------------------
> Konstantin V. Sartakov
> Kuzbassprombank
> Kemerovo
> Russia
> mailto:skv_at_kpbank.ru
>
  Received on Mon May 19 1997 - 00:00:00 CDT