PL/SQL: couldnt 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 ...

thanksproblem solved|||Hi, I am stuck with this same problem. Please can someone help. It is quite urgent.

Thaks in advanced for your help...

Regards,

Paul|||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.|||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|||/// 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...|||I am stuck with the exact same error as above, the code I am using is as follows:

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

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:

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:

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|||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:

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:

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:

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!|||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? Sorry! Apparently I had another name then 'DATE' for my date fields in the other table :-$ So never mind about that!

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!