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>


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...

> 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.
Received on Tue Jan 18 2000 - 13:41:01 CET

Original text of this message