Re: Dynamic PL/SQL: counting rows function

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 28 Jul 1998 16:54:08 GMT
Message-ID: <35c0016b.10215859_at_192.86.155.100>

A copy of this was sent to Jochen Liekens <Jochen.Liekens_at_VlaamsParlement.be> (if that email address didn't require changing) On Tue, 28 Jul 1998 18:17:27 +0200, you wrote:

Here is an example that works:

create or replace function countem( p_tname in varchar2 ) return number is

    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   number default NULL;
    l_status        integer;

begin

    dbms_sql.parse( l_theCursor,

                    'select count(*) from ' || p_tname,
                     dbms_sql.native );

    dbms_sql.define_column( l_theCursor, 1, l_columnValue );

    l_status := dbms_sql.execute(l_theCursor);

    if ( dbms_sql.fetch_rows(l_theCursor) > 0 )

     then
        dbms_sql.column_value( l_theCursor, 1, l_columnValue );

    end if;

    dbms_sql.close_cursor(l_theCursor);

    return l_columnValue;
end countem;
/

I think its the call to:

> dbms_sql.define_column(exec_cursor,1,teller,10);

that is messing you up, not the one you identified. define_column is a very overloaded function. the calls with number types do not call for a length parameter (10) as you are passing, hence the define_column is calling the varchar2 overloaded function (the only one that takes 4 inputs). Later when you call column_value, its expecting you to send a varchar2, not a number, and err'ing out.

>Hi,
>
>I'm trying to write a function that returns the number of rows of a sql
>select statement but it doesn't seem to work.
>The sql-statement is passed on as 2 parameters: tabel (the table) and a
>where-clause.
>
>Can anybody help me out?
>
>TIA.
>
>Jochen.
>
>My code:
>
>function aantal_rec(tabel in varchar2, where_clause in varchar2) return
>number
>
>is
>
>teller number:=0;
>exec_cursor integer;
>rows_processed number default 0;
>sql_stmt varchar2(300);
>
> begin
>
> sql_stmt:='select count(*) from '||tabel||where_clause;
> exec_cursor:=dbms_sql.open_cursor;
> dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
> dbms_sql.define_column(exec_cursor,1,teller,10);
> rows_processed := dbms_sql.execute(exec_cursor);
> while dbms_sql.fetch_rows (exec_cursor) > 0 loop
> dbms_sql.column_value (exec_cursor, 1, teller);
> -- this previous line seems to give me problems
> end loop;
> -- I'm not sure if the loop is necessary
> dbms_sql.close_cursor( exec_cursor );
> return (teller);
> exception
> when others then
> if dbms_sql.is_open(exec_cursor) then
> dbms_sql.close_cursor(exec_cursor);
> end if;
> raise;
> end aantal_rec;
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Tue Jul 28 1998 - 18:54:08 CEST