RE: PL/SQL error?

Out of sheer morbid curiosity I reproduced your table and trigger and then paired it down to a contrived example (not very educational though - well not quite, I learnt about plsql_warinings and the intricacies of line numbering).

I think it was off by a line and was complaining about the condition block in the "if statement" as not reachable and the error goes away if you give the parser something to chew about; in this case 1 = 1.

-Krish

SQL> CREATE OR REPLACE TRIGGER trigger_mailids_after AFTER UPDATE ON t1 FOR EACH ROW
  2 BEGIN
  3 IF TRUE THEN
  4 null;
  5 END IF;
  6 END;
  7 /

SP2-0814: Trigger created with compilation warnings

SQL> show error
Errors for TRIGGER TRIGGER_MAILIDS_AFTER:

LINE/COL ERROR

-------- -----------------------------------------------------------------
2/4      PLW-06002: Unreachable code

SQL> CREATE OR REPLACE TRIGGER trigger_mailids_after AFTER UPDATE ON t1 FOR EACH ROW
  2 BEGIN
  3 IF 1 = 1 AND TRUE THEN
  4 null;
  5 END IF;
  6 END;
  7 /

Trigger created.

SQL> show error
No errors.

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of William Wagman
Sent: Tuesday, November 27, 2007 5:21 PM To: Jared Still
Cc: oracle-l_at_freelists.org
Subject: RE: PL/SQL error?

I got numerous suggestions...

Exchanging <> with != doesn't change the error.

Wrapping with a BEGIN ... END; doesn't change the error.

Moving one of the statements above the IF...END IF; to below it makes the error go away.

Go figure. I think it's a parsing bug. Case closed. :-)

Thanks for your help.

Bill Wagman
Univ. of California at Davis
IET Campus Data Center
wjwagman_at_ucdavis.edu
(530) 754-6208
-----Original Message-----
From: Jared Still [mailto:jkstill_at_gmail.com] Sent: Tuesday, November 27, 2007 4:17 PM To: William Wagman
Cc: oracle-l_at_freelists.org
Subject: Re: PL/SQL error?

On 11/27/07, Jared Still <jkstill_at_gmail.com> wrote:
>
> The posted trigger is for all of update, delete, insert.
> What happens if you remove INSERT and DELETE from the trigger?
>

Well, that wasn't it:

16:16:13 SQL>alter session set plsql_warnings = 'enable:all';

Session altered.

16:16:13 SQL>
16:16:13 SQL>create table my_table ( x integer);

Table created.

16:16:13 SQL>
16:16:13 SQL>CREATE OR REPLACE
16:16:13   2  TRIGGER my_table_trg
16:16:13   3    AFTER INSERT OR DELETE OR UPDATE
16:16:13   4    ON my_table
16:16:13   5    FOR EACH ROW
16:16:13   6  DECLARE
16:16:13   7          v integer;
16:16:13   8  BEGIN
16:16:13   9          IF INSERTING THEN
16:16:13  10                  v := 1;
16:16:13  11          ELSIF UPDATING THEN
16:16:13  12                  IF :NEW.x <> :OLD.x
16:16:13  13                  THEN
16:16:13  14                          v := 1;
16:16:13  15                  END IF;
16:16:13  16          ELSIF DELETING THEN
16:16:13  17                  v := 1;
16:16:13  18          END IF;
16:16:13  19  END;
16:16:13  20  /

Trigger created.

16:16:13 SQL>
16:16:13 SQL>show error
No errors.
16:16:13 SQL> Perhaps you could provide a reproducible test case?

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l