| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Help with trigger compilation errors
In article <7ed3ij$ted$1_at_nnrp1.dejanews.com>,
tim.mcconechy_at_runtime.dk wrote:
> Hello,
>
> This code usually works fine: CREATE OR REPLACE TRIGGER pdmlog_audit_info
> BEFORE INSERT OR UPDATE ON "PDMLOG" FOR EACH ROW BEGIN IF INSERTING THEN
> :new.user_name := user; :new.lastedit := sysdate; ELSIF UPDATING THEN
> :new.user_name := user; :new.lastedit := sysdate; END IF; END; /
ERROR at
> line 2: ORA-06552: PL/SQL: Compilation unit analysis terminated ORA-06553:
> PLS-320: the declaration of the type of this expression is incomplete or
> malformed
>
> I run this only changing pdmlog to the table name....
>
> For some reason the trigger won't work on two tables out of 130.
> PDMLOG is one of them...
>
> Here is the code to duplicate the problem.
>
> DROP TABLE "PDMLOG"
> /
>
> CREATE TABLE "PDMLOG" (
> "LOGNUM" number(6) NOT NULL,
> "USER" varchar2(5) NOT NULL,
> "PDMNAME" varchar2(5) NOT NULL,
> "DATABASE" varchar2(10) NOT NULL,
> "TYPE" varchar2(30) NULL,
> "DATE" date NOT NULL,
> "TIME" varchar2(8) NOT NULL,
> "MAINKEY" varchar2(125) NULL,
> "OLINEKEY" varchar2(180) NULL,
> "NLINEKEY" varchar2(180) NULL,
> "DATA" varchar2(2000) NULL,
> "STATUS1" varchar2(40) NULL,
> "STATUS2" varchar2(40) NULL,
> "ADMSTATUS" varchar2(40) NULL,
> "ADMSTATUS2" varchar2(40) NULL,
> "ADMCODE" varchar2(8) NULL,
> "CHANGED" NUMBER(10) NULL,
> "RECNO" number(20) NULL,
> "USER_NAME" number(2) NULL,
> "LASTEDIT" varchar2(20) NULL
> )
> PCTFREE 20
> PCTUSED 40
> /
>
> CREATE INDEX pdmlogmainkeyidx ON "PDMLOG" (mainkey)
>
> GRANT SELECT , INSERT , DELETE , UPDATE ON "PDMLOG" TO public
> /
>
> DROP SEQUENCE pdmlogseq
> /
>
> CREATE SEQUENCE pdmlogseq INCREMENT BY 1
> /
>
> CREATE OR REPLACE TRIGGER pdmlog_audit_info BEFORE INSERT OR UPDATE ON
> "PDMLOG" FOR EACH ROW BEGIN IF INSERTING THEN :new.user_name := user;
> :new.lastedit := sysdate; ELSIF UPDATING THEN :new.user_name := user;
> :new.lastedit := sysdate; END IF; END; /
>
> ERROR at line 2: ORA-06552: PL/SQL: Compilation unit analysis terminated
> ORA-06553: PLS-320: the declaration of the type of this expression is
> incomplete or malformed
>
> However , If I take another table....
> DROP TABLE "PICTURE"
> /
>
> CREATE TABLE "PICTURE" (
> "MAINKEY" varchar2(125) NOT NULL,
> "PICTPICT01" varchar2(100) NOT NULL,
> "FPICTCOM1" varchar2(40) NOT NULL,
> "CPICTCOM1" varchar2(40) NOT NULL,
> "PICTPICT02" varchar2(100) NOT NULL,
> "FPICTCOM2" varchar2(40) NOT NULL,
> "CPICTCOM2" varchar2(40) NOT NULL,
> "PICTPICT03" varchar2(100) NOT NULL,
> "FPICTCOM3" varchar2(40) NOT NULL,
> "CPICTCOM3" varchar2(40) NOT NULL,
> "PICTPICT04" varchar2(100) NOT NULL,
> "FPICTCOM4" varchar2(40) NOT NULL,
> "CPICTCOM4" varchar2(40) NOT NULL,
> "PICTPICT05" varchar2(100) NOT NULL,
> "FPICTCOM5" varchar2(40) NOT NULL,
> "CPICTCOM5" varchar2(40) NOT NULL,
> "PICTPICT06" varchar2(100) NOT NULL,
> "FPICTCOM6" varchar2(40) NOT NULL,
> "CPICTCOM6" varchar2(40) NOT NULL,
> "PICTPICT07" varchar2(100) NOT NULL,
> "FPICTCOM7" varchar2(40) NOT NULL,
> "CPICTCOM7" varchar2(40) NOT NULL,
> "PICTPICT08" varchar2(100) NOT NULL,
> "FPICTCOM8" varchar2(40) NOT NULL,
> "CPICTCOM8" varchar2(40) NOT NULL,
> "PICTPICT09" varchar2(100) NOT NULL,
> "FPICTCOM9" varchar2(40) NOT NULL,
> "CPICTCOM9" varchar2(40) NOT NULL,
> "PICTPICT10" varchar2(100) NOT NULL,
> "FPICTCOM10" varchar2(40) NOT NULL,
> "CPICTCOM10" varchar2(40) NOT NULL,
> "PICTPICT11" varchar2(100) NOT NULL,
> "FPICTCOM11" varchar2(40) NOT NULL,
> "CPICTCOM11" varchar2(40) NOT NULL,
> "PICTPICT12" varchar2(100) NOT NULL,
> "FPICTCOM12" varchar2(40) NOT NULL,
> "CPICTCOM12" varchar2(40) NOT NULL,
> "LASTEDIT1" varchar2(15) NULL,
> "LASTEDIT2" varchar2(15) NULL,
> "CHANGED" NUMBER(10) NULL,
> "RECNO" number(20) NULL,
> "USER_NAME" number(2) NULL,
> "LASTEDIT" varchar2(20) NULL
> )
> PCTFREE 20
> PCTUSED 40
> /
>
> DROP TABLE pictureimages
> /
>
> CREATE TABLE pictureimages ( MAINKEY varchar2(125) NOT NULL, CODE number(2)
> NOT NULL,PICGPICT LONG RAW NOT NULL) /
>
> CREATE INDEX pictureimagesidx ON pictureimages (MAINKEY)
> /
>
> CREATE INDEX picturemainkeyidx ON "PICTURE" (mainkey)
>
> GRANT SELECT , INSERT , DELETE , UPDATE ON "PICTURE" TO public
> /
>
> DROP SEQUENCE pictureseq
> /
>
> CREATE SEQUENCE pictureseq INCREMENT BY 1
> /
>
> CREATE OR REPLACE TRIGGER picture_audit_info BEFORE INSERT OR UPDATE ON
> "PICTURE" FOR EACH ROW BEGIN IF INSERTING THEN :new.user_name :=
user;
> :new.lastedit := sysdate; ELSIF UPDATING THEN :new.user_name := user;
> :new.lastedit := sysdate; END IF; END; /
>
> It works fine.....
>
> Can anyone help me????
> I trieed pdmlog instead of "PDMLOG"
>
> -----------== Posted via Deja News, The Discussion Network ==----------
> http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own
>
SQL> DROP TABLE "PDMLOG"
2 /
Table dropped.
SQL> ------------Don't use reserved word for column naming !!! ------- SQL> CREATE TABLE "PDMLOG" (
2 "LOGNUM" number(6) NOT NULL, 3 "USER" varchar2(5) NOT NULL, 4 "PDMNAME" varchar2(5) NOT NULL, 5 "DATABASE" varchar2(10) NOT NULL, 6 "TYPE" varchar2(30) NULL, 7 -------- Your mistake
10 "ANY_OTHER_IDENT" date not null, 11 "TIME" varchar2(8) NOT NULL, 12 "MAINKEY" varchar2(125) NULL, 13 "OLINEKEY" varchar2(180) NULL, 14 "NLINEKEY" varchar2(180) NULL, 15 "DATA" varchar2(2000) NULL, 16 "STATUS1" varchar2(40) NULL, 17 "STATUS2" varchar2(40) NULL, 18 "ADMSTATUS" varchar2(40) NULL, 19 "ADMSTATUS2" varchar2(40) NULL, 20 "ADMCODE" varchar2(8) NULL, 21 "CHANGED" NUMBER(10) NULL, 22 "RECNO" number(20) NULL, 23 "USER_NAME" number(2) NULL, 24 "LASTEDIT" varchar2(20) NULL
Table created.
SQL>
SQL> CREATE INDEX pdmlogmainkeyidx ON "PDMLOG" (mainkey)
2 /
Index created.
SQL>
SQL> GRANT SELECT , INSERT , DELETE , UPDATE ON "PDMLOG" TO public
2 /
Grant succeeded.
SQL>
SQL> DROP SEQUENCE pdmlogseq
2 /
Sequence dropped.
SQL>
SQL> CREATE SEQUENCE pdmlogseq INCREMENT BY 1
2 /
Sequence created.
SQL>
SQL> CREATE OR REPLACE TRIGGER pdmlog_audit_info BEFORE INSERT OR UPDATE ON
2 "PDMLOG" FOR EACH ROW BEGIN IF INSERTING THEN :new.user_name := user;
3 :new.lastedit := sysdate; ELSIF UPDATING THEN :new.user_name := user;
4 :new.lastedit := sysdate; END IF; END;
5 /
Trigger created.
SQL> spool off
Best regards,
Sergey Kaplun.
-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Thu Apr 08 1999 - 11:25:54 CDT
 |
 |