Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Help with trigger compilation errors

Help with trigger compilation errors

From: <tim.mcconechy_at_runtime.dk>
Date: Tue, 06 Apr 1999 13:52:07 GMT
Message-ID: <7ed3i4$t7c$1@nnrp1.dejanews.com>


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 Received on Tue Apr 06 1999 - 08:52:07 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US