Results 1 to 4 of 4

Thread: [Oracle11g - PL/SQL] How to call a Function inside of a Function

Threaded View

  1. #1

    Thread Starter
    Fanatic Member eSPiYa's Avatar
    Join Date
    Jun 2006
    Location
    in our house
    Posts
    751

    Exclamation [Oracle11g - PL/SQL] How to call a Function inside of a Function

    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
    Code:
    ...
    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%ROWCOUNT is 0
    v_request_cur%FOUND is False;


    Test#2
    Code:
    ...
    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%ROWCOUNT is 0
    v_request_cur%FOUND is False;


    Test#3
    Code:
    ...
    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%ROWCOUNT is 0
    v_request_cur%FOUND is False;


    Test#4
    Code:
    ...
    v_request_cur cur_type;
    BEGIN
    OPEN v_request_cur FOR TWN_ICARE_PKG.USER_GROUP_GET(P_USER_ID);
    ...
    END;
    Returns an Error Message: PLS-00382: expression is of wrong type


    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.
    Last edited by eSPiYa; Nov 8th, 2008 at 03:16 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width