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!!
