1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Oracle Procedure Warning: compiled but with compilation errors

Discussion in 'SQL PL/SQL' started by neullson, Mar 18, 2011.

  1. neullson

    neullson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Here is my code guys and I need your help...

    Code (Text):

    CREATE OR REPLACE PROCEDURE EIMADMIN1.DAILY_CALCULATE_CONSIGNMENT IS
       
        vROW_ID                 VARCHAR2(100 Char);
        vCONS_ID                VARCHAR2(100 Char);
        vEMPLOYEE_ID            VARCHAR2(100 Char);
        vTOTAL_CONS             NUMBER(10);
        vTOTAL_CREDIT           NUMBER(10);
        vOUTSTANDING_CASH       NUMBER(10);
        vSTATUS                 VARCHAR2(30 Char);
        vCONS_STATUS            VARCHAR2(30 Char);
        vTRANS_STATUS           VARCHAR2(30 Char);
        vTOTAL_SALES            NUMBER(10);
       
    CURSOR CONS_REKAP IS

        SELECT  A.ROW_ID,
                A.CONS_ID,
                A.EMPLOYEE_ID,
                A.TOTAL_CONS,
                E.TOTAL_CREDIT,
                A.TOTAL_CONS - E.TOTAL_CREDIT AS OUTSTANDING_CASH,
                A.STATUS,
                A.CONS_STATUS
        FROM    SIEBEL.CX_CONS_HEADER A,
                (SELECT CONS_ID, SUM(CREDIT_AMOUNT) AS TOTAL_CREDIT
                    FROM SIEBEL.CX_CMS_PAY_HEAD
                    WHERE STATUS_PAYMENT = 'Complete'
                    GROUP BY CONS_ID) E
        WHERE   CONS_STATUS <> 'Closed' AND
                A.CONS_ID = E.CONS_ID;
       
    BEGIN

    OPEN CONS_REKAP;

    LOOP
        FETCH CONS_REKAP
        INTO    vROW_ID, vCONS_ID, vEMPLOYEE_ID, vTOTAL_CONS, vTOTAL_CREDIT, vOUTSTANDING_CASH, vSTATUS, vCONS_STATUS;
        EXIT WHEN CONS_REKAP%NOTFOUND;
       
        SELECT S.LGCL_SRC_STATUS_CD INTO vTRANS_STATUS
            FROM (SELECT LGCL_SRC_STATUS_CD
                        FROM SIEBEL.S_INV_TXN
                        WHERE SIGNATURE_ID = vCONS_ID
                        ORDER BY TXN_DT DESC) S
            WHERE ROWNUM = 1;
       
        UPDATE SIEBEL.CX_CONS_HEADER
            SET CONS_STATUS =  CASE
                                    WHEN vTRANS_STATUS = 'Closed Reconciliation' THEN 'Return to Logistic'
                                    WHEN vTRANS_STATUS = 'Closed Consignment' THEN 'Closed'
                                    ELSE vCONS_STATUS
                                END
            WHERE ROW_ID = vROW_ID AND CONS_ID = vCONS_ID;
        COMMIT;
           
        UPDATE SIEBEL.CX_CONS_HEADER
            SET STATUS =    CASE
                                WHEN vOUTSTANDING_CASH > 0 THEN 'Partially Paid'
                                WHEN vOUTSTANDING_CASH <= 0 AND vTOTAL_CREDIT > 0 THEN 'Paid'
                                ELSE vSTATUS
                            END
            WHERE ROW_ID = vROW_ID AND CONS_ID = vCONS_ID;
        COMMIT;
           
        SELECT  CONSIGNMENT_ID, SUM(PRICE) INTO vTOTAL_SALES
            FROM    SIEBEL.CX_CMS2_ORDER
            WHERE   CONSIGNMENT_ID = vCONS_ID AND
                    STATUS = 'Complete'
            GROUP BY CONSIGNMENT_ID;
           
        UPDATE SIEBEL.CX_CONS_HEADER
            SET TOTAL_CONS = vTOTAL_SALES
            WHERE ROW_ID = vROW_ID AND CONS_ID = vCONS_ID;
        COMMIT;
           
    END LOOP;
    CLOSE CONS_REKAP;

    EXCEPTION
         WHEN NO_DATA_FOUND THEN
           NULL;
           
    END DAILY_CALCULATE_CONSIGNMENT;
     
    And the output is 'Warning: compiled but with compilation errors'. TOAD doesn't give me exact errors or even the error line.

    Please check my code and give me solution if there is. Thank you very much. ;)
     
  2. kiran.marla

    kiran.marla Forum Genius

    Messages:
    403
    Likes Received:
    52
    Trophy Points:
    505
    Location:
    Khammam
    Hi neullson,
    Try run the code in SQL *Plus environment.
    If u get errors ,
    Try "Show err" command, will help you where the exact error is ..
     
  3. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Toad also will give you Error code and error messge

    1. Load the procedure in the PL/SQL tab or editor
    2. Press F9 or right click mouse and click Execute --> Compiler All
    3. It will display the error code and messages in output window.
     
  4. Mr.777

    Mr.777 Active Member

    Messages:
    15
    Likes Received:
    1
    Trophy Points:
    90
    In the whole procedure what i've observed is, you should assign values to the variables you've declared above as;
    Code (SQL):
    SELECT a.r,a.t,b.y,b.u
    INTO rowId, countId,showstop
    FROM abc a, def b, ghi c
    WHERE a.id=b.id
    AND b.ROW=c.ROW
    I've just given you the sample... So, you need to assign the values to the variables you've been delcared... Hope it makes sense...
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    For me,
    It seems the following query could be ONE OF THE PROBLEMS.


    Code (SQL):
        SELECT  CONSIGNMENT_ID, SUM(PRICE) INTO vTOTAL_SALES
            FROM    SIEBEL.CX_CMS2_ORDER
            WHERE   CONSIGNMENT_ID = vCONS_ID AND
                    STATUS = 'Complete'
            GROUP BY CONSIGNMENT_ID;
    There are two columns selected in select clause but assigned only one.

    Change it into


    Code (SQL):
        SELECT  SUM(PRICE) INTO vTOTAL_SALES
            FROM    SIEBEL.CX_CMS2_ORDER
            WHERE   CONSIGNMENT_ID = vCONS_ID AND
                    STATUS = 'Complete'
            GROUP BY CONSIGNMENT_ID;
     
    neullson likes this.
  6. neullson

    neullson Active Member

    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    55
    Hi guys, thank's for the response. And for Rajavu, yeah you are right. I select 2 columns but only provide 1 variable.

    I've changed it to :
    Code (Text):

    SELECT SUM(PRICE) INTO vTOTAL_SALES
            FROM    SIEBEL.CX_CMS2_ORDER
            WHERE   CONSIGNMENT_ID = vCONS_ID AND
                    STATUS = 'Complete'
            GROUP BY CONSIGNMENT_ID;
     
    I've solved that, thank you very much guys... :)