Home » SQL & PL/SQL » SQL & PL/SQL » Trigger Error + the declaration of the type of this expression is incomplete or malformed (Oracle 10g, Windows 2003 Server)
Trigger Error + the declaration of the type of this expression is incomplete or malformed [message #340844] Thu, 14 August 2008 10:12 Go to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

hi i am getting the following error while creating the trigger,

please help me for the same.


SQL> ED
Wrote file afiedt.buf

  1  CREATE OR REPLACE TRIGGER HELLO BEFORE INSERT OR UPDATE ON NoPushAudit
  2  FOR EACH ROW
  3  DECLARE
  4  v_newVal NUMBER(4) := 0;
  5  v_incval NUMBER(4) := 0;
  6  BEGIN
  7    IF INSERTING AND :new.pkey IS NULL THEN
  8      SELECT  NoPushAudit_pkey_SEQ.NEXTVAL INTO v_newVal FROM DUAL;
  9      -- If this is the first time this table have been inserted into (sequence == 1)
 10      IF v_newVal = 1 THEN
 11        --get the max indentity value from the table
 12        SELECT max(pkey) INTO v_newVal FROM NoPushAudit;
 13        v_newVal := v_newVal + 1;
 14        --set the sequence to that value
 15        LOOP
 16             EXIT WHEN v_incval>=v_newVal;
 17             SELECT NoPushAudit_pkey_SEQ.nextval INTO v_incval FROM dual;
 18        END LOOP;
 19      END IF;
 20      -- save this to emulate @@identity
 21     sqlserver_utilities.identity := v_newVal;
 22     -- assign the value from the sequence to emulate the identity column
 23     :new.pkey := v_newVal;
 24    END IF;
 25* END;
SQL> /
CREATE OR REPLACE TRIGGER HELLO BEFORE INSERT OR UPDATE ON NoPushAudit
                                                           *
ERROR at line 1:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed
Re: Trigger Error + the declaration of the type of this expression is incomplete or malformed [message #340875 is a reply to message #340844] Thu, 14 August 2008 14:09 Go to previous messageGo to next message
Barbara Boehmer
Messages: 8625
Registered: November 2002
Location: California, USA
Senior Member
You should not select from the same table that a trigger is on within that trigger as it can cause a mutating issue. You should set your starting value when you create your sequence, not within your trigger. Without your table structure and code for sqlserver_utilities.identity it is hard to guess what might be causing your error.
Re: Trigger Error + the declaration of the type of this expression is incomplete or malformed [message #340920 is a reply to message #340875] Thu, 14 August 2008 22:57 Go to previous messageGo to next message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

I tried the following trigger but it also gave me the same error.

  1  CREATE OR REPLACE TRIGGER HELLO BEFORE INSERT OR UPDATE ON NoPushAudit
  2  FOR EACH ROW
  3  DECLARE
  4  BEGIN
  5  END;


SQL> /
CREATE OR REPLACE TRIGGER HELLO BEFORE INSERT OR UPDATE ON NoPushAudit
                                                           *
ERROR at line 1:
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-320: the declaration of the type of this expression is
incomplete or malformed



and it seems like the error points to NoPushAudit, so i tried to run the same on different table and trigger got created with compilation error.

now that seems like issue with NoPushAudit table. but i am not able to understand.
Re: Trigger Error + the declaration of the type of this expression is incomplete or malformed [message #340921 is a reply to message #340844] Thu, 14 August 2008 23:08 Go to previous messageGo to next message
BlackSwan
Messages: 25035
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

using sqlplus post results from below using CUT & PASTE via <code tags>

SQL> DESC NoPushAudit
SQL> select object_type from user_objects where object_name = 'NOPUSHAUDIT';
Re: Trigger Error + the declaration of the type of this expression is incomplete or malformed [message #340926 is a reply to message #340920] Fri, 15 August 2008 00:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
i tried to run the same on different table and trigger got created with compilation error.

Apart from the current error, your trigger is invalid as it contains no statement. There must be at least "NULL;" between BEGIN and END.

Regards
Michel
Re: Trigger Error + the declaration of the type of this expression is incomplete or malformed [message #340991 is a reply to message #340920] Fri, 15 August 2008 08:06 Go to previous messageGo to next message
joy_division
Messages: 4640
Registered: February 2005
Location: East Coast USA
Senior Member
naikjigar wrote on Thu, 14 August 2008 23:57

and it seems like the error points to NoPushAudit, so i tried to run the same on different table and trigger got created with compilation error.

now that seems like issue with NoPushAudit table. but i am not able to understand.


Please use proper grammar as this sentence makes no sense. To me, this means that you claim that the error must be on the table NoPushAudit, since when you try to create the trigger on a different table, the trigger also fails.
Re: Trigger Error + the declaration of the type of this expression is incomplete or malformed [message #341236 is a reply to message #340991] Sun, 17 August 2008 23:21 Go to previous message
naikjigar
Messages: 51
Registered: July 2008
Location: India
Member

SQL>  DESC NoPushAudit;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PKEY                                               NUMBER(10)
 MOBILENO                                           VARCHAR2(30 CHAR)
 ID                                                 VARCHAR2(25 CHAR)
 NEWVALUE                                           NUMBER(10)
 OLDVALUE                                           NUMBER(10)
 TIMESTAMP                                          DATE

SQL>  select object_type from user_objects where object_name = 'NOPUSHAUDIT';

OBJECT_TYPE
-------------------
TABLE

SQL> 
Previous Topic: Pack decimal
Next Topic: Fiscal Year SQL query
Goto Forum:
  


Current Time: Sat Dec 03 18:31:48 CST 2016

Total time taken to generate the page: 0.07393 seconds