Re: DBMS_SQL function call

From: Detlev Goebel <detlev.goebel_at_gzs.de>
Date: 1997/06/24
Message-ID: <33AF755A.5F9@gzs.de>#1/1

S=F8ren M. Olesen wrote:
> =
 

> Hi
> =
 

> How do I call a function and getting the return value using dbms_sql ??=
 

> =
 

> Thanks
> =
 

> Soren M. Olesen
> =
 

> N.B. I need to get it to work from forms

Oren,

This SQL Sstatement will process everything you want to have with dynamic SQL. Change that code for function.

CREATE OR REPLACE PROCEDURE count_rows (table_name IN VARCHAR2) IS   cur INTEGER;
  dummy INTEGER;
  rows NUMBER;
BEGIN
  cur :=3D dbms_sql.open_cursor;
  dbms_sql.parse(cur, 'select count(*) from '=A6=A6table_name,

         dbms_sql.v7);
  dbms_sql.define_column(cur, 1, rows);
  dummy :=3D dbms_sql.execute(cur);
  LOOP
    IF dbms_sql.fetch_rows(cur) > 0 THEN       dbms_sql.column_value(cur, 1, rows);     ELSE

      exit;
    END IF;
  END LOOP;

  dbms_sql.close_cursor(cur);
  dbms_output.put_line('Table: '=A6=A6table_name=A6=A6' Rows: '=A6=A6rows= );
EXCEPTION
  WHEN OTHERS THEN
    dbms_sql.close_cursor(cur);
END;
/

set serveroutput on
execute count_rows ('emp');
drop PROCEDURE count_rows;

good luck

Detlev

• =

Detlev Goebel

mailto:detlev.goebel_at_gzs.de

---

Received on Tue Jun 24 1997 - 00:00:00 CDT