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 ORA-06512 error

Discussion in 'SQL PL/SQL' started by mohammedalthaf, Jan 12, 2010.

  1. Code (SQL):
    CREATE OR REPLACE PROCEDURE SP_UPDATE_PROFILE (
       i_MOBILE_NUM                  IN     VARCHAR2,
       i_XFER_BARRED_DATE            IN     VARCHAR2,
       i_XFER_COUNT                  IN     NUMBER,
       i_XFER_ARPU_IGNORE_LOGIC      IN     NUMBER,
       i_XFER_DEFAULT_IGNORE_LOGIC   IN     NUMBER,
       i_XFER_BARRED_TYPE            IN     NUMBER,
       i_IVR_BARRED_DATE             IN     VARCHAR2,
       i_IVR_ACCESS_COUNT            IN     NUMBER,
       i_IVR_ARPU_IGNORE_LOGIC       IN     NUMBER,
       i_IVR_DEFAULT_IGNORE_LOGIC    IN     NUMBER,
       i_ARPU_CATEGORY               IN     VARCHAR2,
       i_CIRCLE_ID                   IN     VARCHAR2,
       i_XFER_MONTHLY_COUNT          IN     NUMBER,
       i_XFER_DATE                   IN     VARCHAR2,
       o_error_sts                      OUT VARCHAR2,
       o_error_reason                   OUT VARCHAR2
    )
    IS
       v_COUNT   INT;
       P_COUNT   PLS_INTEGER;
       v_TPIN    VARCHAR2 (10);
    BEGIN
       v_COUNT := 0;



       UPDATE   TBL_PRE_IVR_PROFILE
          SET   XFER_BARRED_DATE = TO_DATE (i_XFER_BARRED_DATE, 'DDMMYYYY'),
                XFER_COUNT = i_XFER_COUNT,
                XFER_ARPU_IGNORE_LOGIC = i_XFER_ARPU_IGNORE_LOGIC,
                XFER_DEFAULT_IGNORE_LOGIC = i_XFER_DEFAULT_IGNORE_LOGIC,
                XFER_BARRED_TYPE = i_XFER_BARRED_TYPE,
                IVR_BARRED_DATE = TO_DATE (i_IVR_BARRED_DATE, 'DDMMYYYY'),
                IVR_ACCESS_COUNT = i_IVR_ACCESS_COUNT,
                IVR_ARPU_IGNORE_LOGIC = i_IVR_ARPU_IGNORE_LOGIC,
                IVR_DEFAULT_IGNORE_LOGIC = i_IVR_DEFAULT_IGNORE_LOGIC,
                ARPU_CATEGORY = i_ARPU_CATEGORY,
                CIRCLE_ID = i_CIRCLE_ID,
                XFER_MONTHLY_COUNT = i_XFER_MONTHLY_COUNT,
                XFER_DATE = TO_DATE (i_XFER_DATE, 'DDMMYYYY')
        WHERE   MOBILE_NUM = i_MOBILE_NUM;

       v_COUNT := sql%ROWCOUNT;
       COMMIT;

       IF (v_COUNT = 1)
       THEN
          BEGIN
             --         DBMS_OUTPUT.PUT_LINE(v_COUNT1);
             o_error_sts := 'SU';
          --DBMS_OUTPUT.PUT_LINE(o_error_msg);
          END;
       ELSE
          IF (v_COUNT = 0)
          THEN
             BEGIN
                INSERT INTO TBL_PRE_IVR_PROFILE (MOBILE_NUM,
                                                 XFER_BARRED_DATE,
                                                 XFER_COUNT,
                                                 XFER_ARPU_IGNORE_LOGIC,
                                                 XFER_DEFAULT_IGNORE_LOGIC,
                                                 XFER_BARRED_TYPE,
                                                 IVR_BARRED_DATE,
                                                 IVR_ACCESS_COUNT,
                                                 IVR_ARPU_IGNORE_LOGIC,
                                                 IVR_DEFAULT_IGNORE_LOGIC,
                                                 ARPU_CATEGORY,
                                                 CIRCLE_ID,
                                                 XFER_MONTHLY_COUNT,
                                                 XFER_DATE)
                  VALUES   (i_MOBILE_NUM,
                            TO_DATE (i_XFER_BARRED_DATE, 'DDMMYYYY'),
                            i_XFER_COUNT,
                            i_XFER_ARPU_IGNORE_LOGIC,
                            i_XFER_DEFAULT_IGNORE_LOGIC,
                            i_XFER_BARRED_TYPE,
                            TO_DATE (i_IVR_BARRED_DATE, 'DDMMYYYY'),
                            i_IVR_ACCESS_COUNT,
                            i_IVR_ARPU_IGNORE_LOGIC,
                            i_IVR_DEFAULT_IGNORE_LOGIC,
                            i_ARPU_CATEGORY,
                            i_CIRCLE_ID,
                            i_XFER_MONTHLY_COUNT,
                            TO_DATE (i_XFER_DATE, 'DDMMYYYY'));

                COMMIT;
                o_error_sts := 'SU';
             END;
          END IF;
       END IF;
    EXCEPTION
       WHEN OTHERS
       THEN
          o_error_sts := 'ER';
          o_error_reason := SQLERRM;
    --dbms_output.put_line('error reason:'||o_error_reason);
    END SP_UPDATE_PROFILE;


    For the above Stored procedure when i am calling from Java program it is giving me ORA-06512: at line 1. when the same procedure executed in toad by setting parameters it is running fine. Please if any one know how to resolve it mail me at althaf.mohammed1@gmail.com

    Thanks,
    Althaf
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have provided nothing to 'resolve'; an ORA-06512 is an informational message that Oracle produces to notify you of the line number of the error (actually in this case the line number where the exception was raised). There is at least another error in that stack which is the true cause of this failure; to receve any useful assistance you must post the entire error stack reported to you. Until you do all we have is Oracle telling us where it encountered an exception but not WHY that exception was raised.