passing table name as parameter in stored procedure
Can anyone of u help me on how to pass a table name as parameter to a stored procedure.I should use this inside the procedure to run a query.
eg:If i pass employee table as parameter(table_name) then i should be able to run a query like select * from table_name.
Right now its giving an error saying table doesnt exist.
Leave a comment...
- 2 Comments
- look up dynamic sql and execute immediate in the plsql manual as this should point you in the right direction.
Alan#1; Tue, 11 Dec 2007 13:55:00 GMT
- Dynamic SQL:
SQL> create or replace function select_any_table ( p_table_name in varchar2 )
2 return sys_refcursor
4 rc sys_refcursor;
6 open rc for 'select * from ' || p_table_name;
7 return rc;
SQL> var x refcursor
SQL> exec :x := select_any_table('DEPT')
PL/SQL procedure successfully completed.
SQL> print x
DEPTNO DNAME LOC
---- ----- ----
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON#2; Tue, 11 Dec 2007 13:56:00 GMT