Re: Question on DBMS_SQL package

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);

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