Sorry for wrong thread title, this should be:
[Oracle10g - PL/SQL] How to call a Function inside of a Function
I've already Googled this for 2 days but I can't find any good answer.
I've created a Function named USERS_GROUP_GET(P_USER_ID) that returns ref_cur. ref_cur is a REF CURSOR. The records that this function returns contains GROUP_ID(NUMBER), GROUP_NAME(VARCHAR2), and GROUP_MODULES(VARCHAR2).
I need to call the function USERS_GROUP_GET inside another function and retrieve the GROUP_ID or GROUP_MODULES for each row and do some processing.
I need to LOOP through the records but I can't fetch anything.
Already used different methods in retrieving data:
Test#1
v_request_cur%ROWCOUNT is 0Code:... v_request_cur cur_type; BEGIN v_request_cur := TWN_ICARE_PKG.USER_GROUP_GET('P_USER_ID' => P_USER_ID); ... END;
v_request_cur%FOUND is False;
Test#2
v_request_cur%ROWCOUNT is 0Code:... CURSOR v_request_cur IS SELECT TWN_ICARE_PKG.USER_GROUP_GET(P_USER_ID) FROM DUAL; BEGIN OPEN v_request_cur; ... END;
v_request_cur%FOUND is False;
Test#3
v_request_cur%ROWCOUNT is 0Code:... v_request_cur cur_type; BEGIN SELECT TWN_ICARE_PKG.USER_GROUP_GET(P_USER_ID) INTO v_request_cur; FROM DUAL; ... END;
v_request_cur%FOUND is False;
Test#4
Returns an Error Message: PLS-00382: expression is of wrong typeCode:... v_request_cur cur_type; BEGIN OPEN v_request_cur FOR TWN_ICARE_PKG.USER_GROUP_GET(P_USER_ID); ... END;
This is very weird. Logically, these methods must work but it fails me.
And when I tried to return v_request_cur from Test#1-3, my function returns records but if I tried to evaluate the Ref Cursor variable, it shows no records:
Code:... v_reccount := v_request_cur%ROWCOUNT; --ROWCOUNT = 0 RETURN v_request_cur; --Returns Records END;
Please help me with this weird problem.




Reply With Quote