Problem with an insert
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: Problem with an insert

  1. #1
    Join Date
    Apr 2001
    Posts
    142

    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

  2. #2
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  3. #3
    Join Date
    Apr 2001
    Posts
    142
    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;

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  5. #5
    Join Date
    Apr 2001
    Posts
    142
    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)

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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"

  8. #8
    Join Date
    Apr 2001
    Posts
    142
    Thanks,

    I will check that out.

  9. #9
    Join Date
    Apr 2001
    Posts
    142
    HI,

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

    THANKS

  10. #10
    Join Date
    Apr 2001
    Posts
    142
    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
  •  



Click Here to Expand Forum to Full Width