Home » Developer & Programmer » Forms » Ref Cursor
Ref Cursor [message #252986] Sat, 21 July 2007 02:55 Go to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

Hi...

Attached are 2 screen shots of the problem i am facing.

Mudabbir./fa/2748/0/
  • Attachment: 1.jpg
    (Size: 105.47KB, Downloaded 391 times)
Re: Ref Cursor [message #252987 is a reply to message #252986] Sat, 21 July 2007 02:58 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

I am using oracle 10g and forms 6i

Mudabbir./fa/2749/0/
  • Attachment: 2.jpg
    (Size: 91.73KB, Downloaded 365 times)
Re: Ref Cursor [message #252994 is a reply to message #252987] Sat, 21 July 2007 04:11 Go to previous messageGo to next message
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 #252995 is a reply to message #252986] Sat, 21 July 2007 04:24 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

Thanks Baz....

I need it to work for dynamic table names...

Any ideas?

Mudabbir
Re: Ref Cursor [message #252999 is a reply to message #252995] Sat, 21 July 2007 06:53 Go to previous messageGo to next message
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 Go to previous messageGo to next message
mudabbir
Messages: 235
Registered: April 2006
Location: Kuwait
Senior Member

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 Go to previous message
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
Previous Topic: Forms runtime color
Next Topic: How I can Disable System Alerts?
Goto Forum:
  


Current Time: Mon Dec 05 11:09:21 CST 2016

Total time taken to generate the page: 0.12995 seconds