Re: PL/SQL "table" question...
Date: 1997/05/23
Message-ID: <33853BEA.6230_at_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 CEST