Re: Count records dynamically

From: <yf201_at_my-deja.com>
Date: Sun, 10 Sep 2000 01:53:35 GMT
Message-ID: <8pepip$244$1_at_nnrp1.deja.com>


To do what you want, you have to use DBMS_SQL package. It allows executing dynamic queries.

you need might want to verify the syntax

handle number;
query varchar2(1000);
status number;

BEGIN
  handle := DMMS_SQL.openCursor();
  query := SELECT COUNT(*) FROM || TableName;

  DBMS_SQL.PARSE(handle,sql);
  status =DBMS_SQL.EXECUTE(handle); -- status will have number of affected rows

  you need to define a variable that will contain the output of the query. I don't remember that exact syntax, but it is defined in the DBMS_SQL package (it only has 5 or so commands);

 DMBS_SQL.CLOSE(handle);

You need to use the DBMS_SQL package only if you construct dynamic queries.
hope that helps...

Yevgeniy

In article <1g5t5.808$N4.283106_at_juliett.dax.net>,   "Robert Vabo" <robert.vabo_at_gecko.no> wrote:
> I want to count number of records in a table using a cursor so I can
 use it
> on any table
>
> CURSOR CountRecs (TableName VARCHAR2)
> IS
> SELECT COUNT(*) FROM TableName;
>
> When I compile this I get an error message :
>
> PLS-00356: 'TABLENAME' must name a table to which the user has access
>
> Any suggestions on another method to do this ?
>
> --
> Regards
> Robert Vabo
> Application developer
> Gecko Informasjonssystemer AS
> www.gecko.no
> robert.vabo_at_gecko.no
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Sep 10 2000 - 03:53:35 CEST

Original text of this message