Question on DBMS_SQL package
From: <pberetta_at_my-deja.com>
Date: Thu, 13 Jan 2000 19:49:18 GMT
Message-ID: <85la7n$f32$1_at_nnrp1.deja.com>
[Quoted] [Quoted] 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);
BEGIN
v_cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.DEFINE_COLUMN(v_cursor,1,v_count); v_select := 'SELECT COUNT (*) FROM ' || TNAME || ';';
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>
[Quoted] SQL> SELECT table_name, tcount(table_name) FROM user_tables; SELECT table_name, tcount(table_name) FROM user_tables
ERROR at line 1:
ORA-06571: Function TCOUNT does not guarantee not to update database
Date: Thu, 13 Jan 2000 19:49:18 GMT
Message-ID: <85la7n$f32$1_at_nnrp1.deja.com>
[Quoted] [Quoted] 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);
[Quoted] 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>
[Quoted] SQL> SELECT table_name, tcount(table_name) FROM user_tables; 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 - 20:49:18 CET