4 Replies Latest reply: Feb 16, 2013 1:47 PM by JustinCave RSS

    Irregular Data Loss - PL/SQL function returning data using ref cursor

    922306
      Database Version: 10.2.0.4.0 (2 Node RAC)

      The high level flow of process is as below:
      1) Insert records in few tables & commit the same
      2) Call pl/sql function to fetch records (on some conditions with joins with other tables) from tables which are populated in step1.
      -> The function is using ORDER BY clause for inline query & using row number 5000 records are returning for each call.
      Meaning - If inline query is suppose to return 1,00,000 records then 20 calls to same function. This is done because application can't hold records beyond certain number.
      3) The data returned from ref cursor is further processed by application (Tibco BW) to generate flat file.

      We are facing issue of data loss in the file & there is no fixed pattern. It happens once between 200-300 process calls.
      Resolution: When the issue occurs, re-trigger the process and in almost each time re-triggering of process provides required data.

      Any pointers on what could be the reason?

      ** Sample Code for function:
      CREATE OR REPLACE FUNCTION FUNC_GET_HRCH_TOTAL_DATA (
      outinstrid IN NUMBER,
      outinstrkey IN NUMBER,
      rownumberstart IN NUMBER,
      rownumbereend IN NUMBER,
      err_code OUT VARCHAR2,
      err_msg OUT VARCHAR2)
      RETURN PACK_TYPES.HRCH_TOTAL_CURSOR
      IS
      REF_HRCH_TOTAL_CURSOR PACK_TYPES.HRCH_TOTAL_CURSOR;
      BEGIN

      OPEN REF_HRCH_TOTAL_CURSOR FOR
      SELECT *
      FROM ( SELECT A.HIERARCHY_KEY, B.KEY, B.VAL_KEY, A.KEY_NEW, C.ITEMID, B.VAL_TAG, B.sort_order,ROWNUM ROWNUMBER
      FROM AOD_HRCH_ITEM A, AOD_HRCH_ATTR B, AOD_HRCH_ITEMS C
      WHERE A.outputid = B.outputid
      AND A.outputid = C.outputid AND A.outputkey = B.outputkey
      AND A.outputkey = C.outputkey AND A.outputid = outinstrid
      AND A.outputkey = outinstrkey AND A.ITEM_SEQ = B.ITEM_SEQ
      AND A.ITEM_SEQ = C.ITEM_SEQ AND A.HIERARCHY_LEVEL_ORDER = B.SORT_ORDER
      ORDER BY A.HIERARCHY_LEVEL_ORDER DESC)
      WHERE ROWNUMBER < rownumbereend
      AND ROWNUMBER >= rownumberstart;


      RETURN REF_HRCH_TOTAL_CURSOR;
      EXCEPTION
      WHEN OTHERS
      THEN
      err_code := x_progress || ' - ' || SQLCODE;
      err_msg := SUBSTR (SQLERRM, 1, 500);

      END FUNC_GET_HRCH_TOTAL_DATA;
      /

      Edited by: meet_sanc on Feb 16, 2013 10:42 AM
        • 1. Re: Irregular Data Loss - PL/SQL function returning data using ref cursor
          sb92075
          remove, delete, eliminate whole, complete & all EXCEPTION handler code.

          I bet error occurs, but you never see it!
          • 2. Re: Irregular Data Loss - PL/SQL function returning data using ref cursor
            922306
            During execution (multiple calls to function), if SQL error occurs then we terminate the whole process.

            We didn't get any ORA exception, but as said sometimes there is data loss!
            • 3. Re: Irregular Data Loss - PL/SQL function returning data using ref cursor
              sb92075
              meet_sanc wrote:
              During execution (multiple calls to function), if SQL error occurs then we terminate the whole process.

              We didn't get any ORA exception,
              you do not SEE any error

              If you actually do not get any Oracle exception, then it won't cause any problem to remove EXCEPTION handler!

              remove, delete, eliminate whole, complete & all EXCEPTION handler code.

              I bet error occurs, but you never see it!
              • 4. Re: Irregular Data Loss - PL/SQL function returning data using ref cursor
                JustinCave
                Your SELECT statement is almost certainly incorrect
                SELECT *
                  FROM ( SELECT A.HIERARCHY_KEY, B.KEY, B.VAL_KEY, A.KEY_NEW, C.ITEMID, B.VAL_TAG, B.sort_order,ROWNUM ROWNUMBER
                           FROM AOD_HRCH_ITEM A, AOD_HRCH_ATTR B, AOD_HRCH_ITEMS C
                          WHERE A.outputid = B.outputid
                            AND A.outputid = C.outputid AND A.outputkey = B.outputkey
                            AND A.outputkey = C.outputkey AND A.outputid = outinstrid
                            AND A.outputkey = outinstrkey AND A.ITEM_SEQ = B.ITEM_SEQ
                            AND A.ITEM_SEQ = C.ITEM_SEQ AND A.HIERARCHY_LEVEL_ORDER = B.SORT_ORDER
                          ORDER BY A.HIERARCHY_LEVEL_ORDER DESC)
                 WHERE ROWNUMBER < rownumbereend 
                   AND ROWNUMBER >= rownumberstart;
                Since the ORDER BY is applied after the ROWNUM is assigned in this case, your query is asking for an arbitrary 5000 rows each time. It would be entirely valid for one row to be returned in each of your 200 different calls or for one row to be returned in none of them.

                You almost certainly want to do something along the lines of the canonical askTom thread
                select * 
                  from ( select a.*, rownum rnum
                           from ( YOUR_QUERY_GOES_HERE -- including the order by ) a
                          where rownum <= MAX_ROWS )
                 where rnum >= MIN_ROWS
                That said, it strikes me as inconceivable that Tibco cannot handle a cursor that returns more than a certain number of rows. You're doing a ton of work to return separate pages of data that is almost certainly unnecessary. Unless you're saying that you have somehow crippled your Tibco install by giving it a ridiculously small amount of memory to deal with, something doesn't sound right. A cursor is just a pointer-- it holds no data-- so the number of rows that you can fetch from a cursor should have no impact on how much memory the client application needs.

                As others have pointed out, your exception handler is almost certainly doing more harm than good. Returning error codes and error messages as OUT parameters rather than simply allowing the exception to propagate removes a ton of useful information (like the error stack) and makes your process much less robust.

                Justin