2 Replies Latest reply on Sep 14, 2013 4:24 PM by Ishan

    Whenever run this query oracle connection got disconnected?

    PraVeen_KLDY

         SELECT ROWNUM AS SNO,GRNNO,GRNDATE,SUPPLIERINVNO,

                SUPPLIERINVDATE,GRNVALUE,REMARKS,PAYMENTSTATUS

         FROM

         (

         SELECT GRN.GRNNO,GRN.GRNDATE,GRN.SUPPLIERINVNO,GRN.SUPPLIERINVDATE,

                SUM(GRN.GRNVALUE) AS GRNVALUE,GRN.REMARKS,

               (CASE WHEN (( NVL(SUM(GRN.GRNVALUE),0) - NVL(SUM(PI.GRNWCRVALUE),0))= 0) THEN 'Paid' ELSE 'Pending' END) AS PAYMENTSTATUS

         FROM(

          SELECT GRNH.GOODSRECEIPTNO AS GRNNO,GRNH.GOODSRECEIPTDATE AS GRNDATE,

                 grnh.supplierinvoiceno as supplierinvno,grnh.supplierinvoicedate as supplierinvdate,

                 SUM(GRNH.GOODSRECEIPTAMOUNT) AS GRNVALUE,

                 GRNH.REMARKS

          FROM   GOODSRECEIPTNOTEHEADER GRNH

          WHERE  GRNH.PLANTCODE = '01'

          AND    GRNH.SUPPLIERCODE ='100001'

      --    AND    (S_VENDORCODE IS NULL OR GRNH.SUPPLIERCODE = S_VENDORCODE)

      --    AND    (S_INVOICENUMBER IS NULL OR UPPER(GRNH.SUPPLIERINVOICENO) LIKE '%'||UPPER(P_INVOICENUMBER)||'%')

      --    AND    (P_FROMDATE IS NULL OR GRNH.GOODSRECEIPTDATE >= P_FROMDATE)

      --    AND    (P_TODATE IS NULL OR GRNH.GOODSRECEIPTDATE <= P_TODATE)

          ---CHECK THE PO TERMS DETAILS

          AND GRNH.GOODSRECEIPTNO NOT IN

          (

                  SELECT I.GRNNO

                  FROM (

                    SELECT GRNNO,GRNDATE, (TO_DATE(GRNDATE)+TERMSDAY) AS TERMEXPDAY from

                    (

                    SELECT T.GRNNO,T.GRNDATE,NVL(SUM(TO_NUMBER(TM.TERMDESCRIPTION)),0) AS TERMSDAY FROM

                    (

                     SELECT TERM.GRNNO,TERM.GRNDATE,REGEXP_SUBSTR (TERM.PAYMENTTERMSID, '[^,]+', 1, level) TERMSID

                     FROM

                     (

                     SELECT GRNH.GOODSRECEIPTNO AS GRNNO,GRNH.GOODSRECEIPTDATE AS GRNDATE,

                            POH.PAYMENTTERMSID

                      FROM   GOODSRECEIPTNOTEHEADER GRNH

                      LEFT OUTER JOIN PURCHASEORDERHEADER POH ON POH.PURCHASEORDERNO =  GRNH.PURCHASEORDERNO

                       WHERE  GRNH.PLANTCODE = '01'

          AND    GRNH.SUPPLIERCODE ='100001'

      --                AND    (S_VENDORCODE IS NULL OR GRNH.SUPPLIERCODE = S_VENDORCODE)

      --                AND    (S_INVOICENUMBER IS NULL OR UPPER(GRNH.SUPPLIERINVOICENO) LIKE '%'||UPPER(P_INVOICENUMBER)||'%')

      --                AND    (P_FROMDATE IS NULL OR GRNH.GOODSRECEIPTDATE >= P_FROMDATE)

      --                AND    (P_TODATE IS NULL OR GRNH.GOODSRECEIPTDATE <= P_TODATE)

                      GROUP BY GRNH.GOODSRECEIPTNO,GRNH.GOODSRECEIPTDATE

                             ,POH.PAYMENTTERMSID

                   )TERM

                   connect by level <= length(regexp_replace(TERM.PAYMENTTERMSID,'[^,]*'))+1

                   GROUP BY TERM.GRNNO,TERM.GRNDATE,REGEXP_SUBSTR (TERM.PAYMENTTERMSID, '[^,]+', 1, level)

                   ORDER BY TERM.GRNNO

                   )T

                   INNER JOIN TERMSMASTER TM ON T.TERMSID = TM.TERMID

                   GROUP BY T.GRNNO,T.GRNDATE

                   )

                 )I WHERE TO_DATE(I.TERMEXPDAY) < TO_DATE(SYSDATE)

          )

          GROUP BY GRNH.GOODSRECEIPTNO,GRNH.GOODSRECEIPTDATE,

                 GRNH.SUPPLIERINVOICENO,GRNH.SUPPLIERINVOICEDATE,

                 GRNH.REMARKS

                

          )GRN

          LEFT OUTER JOIN PAIDINFO PI on PI.GRNWCRNO = GRN.GRNNO

          GROUP BY GRN.GRNNO,GRN.GRNDATE,GRN.SUPPLIERINVNO,GRN.SUPPLIERINVDATE,

                GRN.GRNVALUE,GRN.REMARKS

          );

        • 1. Re: Whenever run this query oracle connection got disconnected?
          John Spencer

          My guess would be that you are actually getting an ORA-00600 (or possibly a 7445) error which results in your session getting disconnected.  You would need to look in the alert log to see the actual Oracle error.  Once you have that, you can use the ORA 600 lookup tool on My Oracle Support to see if it is a know issue.

           

          Depending on your unspecified Oracle version, there are a number of bugs that can cause that sort of behaviour.  Some of them have workarounds some don't. It is possible that a re-written version of the query would avoid the buggy area and run successfully.

           

          John

          • 2. Re: Whenever run this query oracle connection got disconnected?
            Ishan

            As John mentioned,

             

            Try to re-write you query in some other way.

             

            This error can also come if you are using non-recommended way of using syntax which Oracle is not able to somehow catch and results in the error that you got.

             

            Thanks,

            Ishan