5 Replies Latest reply: Aug 10, 2012 5:29 AM by EJP RSS

    Error report:No more data to read from socket  when compile package

    954818

      We are going to migrate db from oracle 10g to oracle 11g. But when compile a package which is fine on oracle 10g, an exception "Error report:No more data to read from socket" throws on sql developer and break the connection. We have researched the issue and found if modify some code on a cursor, the issue will not happen.
      code:
      PROCEDURE DELETE_MANU_INTERV_BORROWS(
      p_borrow_id IN NUMBER,
      p_asset_id IN NUMBER,
      p_nsb_coll_type IN VARCHAR2,
      p_nsb_coll_code IN VARCHAR2,
      p_branch_code IN VARCHAR2,
      p_settle_date IN NUMBER,
      p_trans_type IN VARCHAR2,
      p_status OUT VARCHAR2,
      p_shorts_cursor OUT SYS_REFCURSOR
      )
      IS
      var_end_settle_date GEC_IM_ORDER.SETTLE_DATE%type;


      CURSOR exist_allocation IS
      SELECT out_a.ALLOCATION_ID FROM
      GEC_ALLOCATION out_a,
      GEC_BORROW b,
      GEC_IM_ORDER o,
      GEC_TRADE_COUNTRY gc,
      (SELECT f.FUND_CD, f.BRANCH_CD, tc.TRADE_COUNTRY_CD, NVL(gc.COLLATERAL_CURRENCY_CD, gbk.COLLATERAL_CURRENCY_CD) as COLLATERAL_CURRENCY_CD
      FROM GEC_FUND f
      LEFT JOIN GEC_G1_BOOKING gbk ON f.fund_cd = gbk.fund_cd AND gbk.TRANSACTION_CD = 'G1L' AND gbk.POS_TYPE = 'NSB'
      LEFT JOIN GEC_G1_COLLATERAL gc ON gc.G1_BOOKING_ID = gbk.G1_BOOKING_ID
      LEFT JOIN GEC_TRADE_COUNTRY tc ON gc.TRADE_COUNTRY_CD = tc.TRADE_COUNTRY_CD
      ) loan_info
      WHERE b.BORROW_ID = out_a.BORROW_ID AND
      out_a.IM_ORDER_ID = o.IM_ORDER_ID AND
      o.FUND_CD = loan_info.FUND_CD AND
      o.ASSET_ID = p_asset_id AND
      gc.TRADE_COUNTRY_CD = o.TRADE_COUNTRY_CD AND
      o.TRANSACTION_CD = p_trans_type AND
      loan_info.COLLATERAL_CURRENCY_CD = p_nsb_coll_code AND
      loan_info.BRANCH_CD = p_branch_code AND
      o.SETTLE_DATE >= p_settle_date AND
      o.SETTLE_DATE <= var_end_settle_date AND
      b.STATUS = GEC_CONSTANTS_PKG.C_BORROW_MANUAL
      ORDER BY out_a.ALLOCATION_ID ASC
      FOR UPDATE OF out_a.ALLOCATION_ID;

      If delete the "OF out_a.ALLOCATION_ID" of the for update clause of CURSOR exist_allocation, this prolbem will not happen, and the code is comple succesfully on sql developer for oracle 10g.
      Does anybody have any idea? I've googled the similer problems but still can't find a clue. I'll appreciate any help.

        • 1. Re: Error report:No more data to read from socket  when compile package
          rp0428
          Welcome to the forum!

          Please provide the 4 digit Oracle version (result of SELECT * FROM V$VERSION) for the source and target servers; 10g and 11g are not versions. You also mention sql developer so what is the exact version you are using?
          >
          If delete the "OF out_a.ALLOCATION_ID" of the for update clause of CURSOR exist_allocation, this prolbem will not happen, and the code is comple succesfully on sql developer for oracle 10g.
          >
          Please clarify what works and what doesn't work because your statements are both incorrect and misleading.

          You can't delete the "OF out_a.ALLOCATION_ID" of the for update clause or you would get a syntax error by leaving FOR UPDATE OF with nothing specified after it.

          Also you original statement said
          >
          But when compile a package which is fine on oracle 10g
          >
          But now you say that if you delete the "OF..." the problem doesn't happen and the code compiles on 10g.

          Does the original code compile on 10g or not? Does it compile on 11g or not? After the original code is migrated to 11g does it compile? That is, the code is there can you manually compile it?
          • 2. Re: Error report:No more data to read from socket  when compile package
            954818
            Thank you for the reply. Here is the version info
            oracle 11g: 11.2.0.1.0
            oracle 10g: 10.2.0.1.0
            sql developer: 1.5.5.59.69/ 3.0.04.34

            We tried on sql developer of different version and SQL plus also have this issue. But if we delete the "of out_a.ALLOCATION_ID " to make the "select for update of ..." to "select for update", I mean delete all the statements after "FOR UPDATE" rather than "FOR UPDATE OF with nothing specified after it" and this package can compile succesfully! But I don't think the original code have any syntax error as original code can compile on 10g.After that I also try to keep the "select for update of" and delete some other code, and I found modify some other code in this procedure can also lead to compile succefully.I don't think code is the root cause.But the strange thing is other procedures except DELETE_MANU_INTERV_BORROWS proc will not cause this problem in this package.
            • 3. Re: Error report:No more data to read from socket  when compile package
              rp0428
              You didn't answer the other questions
              >
              Does the original code compile on 10g or not? Does it compile on 11g or not? After the original code is migrated to 11g does it compile? That is, if the code is there can you manually compile it?
              >
              Can you compile the original code on 10g manually using sql*plus or not?

              Can you compile the original code on 11g manually using sql*plus or not?
              • 4. Re: Error report:No more data to read from socket  when compile package
                954818
                We can compile the original code on 10g manually using sql*plus. I think this issue may related to some bug on oracle.
                • 5. Re: Error report:No more data to read from socket  when compile package
                  EJP
                  Not a JDBC question. Locking.