0

I have a procedure that calls a function. After few procedure calls it is not executing that function anymore. Also there are no exceptions thrown. When I deleted the session_id from browser and refreshed it, everything starts to work as expected but not permanent.. After few procedure calls again it is starting to act weird again.. I red an article where was said something that when deleting the session id then all SYS_REFCURSORs also are deleted.
I put below modified code example how approximately everything looks, but could not show the real one because I'm not allowed to do that.

I would like to hear some opinions and possible reasons why this could happen.

Procedure that is called from DA:

PROCEDURE search_user(i_max_row_count IN INTEGER,
                                        i_session_id    IN NUMBER,
                                        i_user_id       IN some_common_logic.user_ids,
                                        cur_out         OUT SYS_REFCURSOR)
IS
  BEGIN        
    GetResult(i_max_row_count, i_session_id, cur_out);      
  END;

Procedure that calls the function:

PROCEDURE getresult(i_max_row_count IN INTEGER,
                    i_session_id    IN NUMBER,
                    cur_out         OUT SYS_REFCURSOR)
IS
  --some variables
  BEGIN
    OPEN cur_out FOR
    SELECT
      rownum,
      users.user_id AS "ID"
    FROM users
    WHERE users.user_id IN (SELECT object_id
                              FROM TABLE (get_user_id(i_max_row_count, i_session_id)));
  END;

Funciton that is not called:

FUNCTION Get_User_Id(i_max_row_count IN INTEGER,
                     i_session_id IN NUMBER)
  RETURN FilterRows pipelined
IS
   cur INTEGER := DBMS_SQL.OPEN_CURSOR;
BEGIN
   DBMS_OUTPUT.PUT_LINE('Get_User_Id is called!');
   --some logic here
   DBMS_SQL.CLOSE_CURSOR(cur);
   RETURN;
END;
MT0
  • 143,790
  • 11
  • 59
  • 117
Mr. Blond
  • 1,113
  • 2
  • 18
  • 41
  • Does this modified code encounter the same problem (presumably not as you don't pipe any rows), and can you get it calling it from somewhere other than your web app? Do you ever close your `cur_out` cursor? How do you know the problem is in the pipelined function? Can you return from that without closing that cursor? – Alex Poole Feb 17 '15 at 15:22
  • I basically renamed the function and variable names to show the main idea/structure and I've not tried without pipeline if that is what you meant. I've also tried to call this procedure from database itself and filling data by my own, and also that function wasn't called.. The cursor is closed inside that function only. I will test it without closing the cursor maybe. @AlexPoole – Mr. Blond Feb 17 '15 at 15:47
  • I removed that `DBMS_SQL.CLOSE_CURSOR(cur);` from **Get_User_Id** function and it works now! I will mark this as an answer if you provide a little information about cursors, Thank you! ;) @AlexPoole – Mr. Blond Feb 17 '15 at 15:57
  • You have two cursors though; inside Get_User_id, can you return without calling `DBMS_SQL.CLOSE_CURSOR(cur)`? I suspect not now you've added the return to the question. But does your caller ever close `cur_OUT`? I'd expect an error if you ran out of cursors but it might be squashed somewhere... I'm not sure why removing that close helped, it wasn't what i was driving at *8-) – Alex Poole Feb 17 '15 at 15:58
  • There is exception handler ` EXCEPTION WHEN OTHERS THEN DBMS_SQL.CLOSE_CURSOR (cur);` Is that means if the cursors run out of then that error is handled?@AlexPoole – Mr. Blond Feb 17 '15 at 16:01
  • Hard to be sure from a snippet, and depends where it is, but I think so - a `when others` catch that doesn't re-raise the error is usually a bug waiting to happen. Trying to close a cursor it couldn't open would throw a new exception, but perhaps GetResult then catches and squashes that. – Alex Poole Feb 17 '15 at 16:05

0 Answers0