Re: Question on DBMS_SQL package

Bob,
  Sure does! Thank you, I was just about to give up on this idea, your code does exactly what I wanted to do. Oracle support never suggested alternate methods of achieving this result (other than upgrade to 8i). Thanks again,
Paul

In article <RcZg4.47$7R.650_at_news.colt.net>,   "Bob Bain" <bob.bain_at_terra-nova.e-mail.com> wrote:
> Paul,
>
> First off you will need to rewrite your function so that it looks
something
> like this :-
>
> 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
> statement, so to get the count of the number of rows in all the
tables held
> 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.
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Jan 19 2000 - 01:37:07 CET