Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: PL/SQL "table" question...
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
> -----------------------------------------------------------------------=
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;
set serveroutput on
execute count_rows ('emp');
Detlev Goebel
mailto:detlev.goebel_at_gzs.de