Re: PL/SQL "table" question...

From: Detlev Goebel <detlev.goebel_at_gzs.de>
Date: 1997/05/23
Message-ID: <33853BEA.6230@gzs.de>#1/1

Ranganathan Chakravarthi wrote:
> =
 

> Vijay Damodaran (vijayd_at_nortel.com) wrote:
> :
> : When I try to execute this script, I get the following error:
> :
> : ORA-06550: line 6, column 10:
> : PLS-00382: expression is of wrong type
> : ORA-06550: line 7, column 50:
> : PLS-00487: Invalid reference to variable 'ADM'
> :
> =
 

> I tried the same thing and got the same error. I came to a conclusion
> that tables cannot be passed as parameters to functions/procedures.
> I might be wrong. Let me know if you find an answer.
> TIA,
> =
 

> -- Ranga
> -----------------------------------------------------------------------=

---

> If it doesn't work, don't worry; if it did, you are out of a jo=
 b!
> -----------------------------------------------------------------------=

---

> Ranga Chakravarthi e-mail: rang=
 a_at_one.net
> -----------------------------------------------------------------------=

---

Hi,

using table names as parameter to stored procedures you are in need of dynamic SQL.
Make sure that the package dbms_sql is installed. If so you can use it as followed:

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

• =

Detlev Goebel

mailto:detlev.goebel_at_gzs.de

---

Received on Fri May 23 1997 - 00:00:00 CDT