Re: Question on DBMS_SQL package
From: Bob Bain <bob.bain_at_terra-nova.e-mail.com>
Date: Tue, 18 Jan 2000 12:41:01 -0000
Message-ID: <RcZg4.47$7R.650_at_news.colt.net>
BEGIN
Date: Tue, 18 Jan 2000 12:41:01 -0000
Message-ID: <RcZg4.47$7R.650_at_news.colt.net>
Paul,
[Quoted] First off you will need to rewrite your function so that it looks something like this :-
[Quoted] [Quoted] CREATE OR REPLACE FUNCTION TCOUNT(TNAME IN VARCHAR2) RETURN NUMBER AS OutCount INTEGER;
v_dummy INTEGER; v_select varchar2(100); v_cursor number; v_count number(10);
BEGIN
-- -- Get a new cursor handle -- v_cursor := DBMS_SQL.OPEN_CURSOR; -- v_select := 'SELECT COUNT(*) FROM ' || TNAME; -- DBMS_SQL.PARSE(v_cursor, v_select, DBMS_SQL.V7); DBMS_SQL.DEFINE_COLUMN(v_cursor, 1, v_count); -- v_dummy := DBMS_SQL.EXECUTE_AND_FETCH(v_cursor); -- DBMS_SQL.COLUMN_VALUE(v_cursor, 1, v_count); -- DBMS_SQL.CLOSE_CURSOR(v_cursor); -- OutCount := v_count; -- return( OutCount ); END; You can only call this function from a PL/SQL statement, not from a select [Quoted] statement, so to get the count of the number of rows in all the tables held [Quoted] by a schema you will need to do the following :- declare cursor c_get_tabs IS select table_name from user_tables; begin for tabs in c_get_tabs loop dbms_output.put_line(tabs.table_name || ' has ' || tcount(tabs.table_name) || ' rows.'); end loop; end; / (don't forget to set serveroutput on in SQL). Voila it all works fine. <pberetta_at_my-deja.com> wrote in message news:85la7n$f32$1_at_nnrp1.deja.com...Received on Tue Jan 18 2000 - 13:41:01 CET
> 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> 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.