| Ref Cursor [message #252986] |
Sat, 21 July 2007 02:55  |
|
|
Hi...
Attached are 2 screen shots of the problem i am facing.
Mudabbir
-
Attachment: 1.jpg
(Size: 105.47KB, Downloaded 1458 times)
|
|
|
|
|
|
| Re: Ref Cursor [message #252994 is a reply to message #252987] |
Sat, 21 July 2007 04:11  |
bbaz
Messages: 138
Registered: April 2007
|
Senior Member |
|
|
Hi there,
Your issue is not with the CURSOR it is with your SELECT statement. Below is sample procedure that works fine for a column variable and NOT for a table var:
CREATE OR REPLACE PROCEDURE cursor_test
IS
test_cur sys_refcursor;
tmp_table varchar2(100);
v_column_name varchar2(300) := 'dummy';
v_table_name varchar2(200) := 'DUAL';
v_value varchar2(32);
BEGIN
OPEN test_cur FOR SELECT v_column_name FROM DUAL;
LOOP
FETCH test_cur INTO v_value;
EXIT when test_cur%notfound;
DBMS_OUTPUT.PUT_LINE('this is a TEST: '||v_value);
END LOOP;
CLOSE test_cur;
END;
Hope this might help.
Baz
|
|
|
|
|
|
| Re: Ref Cursor [message #252999 is a reply to message #252995] |
Sat, 21 July 2007 06:53 |
bbaz
Messages: 138
Registered: April 2007
|
Senior Member |
|
|
Try the following:
v_table_name varchar2(200) := 'EMP';
OPEN test_cur FOR SELECT v_column_name FROM USER_OBJECTS WHERE OBJECT_NAME=v_table_name;
This should work, but you need to make sure that the TABLE already exists. This is Dynamic.
Let us know if it works.
Baz
|
|
|
|
| Re: Ref Cursor [message #253013 is a reply to message #252999] |
Sat, 21 July 2007 08:31 |
|
|
OPEN test_cur FOR SELECT v_column_name FROM USER_OBJECTS WHERE OBJECT_NAME=v_table_name;
this wont work as the column i am passing in "V_Column_name" is not a column in user_objects but it is a column in the table i am passing.
Let me explain what exactly i need...
i need to select all the columns of a table into a cursor. the table name should be dynamic...
I have done this already using EXEC_SQL
but the problem is its creating a new session to the same schema.
Mudabbir
|
|
|
|
| Re: Ref Cursor [message #253032 is a reply to message #253013] |
Sat, 21 July 2007 12:07 |
bbaz
Messages: 138
Registered: April 2007
|
Senior Member |
|
|
I tried this and it works fine, I think this is what you are trying to do. Please confirm.
CREATE OR REPLACE PROCEDURE cursor_test_1 (colname IN varchar2, tab_name IN varchar2)
IS
test_cur sys_refcursor;
v_value varchar2(32);
sql_stmt VARCHAR2(1000);
BEGIN
sql_stmt := 'SELECT '||colname||' FROM '||UPPER(tab_name);
OPEN test_cur FOR sql_stmt;
LOOP
FETCH test_cur INTO v_value;
EXIT when test_cur%notfound;
DBMS_OUTPUT.PUT_LINE(v_value);
END LOOP;
CLOSE test_cur;
END;
Good luck,
Baz
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
