Re: PLS-00103: Encountered the symbol "" when expecting one of the following

From: Paul Brownjohn <Paul-no-spam-_at_brownjohn.co.uk>
Date: Fri, 25 Feb 2005 09:13:23 +0100
Message-ID: <421edf3d@news.vo.lu>

DA Morgan wrote:
> Sybrand Bakker wrote:
>
>

>> IIRC the keyword DECLARE is redundant in trigger bodies, as it is in
>> procedure bodies.
>> -- 
>> Sybrand Bakker, Senior Oracle DBA

>
>
> SQL*Plus: Release 10.1.0.3.0 - Production on Thu Feb 24 18:24:40 2005
>
> Copyright (c) 1982, 2004, Oracle. All rights reserved.
>
> Connected to:
> Oracle Database 10g Enterprise Edition Release 10.1.0.3.0 - Production
> With the Partitioning, OLAP and Data Mining options
>
> SQL> drop table t purge;
>
> Table dropped.
>
> SQL> CREATE TABLE t (testcol VARCHAR2(20));
>
> Table created.
>
> SQL> CREATE OR REPLACE TRIGGER statement_level
> 2 BEFORE UPDATE
> 3 ON t
> 4
> 5 DECLARE
> 6
> 7 x VARCHAR2(1);
> 8
> 9 BEGIN
> 10 x := 'A';
> 11 END statement_level;
> 12 /
>
> Trigger created.
>
> SQL> CREATE OR REPLACE TRIGGER statement_level
> 2 BEFORE UPDATE
> 3 ON t
> 4
> 5 --DECLARE
> 6
> 7 x VARCHAR2(1);
> 8
> 9 BEGIN
> 10 x := 'A';
> 11 END statement_level;
> 12 /
> x VARCHAR2(1);
> *
> ERROR at line 7:
> ORA-04079: invalid trigger specification
>
> Redundant? Not at all. By the same token:
>
> SQL> CREATE OR REPLACE PROCEDURE a IS
> 2
> 3 x VARCHAR2(1);
> 4
> 5 BEGIN
> 6 x := 'A';
> 7 END;
> 8 /
>
> Procedure created.
>
> SQL> CREATE OR REPLACE PROCEDURE a IS
> 2
> 3 DECLARE
> 4
> 5 x VARCHAR2(1);
> 6
> 7 BEGIN
> 8 x := 'A';
> 9 END;
> 10 /
>
> Warning: Procedure created with compilation errors.
>
> SQL> show err
> Errors for PROCEDURE A:
>
> LINE/COL ERROR
> -------- -----------------------------------------------------------------
> 3/1 PLS-00103: Encountered the symbol "DECLARE" when expecting one
> of the following:
>
> is also invalid.

I don't think I have made the problem clear... the trigger code that results from running the script is OK. If I copy the trigger that the script creates and execute it, the result is a valid trigger that does what I want. So the actual details of the trigger code are not the problem.

The problem is that when I run the script...it creates the triggers but they are all invalid. If I then attempt a re-compile in situ (using Toad) I get the error. However if I copy the code from Toad ('Create Trigger etc...') and run it in Toad or SQL+ or whatever it creates a valid trigger that works.

I have searched the internet and found some references to incompletely applied patches associated with similar behavior so I brought the code home and ran the whole sequence of operations on my system at home (systems at work are Unix, I don't know what flavour, whilst my home system runs on Windows). The result was still exactly the same. So I guess the problem has to be in the script code. None of the DBAs at work have an answer and its certainly beyond me.

Cheers

Paul BJ Received on Fri Feb 25 2005 - 02:13:23 CST