Problem with an insert

Printable View

03-05-2003, 07:58 AM
Diggers12

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
03-05-2003, 08:10 AM
abhaysk

post us description of the 2 tables used

Abhay.
03-05-2003, 08:45 AM
Diggers12

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;
03-05-2003, 08:56 AM
abhaysk

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.
03-05-2003, 08:59 AM
Diggers12

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)
03-05-2003, 09:03 AM
abhaysk

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.
03-05-2003, 09:16 AM
abhaysk

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
03-05-2003, 09:20 AM
Diggers12

Thanks,

I will check that out.
03-05-2003, 10:37 AM
Diggers12

HI,

YES THIS IS THE PROBLEM, ANY IDEAS ABOUT RESOLVING IT?

THANKS
03-05-2003, 10:50 AM
Diggers12

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?
03-05-2003, 10:55 AM
slimdave

i would expect that SUBSTR(V_EVENTDATADESC,1,3) is returning non-numerics -- i don't think nulls would be a problem.
03-05-2003, 11:09 AM
slimdave

i would expect that SUBSTR(V_EVENTDATADESC,1,3) is returning non-numerics -- i don't think nulls would be a problem.
03-06-2003, 12:25 AM
abhaysk

Quote:

Originally posted by Diggers12
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?

Ofcourse it wud resolve if EVENTDATADESC column is NULL....
Problem arrises if EVENTDATADESC column is not NULL & substr(V_EVENTDATADESC,1,3) has non numeric values as told by SLIMDAVE...

U cud resolve as ( this is jus 1 method )

Code:

IF V_EVENTDATADESC IS NULL THEN V_LOCATIONDBOID := 42000000000000000000; ELSE Begin SELECT LOCATIONDBOID INTO V_LOCATIONDBOID FROM LOCATIONS WHERE LOCATIONID = TO_NUMBER(SUBSTR(V_EVENTDATADESC,1,3)) ; EXCEPTION WHEN OTHERS THEN V_LOCATIONDBOID = 'Wat Ever U want To Set as Substr reurned non numeric value & cannot be joined' or if u wish not to perform any action...then jus give NULL END;

u may need to take care of this part of code as well

IF SUBSTR(V_EVENTDATADESC,7,9) = '019000000' THEN V_APPLICATIONDBOID := TO_NUMBER(SUBSTR(V_EVENTDATADESC,7)); END IF;

Abhay

[/code]