Problem with an insert

[Diggers12]

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

[abhaysk]

post us description of the 2 tables used


Abhay.

[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;

[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.

[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)

[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.

[abhaysk]

Check this example....

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

[Diggers12]

Thanks,

I will check that out.

[Diggers12]

HI,

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

THANKS

[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?