Re: Count records dynamically
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