Results 1 to 10 of 10
  1. #1
    Join Date
    May 2002
    Posts
    16

    Unanswered: PL/SQL: couldn't create trigger

    Hi,

    i have a mass operation with some triggers to increment the id of a table ...

    now, some of the copy&pasted statements working, some won't ...

    part of the statement:

    CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_LICENSE_ID BEFORE INSERT ON SHEMA.LICENSE REFERENCING OLD AS old NEW AS new FOR EACH ROW
    (some pl/sql syntax)

    ^^ this doesn't print out any error.

    CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_SOFTWARE_ID BEFORE INSERT ON SHEMA.SOFTWARE REFERENCING OLD AS old NEW AS new FOR EACH ROW
    (some pl/sql syntax)

    ^^ there's an error like:
    CREATE OR REPLACE TRIGGER SHEMA.TRG_INS_SEQ_WORKSTATION_ID BEFORE INSERT ON SHEMA.WORKST
    *
    ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-320: the declaration of the type of this expression is
    incomplete or malformed

    whats going wrong?? both tables exists in the database with an id field defined as number(10) and flagged with a primary key.

    maybe somebody can explain ...

    thanks

  2. #2
    Join Date
    May 2002
    Posts
    16
    problem solved

  3. #3
    Join Date
    Aug 2002
    Posts
    28
    Hi, I am stuck with this same problem. Please can someone help. It is quite urgent.

    Thaks in advanced for your help....

    Regards,

    Paul

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    This is what the error means:

    Cause: In a declaration, the name of a variable or cursor is misspelled or the declaration makes a forward reference. Forward references are not allowed in PL/SQL. A variable or cursor must be declared before it is referenced it in other statements, including other declarative statements. For example, the following declaration of dept_rec raises this exception because it refers to a cursor not yet declared:

    DECLARE
    dept_rec dept_cur%ROWTYPE;
    CURSOR dept_cur IS SELECT ...
    ...
    Without seeing your code, it's hard to comment further.

  5. #5
    Join Date
    Aug 2002
    Posts
    28
    Hi,

    This problem is getting strange.

    I am using this code to product a primary key for a table. I have created a sequene and am using the next value from the sequence to act as the key.

    The code I am using for the trigger is as follows

    BEGIN
    IF :new.ORDER_ID IS NULL THEN
    SELECT AUTO_ORDER_ID.NEXTVAL ///SEQUENCE
    INTO :new.ORDER_ID
    FROM dual;
    END IF;
    END;

    This has worked for about 8 tables with the exception of this one. I have tried to re-create the table using a different name and a different ID atttribute i.e table called sales and Sales_id and still no joy.

    any ideas??

    Oh.. I have also reinstalled the whole 9i database and I still get the same problem

    Thanks for your help in advanced.

    Regards,

    Paul

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    /// is not a valid comment syntax in PL/SQL, so that won't work! If that isn't the problem, I can't see anything obvious. You are getting:

    ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed

    yes? On what line?

    If still struggling, please post the ENTIRE trigger text exactly as you are running it - including the CREATE..., and the description of the table...

  7. #7
    Join Date
    Feb 2002
    Location
    San Francisco, CA
    Posts
    441
    I am stuck with the exact same error as above, the code I am using is as follows:
    Code:
    CREATE OR REPLACE TRIGGER "FYP"."TEST" BEFORE
    INSERT ON "FYP"."JOBS" 
        FOR EACH ROW begin
      select seq_jobs.nextval into :new.id from dual;
    end;
    sequence named seq_jobs exists.

    The table named exists too and the code is as follows:
    Code:
    CREATE TABLE "FYP"."JOBS" ("ID" NUMBER(10), "ALARM_TEXT" 
        VARCHAR2(255 byte), "POINT_ID" NUMBER(10), "DATE" DATE, "PRORITY" 
        NUMBER(10))  
        TABLESPACE "FYP" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 
        255 
        STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 
        2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) 
        PARALLEL ( DEGREE DEFAULT ) 
        LOGGING
    This is really annoying: I cannot see the forward reference in the code: I don't see anything referenced apart from the table, the colum ID and the sequence...

  8. #8
    Join Date
    Jun 2003
    Posts
    5
    Hello all,

    I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:

    ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.


    Code for the table:
    Code:
    CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID" 
        NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT 
        NULL, "MESSAGE" NVARCHAR2(50) NOT NULL, 
        CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
        "SOURCE_ID") 
        REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"), 
        CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
        "UNKNOWNMSG_ID") USING 
        INDEX  
        TABLESPACE "DCSMSG" 
        STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS 
        2 MAXTRANS 255) 
        TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 
        255 
        STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) 
        LOGGING
    Code for the Sequence:
    Code:
    CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY 
        1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE 
        CACHE 20 ORDER
    Code for the Trigger:
    Code:
    CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR" 
        BEFORE INSERT 
        ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG" 
        FOR EACH ROW BEGIN 
    SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL 
    INTO :NEW.UNKNOWNMSG_ID 
    FROM DUAL;
    END;
    Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.

    Any help would be very much appreciated! Thanks!

    With kind regards,



    Gog

  9. #9
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by Gog
    Hello all,

    I'm pretty new to Oracle and I'm having the same problem. I got a DB with 14 tables, and on 5 of them I can't create a trigger and get the same errors as above:

    ORA-06552: PL/SQL: Compilation unit analysis terminated
    ORA-06553: PLS-320: the declaration of the type of this expression is incomplete or malformed.


    Code for the table:
    Code:
    CREATE TABLE "DCSMSG"."DCSMSG_TBL_UNKOWNMSG"("UNKNOWNMSG_ID" 
        NUMBER NOT NULL, "SOURCE_ID" NUMBER NOT NULL, "DATE" DATE NOT 
        NULL, "MESSAGE" NVARCHAR2(50) NOT NULL, 
        CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_FID" FOREIGN KEY(
        "SOURCE_ID") 
        REFERENCES "DCSMSG"."DCSMSG_TBL_SOURCES"("SOURCE_ID"), 
        CONSTRAINT "DCSMSG_TBL_CNSTR_UNKOWN_ID" PRIMARY KEY(
        "UNKNOWNMSG_ID") USING 
        INDEX  
        TABLESPACE "DCSMSG" 
        STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) PCTFREE 10 INITRANS 
        2 MAXTRANS 255) 
        TABLESPACE "DCSMSG" PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 
        255 
        STORAGE ( INITIAL 64K NEXT 0K MINEXTENTS 1 MAXEXTENTS 2147483645 
        PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1) 
        LOGGING
    Code for the Sequence:
    Code:
    CREATE SEQUENCE "DCSMSG"."DCSMSG_SEQ_UNKMSG_AUTONR" INCREMENT BY 
        1 START WITH 1 MAXVALUE 1.0E28 MINVALUE 1 NOCYCLE 
        CACHE 20 ORDER
    Code for the Trigger:
    Code:
    CREATE OR REPLACE TRIGGER "DCSMSG"."DCSMSG_TBL_TRG_UNKMSG_AUTONR" 
        BEFORE INSERT 
        ON "DCSMSG"."DCSMSG_TBL_UNKNOWNMSG" 
        FOR EACH ROW BEGIN 
    SELECT DCSMSG.DCSMSG_SEQ_UNKMSG_AUTONR.NEXTVAL 
    INTO :NEW.UNKNOWNMSG_ID 
    FROM DUAL;
    END;
    Anyone who can help me out? I did the same thing for the other 8 tables, and it worked fine. I re-created these 5 tables, but didn't work. removing the foreign keys didn't work either. I can't see the problem here.

    Any help would be very much appreciated! Thanks!

    With kind regards,



    Gog
    Looking at your code and that of Rhs98 posted earlier, I notice that you both have table and column names in double quotes, and in fact both have a column called "DATE" which is of course a reserved word in Oracle.

    Changing the column name from "DATE" to ADATE (i.e. anything that isn't a reserved word) makes the problem go away.

    Moral: NEVER put double quotes round table and column names, and (as a consequence) NEVER use reserved words as table and column names!

  10. #10
    Join Date
    Jun 2003
    Posts
    5
    hey thanks for the fast reply .. It seems to have worked. But this raises another question.

    Why did a few other tables with a field called 'DATE' in it work properly than? [edit] Sorry! Apparently I had another name then 'DATE' for my date fields in the other table :-$ So never mind about that![/edit]

    And ps. I didn't put the quotes there, DBA Studio studio gave that code to me when I selected 'show object ddl' :-D

    thx for the help!
    Last edited by Gog; 06-04-03 at 07:10.

Posting Permissions

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