Results 1 to 4 of 4

Thread: Ora-06552

  1. #1
    Join Date
    Nov 2008
    Posts
    16

    Unanswered: Ora-06552

    I have a trigger on a staging DB on materialized view CNR_CA_NRTV_MV. But the trigger doesnt get compiled.I dont see any of these issues in the trigger code.Trigger code and compilation errors are below. Please provide valuable comments to resolve it -

    Compilation Errors -
    Compilation errors for TRIGGER SL42CA3P.INCNRSC4_MAIN
    Error: PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-488: invalid variable declaration: object 'TIMESTAMP' must be a type or subtype
    Line: 10
    Text: SELECT COUNT(*) INTO l_exists FROM PS_CA_CNR_CA_NRTV WHERE CNR_CA_ID = :NEW.CNR_CA_ID and CNR_SEQ_NUM = :NEW.CNR_SEQ_NUM AND ROWNUM=1;

    Error: PL/SQL: SQL Statement ignored
    Line: 10
    Text: SELECT COUNT(*) INTO l_exists FROM PS_CA_CNR_CA_NRTV WHERE CNR_CA_ID = :NEW.CNR_CA_ID and CNR_SEQ_NUM = :NEW.CNR_SEQ_NUM AND ROWNUM=1;

    Error: PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-488: invalid variable declaration: object 'TIMESTAMP' must be a type or subtype
    Line: 13
    Text: UPDATE PS_CA_CNR_CA_NRTV

    Error: PL/SQL: SQL Statement ignored
    Line: 13
    Text: UPDATE PS_CA_CNR_CA_NRTV

    Error: PL/SQL: ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-488: invalid variable declaration: object 'TIMESTAMP' must be a type or subtype
    Line: 27
    Text: INSERT INTO PS_CA_CNR_CA_NRTV

    Error: PL/SQL: SQL Statement ignored
    Line: 27
    Text: INSERT INTO PS_CA_CNR_CA_NRTV




    Trigger Code-

    CREATE OR REPLACE TRIGGER INCNRSC4_MAIN
    AFTER INSERT ON CNR_CA_NRTV_MV
    REFERENCING NEW AS NEW
    FOR EACH ROW
    DECLARE
    l_exists INTEGER;
    BEGIN

    SELECT COUNT(*) INTO l_exists FROM PS_CA_CNR_CA_NRTV WHERE CNR_CA_ID = :NEW.CNR_CA_ID and CNR_SEQ_NUM = :NEW.CNR_SEQ_NUM AND ROWNUM=1;
    IF l_exists = 1
    THEN
    UPDATE PS_CA_CNR_CA_NRTV
    SET
    CNR_CA_ID = :NEW.CNR_CA_ID ,
    CNR_SEQ_NUM = :NEW.CNR_SEQ_NUM ,
    cNR_NRTV = :NEW.CNR_NRTV ,
    USERID = :NEW.USERID ,
    MODIFICATION_DATE = :NEW.MODIFICATION_DATE ,
    TIMESTAMP = :NEW.TIMESTAMP
    WHERE CNR_CA_ID = :NEW.CNR_CA_ID and CNR_SEQ_NUM = :NEW.CNR_SEQ_NUM ;
    ELSE
    INSERT INTO PS_CA_CNR_CA_NRTV

    (
    CNR_CA_ID ,
    CNR_SEQ_NUM ,
    CNR_NRTV ,
    USERID ,
    MODIFICATION_DATE ,
    TIMESTAMP
    )
    VALUES
    (
    :NEW.CNR_CA_ID ,
    :NEW.CNR_SEQ_NUM ,
    :NEW.CNR_NRTV ,
    :NEW.USERID ,
    :NEW.MODIFICATION_DATE ,
    :NEW.TIMESTAMP
    ) ;
    END IF;
    END;

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    TIMESTAMP is an Oracle reserved word.
    .
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Nov 2008
    Posts
    16
    Thanks for the reply.

    Actually I know TIMESTAMP is the problem.
    There are 2 things here -
    1) TIMESTAMP is a column in other tables also and triggers on those tables compiled successfully.
    2) I cannot get rid of TIMESTAMP column in this table. Is there any word around etc ?

  4. #4
    Join Date
    Dec 2003
    Posts
    1,074
    Try using "TIMESTAMP" with double-quotes. --=cf

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •