Home » SQL & PL/SQL » SQL & PL/SQL » nested cursor by table_name as parameter
nested cursor by table_name as parameter [message #248785] Mon, 02 July 2007 00:22 Go to next message
dephneHsu
Messages: 1
Registered: July 2007
Junior Member
Hello, I am trying to create a procedure as below:


CREATE OR REPLACE PROCEDURE script_out_tables_wColumn(p_column_name IN VARCHAR2)
IS
/**
all the tables which belongs to the owner and contains given column name
will be script out into insert statement
**/

TYPE varchar_collect_type IS TABLE OF VARCHAR2(255) INDEX BY PLS_INTEGER;

v_tab_names_collect varchar_collect_type;
v_insert_into_tab_name VARCHAR2(255) :='temp_table';

BEGIN
DBMS_OUTPUT.ENABLE(10000000);


FOR cur_table_names IN( SELECT table_name
FROM user_tab_columns
WHERE column_name = p_column_name
ORDER BY table_name)
LOOP
FOR cur_table IN( SELECT empno, deptno
FROM cur_table_names.table_name
ORDER BY empno)
LOOP
DBMS_OUTPUT.PUT_LINE(empno);
END LOOP;

END LOOP;
END script_out_tables_wColumn;


But it doesn't work. Would any one knows what the problem is?
Thank you very much.
Re: nested cursor by table_name as parameter [message #248787 is a reply to message #248785] Mon, 02 July 2007 00:26 Go to previous messageGo to next message
BlackSwan
Messages: 25037
Registered: January 2009
Location: SoCal
Senior Member
> doesn't work.
I do not recognize this as a valid Oracle error code.

My car dosen't work.
Please tell me how to make it better.
Re: nested cursor by table_name as parameter [message #248805 is a reply to message #248785] Mon, 02 July 2007 01:41 Go to previous message
sehgal.best
Messages: 7
Registered: February 2007
Junior Member
Hi
This code will not be compiled as plsql needs actual table names rather than cur_table_names.table_name type of syntax in the stored procedure. you can achieve similar type of functionality using dynamic sql. try exploring package dbms_sql.

Previous Topic: "Error" , "Block" merged silly topic titles
Next Topic: function
Goto Forum:
  


Current Time: Mon Dec 05 06:44:06 CST 2016

Total time taken to generate the page: 0.12486 seconds