2 Replies Latest reply: Jun 3, 2014 2:57 PM by rp0428 RSS

    How to resolve a numeric or value error in procedure when it is called in callable statement in java?

    3182c01f-78ed-4f9b-9b4d-c3842dcd6cc9

      Hi!

       

      I have a problem about this numeric or value error on my java class. Whenever I am calling for the procedure in callable statement it says that error. I don't know what is the problem with my sql code because I;ve tried running it on database alone and it runs perfectly. The results that I need came out fine. But when I'm already calling it in java that error appears. I really don't know what to do. I'm still an amateur programmer so I really have no idea. But I tried on finding the line that the said error is coming from and here is the code...

       

      create or replace

      PROCEDURE RENTING

      (P_NNAME IN VARCHAR2,

      P_ADD IN VARCHAR2,

      P_PHONE IN NUMBER,

      P_ORDER IN VARCHAR2,

      P_EMP_ID IN NUMBER,

      P_VALID OUT NUMBER,

      P_OR_NO OUT NUMBER

      )

      IS

      V_AVAI  TITLE.AVAILABLE%TYPE;

      P_OR VARCHAR2(5000);

      P_OR_2 VARCHAR2(5000);

      ORD_NO NUMBER(6);

      TID NUMBER(38);

      NUM NUMBER(3);

      CTR NUMBER(3);

      GO NUMBER(1);

      BEGIN

      P_OR:=P_ORDER;

      ORD_NO:=SEQ_ORDER_NO.NEXTVAL;

       

       

      INSERT INTO TRANSACTIONS (ORDER_NO, CUST_NICKNAME, CUST_ADD, CUST_CONTACT)

      VALUES (ORD_NO, P_NNAME, P_ADD, P_PHONE);

       

       

      INSERT INTO RENTS (ORDER_NO,ORDERS,DATERENTED,EMP_ID,DATERETURNED)

      VALUES (ORD_NO,P_OR,SYSDATE,P_EMP_ID,NULL);

       

       

      LOOP

        CTR:=INSTR(P_OR,';');

        NUM:=LENGTH(P_OR);

        IF CTR!=0 THEN

          CTR:=CTR-1;

          P_OR_2:=SUBSTR(P_OR,1,CTR);                                             --here is the line where the error is.

          TID:=TO_NUMBER(P_OR_2,999999);                   

          CTR:=CTR+2;

          P_OR:=SUBSTR(P_OR,CTR,NUM);

          SELECT AVAILABLE

          INTO V_AVAI

          FROM TITLE

          WHERE TITLE_ID=TID;

          IF V_AVAI!=0 THEN

            UP(TID);

            P_VALID:=1;

          ELSIF V_AVAI=0 THEN

            P_VALID:=0;

          END IF;

        ELSIF CTR = 0 THEN

          P_OR_2:=SUBSTR(P_OR,1,NUM);

          TID:=TO_NUMBER(P_OR_2);

          SELECT AVAILABLE

          INTO V_AVAI

          FROM TITLE

          WHERE TITLE_ID=TID;

          IF V_AVAI!=0 THEN

            UP(TID);

            P_VALID:=1;

            P_OR_NO:=ORD_NO;

          ELSIF V_AVAI=0 THEN

            P_VALID:=0;

          END IF;

          GO:=1;

        END IF;

        EXIT WHEN GO=1;

      END LOOP;

      END RENTING;

       

      please please please! I really need some help!!

        • 1. Re: How to resolve a numeric or value error in procedure when it is called in callable statement in java?
          gimbal2

          Well first of all, do-not-wall-yourself-in. Stay away from red herrings. You tested it directly in the database and it worked - fine. That does not prove that there are no problems with the procedure, it only proves that there are no problems with the specific test case you run.

           

          So now you add a different execution path where Java is involved and boom, things blow up. Your first natural response is to want the problem to be outside of the procedure; "Its a Java thing". Don't make that assumption. Its not like Java is executing the procedure, its still the database doing it. That very same database where it previously worked, the only thing that changed here is the way the procedure is triggered to be invoked. So the easiest answer is: there is something different in the way that procedure is invoked. I spot a ton of parameters, start by investigating that you're feeding the procedure properly.

           

          I'm going to suggest this last so its only a footnote and not a poke at your intelligence: before you do anything be 100% positive your Java code is connecting to the database / schema you're expecting it to connect to. You wouldn't believe how many people fall into that easiest of deadly traps!

          • 2. Re: How to resolve a numeric or value error in procedure when it is called in callable statement in java?
            rp0428

            IF CTR!=0 THEN 

                CTR:=CTR-1;

                P_OR_2:=SUBSTR(P_OR,1,CTR);                                             --here is the line where the error is.

                TID:=TO_NUMBER(P_OR_2,999999);                   

            How do you know that line is where the error is? Why didn't you post the actual results and the exception trace?

             

            You have SEVERAL flaws in your code.

             

            You decrement CTR so it could be zero. Then the SUBSTR would return null. Then you would try to use TO_NUMBER on a NULL.

             

            The standard way to troubleshoot PL/SQL code is to either use a debugger (e.g. sql developer) and examine the value of the variables involved or add instrumentation to print out the values so you can SEE what values the code is working with.