From: yf201@my-deja.com
Newsgroups: comp.databases.oracle,comp.databases.oracle.misc,comp.databases.oracle.server,comp.databases.oracle.tools
Subject: Re: Count records dynamically
Date: Sun, 10 Sep 2000 01:53:35 GMT
Organization: Deja.com - Before you buy.
Lines: 63
Message-ID: <8pepip$244$1@nnrp1.deja.com>
References: <1g5t5.808$N4.283106@juliett.dax.net>
NNTP-Posting-Host: 172.138.244.216
X-Article-Creation-Date: Sun Sep 10 01:53:35 2000 GMT
X-Http-User-Agent: Mozilla/4.0 (compatible; MSIE 5.0; Windows 95; DigExt)
X-Http-Proxy: 1.1 x72.deja.com:80 (Squid/1.1.22) for client 172.138.244.216
X-MyDeja-Info: XMYDJUIDyf201


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@juliett.dax.net>,
  "Robert Vabo" <robert.vabo@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@gecko.no
>
>


Sent via Deja.com http://www.deja.com/
Before you buy.