| Dynamic SQL and Cursor [message #38207] |
Mon, 01 April 2002 10:48  |
Mahesh
Messages: 90
Registered: January 2001
|
Member |
|
|
I am facing a problem to use the combination of dynamic sql and cursor. I have different tables with different structure. In order to use the REF CURSOR, i am facing problem to declare the recrod variable. When i try to declare record variable by using cursor_name%ROWTYPE in REF cursor, it gives error message. I will not know the record type in advance as i am trying to write a program where user can specify the table and columns at run time. Is there any way out?
Thanks for you time and help.
Mahesh
|
|
|
|
| Re: Dynamic SQL and Cursor [message #38221 is a reply to message #38207] |
Wed, 03 April 2002 03:06  |
tinel
Messages: 42
Registered: November 2001
|
Member |
|
|
Hi!
Here is a sample that may help you.
CREATE OR REPLACE PROCEDURE Proc_Mahesh(
p_table IN VARCHAR2,
p_column1 IN VARCHAR2,
p_column2 IN VARCHAR2,
p_column3 IN VARCHAR2,
p_condition IN VARCHAR2
)
IS
TYPE Ref_Cursor IS REF CURSOR;
c1 ref_cursor;
v_val1 NUMBER(7,2);
v_val2 NUMBER(7,2);
BEGIN
OPEN c1 FOR 'SELECT ' || p_column1 || ', '
|| p_column2 || ' FROM ' || p_table || ' WHERE ' || p_column3 || '=''' || p_condition || '''';
LOOP
FETCH c1 INTO v_val1, v_val2;
EXIT WHEN c1%notfound;
-- some plsql statement
END LOOP;
CLOSE c1;
END;
Bye
|
|
|
|
| Re: Dynamic SQL and Cursor [message #38233 is a reply to message #38221] |
Wed, 03 April 2002 09:54 |
Mahesh
Messages: 90
Registered: January 2001
|
Member |
|
|
Thanks for the help.
In the example you have given in your response, v_val1 and v_val2 are declared of type number(7,2). But in my case depending on the field provided by the user, the datatype may be different. In this case i have to declare these two vaiables dynamically. When i use normal cursor, i can use rowtype or type. Is there anyway to solve this problem?
Thanks a lot for your time and help.
Mahesh
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
