Paul Brownjohn <Paul-no-spam-_at_brownjohn.co.uk> wrote in
news:421edf3d_at_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
Privs acquired via a ROLE work OK via SQL*Plus, but do not exist within
PL/SQL procedures.
Received on Fri Feb 25 2005 - 09:45:34 CST
