A friendly place for programming greenhorns!
Big Moose Saloon
Register / Login
Win a copy of
Node.js in Action
this week in the
How to Pass Cursor Row as a parameter to another Function?
Joined: Jun 29, 2011
Jul 23, 2012 09:18:31
I Have Cursor
CURSOR ABC IS Select * from XYZ; BEGIN FOR REC IN ABC LOOP FUNCTION(); END LOOP;
Now I need to pass each row from cursor ABC as a parameter to FUNCTION().
Can some one tell me how to do that?
Joined: May 22, 2002
Jul 23, 2012 11:42:43
I'm not sure if you want to call the function for each row in the select or if you want pass the cursor to the function.
For the first case include the function in the select
select col1,col2,functioncall(col1) from table;
For the second
CREATE OR REPLACE FUNCTION schemaname.con_list (p_cursor IN SYS_REFCURSOR) RETURN VARCHAR2 IS l_return VARCHAR2(32767); l_temp VARCHAR2(32767); BEGIN LOOP FETCH p_cursor INTO l_temp; EXIT WHEN p_cursor%NOTFOUND; l_return := l_return || ',' || l_temp; END LOOP; close p_cursor; RETURN LTRIM(l_return, ','); END;
Then call it like:
SELECT ICEO.concatenate_list ( CURSOR (SELECT col10 FROM schemaname.tablename)) AS newlist FROM DUAL;
Hope this helps.
Joined: Jun 29, 2011
Jul 23, 2012 12:05:12
I guess you dint get my question correctly, Let me explain more detail..
From above example I have Cursor ABC.
Instead of doing
FUNCTION(REC.Col1, REC.Col2, REC.Col3)
I wanna send each record completely, what is the correct procedure of doing below
Joined: Aug 22, 2010
Jul 23, 2012 12:17:59
Welcome to the Ranch, Sandeep!
Please re-read Gene's answer. Does it pass individual columns from the cursor to the function or not?
Alternatively, you might perhaps use the
I agree. Here's the link:
subject: How to Pass Cursor Row as a parameter to another Function?
java.sql.SQLException: [Microsoft][ODBC Driver Manager] Invalid cursor state
JDBC & oracle function
How I can pass Cursor as In parameter
Error in jdbc while executing procedure
All times are in JavaRanch time: GMT-6 in summer, GMT-7 in winter
| Powered by
Copyright © 1998-2014