Dynamic PL/SQL: counting rows function

From: Jochen Liekens <Jochen.Liekens_at_VlaamsParlement.be>
Date: Tue, 28 Jul 1998 18:17:27 +0200
Message-ID: <35BDF996.8E9082E7_at_VlaamsParlement.be>

Hi,

[Quoted] 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 );
[Quoted]     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; Received on Tue Jul 28 1998 - 18:17:27 CEST