Re: Question on DBMS_SQL package

hi

[Quoted] try this

SELECT table_name, tcount('table_name') FROM user_tables

pberetta_at_my-deja.com wrote:

> Tried to create and use what I thought was a rather simple function
> using the DBMS_SQL package. All I'm attempting to do is create a
> dynamic SQL statement that will return the count of records in any
> table. Wrote the following:
> CREATE OR REPLACE FUNCTION TCOUNT(TNAME VARCHAR2)
> RETURN NUMBER IS OUTCOUNT NUMBER(10);
> v_select varchar2(100);
> v_cursor number;
> v_count number(10);
> BEGIN
> v_cursor := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_count);
> v_select := 'SELECT COUNT (*) FROM ' || TNAME || ';';
> DBMS_SQL.PARSE(v_cursor,v_select,DBMS_SQL.V7);
> DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_count);
> DBMS_SQL.CLOSE_CURSOR(v_cursor);
> outcount := v_count;
> RETURN outcount;
> END;
> /
> The function compiles without errors, but when I attempt to invoke it
> in a SELECT statement, the result is:
> SQL>
> SQL> CREATE OR REPLACE FUNCTION TCOUNT(TNAME VARCHAR2)
> RETURN NUMBER IS OUTCOUNT NUMBER(10);
> v_select varchar2(100);
> v_cursor number;
> v_count number(10);
> BEGIN
> v_cursor := DBMS_SQL.OPEN_CURSOR;
> DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_count);
> v_select := 'SELECT COUNT (*) FROM ' || TNAME || ';';
> DBMS_SQL.PARSE(v_cursor,v_select,DBMS_SQL.V7);
> DBMS_SQL.COLUMN_VALUE(v_cursor,1,v_count);
> DBMS_SQL.CLOSE_CURSOR(v_cursor);
> outcount := v_count;
> RETURN outcount;
> END;
> /
> SELECT table_name, tcount(table_name) FROM user_tables
> *
> ERROR at line 1:
> ORA-06571: Function TCOUNT does not guarantee not to update database
>
> I'm at a loss as to how to correct this problem (or if it can be
> corrected). Any help would be appreciated.
> Thanks,
> Paul
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Thu Jan 13 2000 - 21:53:39 CET