-
Problem with an insert
Hello,
I have a DB and run the following script :
UPDATE APPLICATIONVERSION SET APPVERDESC = 'CS6.3SP1 OF THE PICIS DB Start Events' WHERE APPVERDBOID = 27000000000024000000;
SET SERVEROUTPUT ON SIZE 1000000;
DECLARE
V_EVENTDATADBOID NUMBER(21);
V_STARTED DATE;
V_EVENTDATADESC VARCHAR2(255);
V_ISDELETED VARCHAR2(1);
V_PICISDATADBOID NUMBER(21);
V_EVENTDBOID NUMBER(21);
V_STAFFDBOID NUMBER(21);
V_LOCATIONDBOID NUMBER(21);
V_APPLICATIONDBOID NUMBER(21);
V_NUM NUMBER(1);
V_SQLCODE NUMBER;
V_ERRMSG VARCHAR2(250);
c_ZEROPAD CONSTANT VARCHAR2(20) := '00000000000000000000';
CURSOR CEventdata IS
SELECT EVENTDATADBOID,STARTED,EVENTDATADESC,ISDELETED,NVL(PICISDATADBOID,-1),EVENTDBOID,STAFFDBOID
FROM EVENTDATA
WHERE EVENTDBOID IN (66000000000014000000,66000000000015000000);
BEGIN
OPEN CEventdata;
FETCH CEventdata INTO V_EVENTDATADBOID,V_STARTED,V_EVENTDATADESC,V_ISDELETED,V_PICISDATADBOID,V_EVENTDBOID,V_STAFFDBOID;
IF(CEventdata%FOUND) THEN
LOOP
BEGIN
V_APPLICATIONDBOID := 19000000000000000000;
IF V_EVENTDATADESC IS NULL THEN
V_LOCATIONDBOID := 42000000000000000000;
ELSE
SELECT LOCATIONDBOID INTO V_LOCATIONDBOID FROM LOCATIONS WHERE LOCATIONID = TO_NUMBER(SUBSTR(V_EVENTDATADESC,1,3)) ;
IF SUBSTR(V_EVENTDATADESC,7) = 'CPCUSTOMIZE' THEN V_APPLICATIONDBOID := 19000000000014000000; END IF;
IF SUBSTR(V_EVENTDATADESC,7) = 'DBTOUCHUP' THEN V_APPLICATIONDBOID := 19000000000008000000; END IF;
IF SUBSTR(V_EVENTDATADESC,7) = 'VCCUSTOMIZE' THEN V_APPLICATIONDBOID := 19000000000013000000; END IF;
IF SUBSTR(V_EVENTDATADESC,7) = 'CPREMOTEVIEW' THEN V_APPLICATIONDBOID := 19000000000019000000; END IF;
IF SUBSTR(V_EVENTDATADESC,7,9) = '019000000' THEN V_APPLICATIONDBOID := TO_NUMBER(SUBSTR(V_EVENTDATADESC,7)); END IF;
END IF;
INSERT INTO PCS_EVENTDATA
(PCSEVENTDATADBOID,STARTED,PICISDATADBOID,EVENTDBOID,STAFFDBOID,LOCATIONDBOID,APPLICATIONDBOID)
VALUES
(V_EVENTDATADBOID,V_STARTED,NVL(V_PICISDATADBOID,-1),V_EVENTDBOID,V_STAFFDBOID,V_LOCATIONDBOID,V_APPLICATIONDBOID);
EXCEPTION
WHEN OTHERS THEN
V_SQLCODE :=SQLCODE;
V_ERRMSG := SQLERRM;
DBMS_OUTPUT.PUT_LINE('Error on INSERT INTO PCS_EVENTDATA: EVENTDATADBOID=' || TO_CHAR(V_EVENTDATADBOID,c_ZEROPAD) ||' (SQLCODE=' || TO_CHAR(V_SQLCODE) || ') ' || V_ERRMSG);
END;
COMMIT;
FETCH CEventdata INTO V_EVENTDATADBOID,V_STARTED,V_EVENTDATADESC,V_ISDELETED,V_PICISDATADBOID,V_EVENTDBOID,V_STAFFDBOID;
EXIT WHEN CEventdata%NOTFOUND;
END LOOP;
END IF;
CLOSE CEventdata;
/* Update application version */
UPDATE APPLICATIONVERSION
SET APPVERDESC = 'CS6.3SP1 OF THE PICIS DB EventData'
WHERE APPVERDBOID = 27000000000024000000;
COMMIT;
END;
/
SET SERVEROUTPUT OFF
I then get the following error
Error on INSERT INTO PCS_EVENTDATA: EVENTDATADBOID=74138335077564006601(SQLCODE=-1722) ORA-01722: invalid number.
Has any idea what the problem is? What is the reason why this EVENTDATA row fails. All the columns for this row seem ok, but oracle cannot insert this row into PCS_EVENTDATA? I am using Oracle 8.1.7 on Win2000.
Thanks
-
post us description of the 2 tables used
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
These are the two tables
SQL> desc eventdata;
Name Null? Type
----------------------------------------- -------- ---------------
EVENTDATADBOID NOT NULL NUMBER(21)
STARTED DATE
EVENTDATADESC VARCHAR2(128)
ISDELETED NOT NULL VARCHAR2(1)
PICISDATADBOID NUMBER(21)
EVENTDBOID NUMBER(21)
STAFFDBOID NUMBER(21)
UPDATETIME DATE
DOCUMENTED DATE
ENVIRONMENTLOCATIONDBOID NOT NULL NUMBER(21)
CREATE TABLE PCS_EVENTDATA
(
PCSEVENTDATADBOID NUMBER(21),
PCSEVENTDATADESC VARCHAR2(255),
STARTED DATE NOT NULL,
PICISDATADBOID NUMBER(21) DEFAULT -1 NOT NULL,
EVENTDBOID NUMBER(21) NOT NULL,
STAFFDBOID NUMBER(21) DEFAULT 18000000000000000000 NOT NULL,
LOCATIONDBOID NUMBER(21) DEFAULT 42000000000000000000 NOT NULL,
APPLICATIONDBOID NUMBER(21) DEFAULT 19000000000000000000 NOT NULL,
CONSTRAINT PK_PCS_EVENTDATA PRIMARY KEY (PCSEVENTDATADBOID)
USING INDEX
TABLESPACE PCS_RT_INDXS
)TABLESPACE PCS_RT_DATA;
-
thers i more table LOCATIONS...
please check if LOCATIONDBOID in the table LOCATIONS is difined
Number(21)....else it errors if it has defined varchar or char or so....
All other columns are matching with the datatypes in the tables EVENTDATA & PCS_EVENTDATA...
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Yes the LocationDboid is NUMBER(21)
SQL> desc locations;
Name Null? Type
----------------------------------------- -------- ----------------
LOCATIONDBOID NOT NULL NUMBER(21)
LOCATIONDESC VARCHAR2(128)
LOCATIONID NUMBER(3)
COMPUTERNAME VARCHAR2(48)
INITIALS VARCHAR2(3)
DEPTDBOID NUMBER(21)
-
To add :
check this code
IF SUBSTR(V_EVENTDATADESC,7,9) = '019000000' THEN V_APPLICATIONDBOID := TO_NUMBER(SUBSTR(V_EVENTDATADESC,7)); END IF;
i dunno wasss ur Program Logic....but certainly if V_APPLICATIONDBOID has non numeric value then it errors...
Do one thing....output all the fields in exception part....u will get the answer....( it wud be easy to debug )
Abhay.
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Check this example....
Code:
SQL> insert into emp values(1,'123abhay','','');
1 row created.
SQL> commit;
Commit complete.
SQL> desc emp;
Name Null? Type
----------------------------------------------------- -------- -----------------------------
ID NUMBER
EMP_NAME VARCHAR2(32)
ADDRESS VARCHAR2(100)
NICK_NAME VARCHAR2(15)
SQL> select to_number(substr(EMP_NAME,1,3)) from emp where ID=1;
TO_NUMBER(SUBSTR(EMP_NAME,1,3))
-------------------------------
123
SQL> select to_number(substr(EMP_NAME,1,6)) from emp where ID=1;
select to_number(substr(EMP_NAME,1,6)) from emp where ID=1
*
ERROR at line 1:
ORA-01722: invalid number
SQL>
here lies the problem...
SELECT LOCATIONDBOID INTO V_LOCATIONDBOID FROM LOCATIONS WHERE LOCATIONID = TO_NUMBER(SUBSTR(V_EVENTDATADESC,1,3))
Abhay
funky...
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
-
Thanks,
I will check that out.
-
HI,
YES THIS IS THE PROBLEM, ANY IDEAS ABOUT RESOLVING IT?
THANKS
-
The problem seems to be the fact that the EVENTDATADESC columns has some NULL entries, but I thought the line
IF V_EVENTDATADESC IS NULL THEN
V_LOCATIONDBOID := 42000000000000000000;
ELSE SELECT LOCATIONDBOID INTO V_LOCATIONDBOID FROM LOCATIONS WHERE LOCATIONID = TO_NUMBER(SUBSTR(V_EVENTDATADESC,1,3)) ;
would resolve this,
any ideas?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|