0

I'm having trouble running this piece of code. It says that the trigger was created with compilation errors but does not explain what's wrong and does not give any output. Below is the code and the error I'm given. I'm on Oracle 11g R1 and am using PL/SQL.

CREATE OR REPLACE TRIGGER checkRecommendedAge
BEFORE INSERT OR UPDATE ON Loan
FOR EACH ROW
DECLARE
    borAge number;
    ex     exception;

BEGIN
    SELECT count(*) INTO borAge
    FROM Loan
    WHERE dateOut =: new.dateOut;
    IF borAge < 18 THEN
        dbms.output.put_line('Row added to Book table succesful');
    ELSE
        raise ex;
    END IF;

    EXCEPTION
    WHEN EX THEN
    raise_application_error("Error");
END;
/

Error:

    Warning: Trigger created with compilation errors.

SQL> SHOW ERRORS
Errors for TRIGGER CHECKRECOMMENDEDAGE:

LINE/COL ERROR
-------- -----------------------------------------------------------------
0/0      PLS-00801: internal error [ph2csql_strdef_to_diana:bind]
6/2      PL/SQL: SQL Statement ignored
7/7      PL/SQL: ORA-06544: PL/SQL: internal error, arguments:
         [ph2csql_strdef_to_diana:bind], [], [], [], [], [], [], []

10/3     PL/SQL: Statement ignored
10/3     PLS-00201: identifier 'DBMS.OUTPUT' must be declared
17/2     PL/SQL: Statement ignored
17/27    PLS-00201: identifier 'Error' must be declared

Any help would be appreciated

Thanks

DCookie
  • 42,630
  • 11
  • 83
  • 92
Brian
  • 1,951
  • 16
  • 56
  • 101
  • Try issuing `SHOW ERRORS` after the compilation. – Chandu Jan 13 '12 at 13:52
  • @Cybernate Hi thanks! I updated original post – Brian Jan 13 '12 at 13:59
  • 1
    @Brian - Are you sure there are no copy & paste errors? The error message references an identifier `dbms.output` which implies that you made a typo when you referenced the `dbms_output` package. But your code doesn't seem to have that typo. The error message also references an identifier `Error` when that text doesn't appear anywhere in the posted code. Also, can you post the complete trigger definition including the `CREATE`? – Justin Cave Jan 13 '12 at 14:29
  • @Justin Cave How do i do the complete trigger thing ? – Brian Jan 13 '12 at 15:06
  • @Brian - I'm not sure I follow the question. Presumably, you're creating a trigger by writing a statement that begins `CREATE [OR REPLACE] TRIGGER <>`, right? Just copy and paste the entire `CREATE TRIGGER` statement here rather than just posting the body of the trigger that begins with `DECLARE`. – Justin Cave Jan 13 '12 at 15:09
  • I'm sorry but pet peeve. Why `dbms_output` in a trigger? Who's going to be there to check. It's just an annoyingly miniscule waste of time and CPU. – Ben Jan 13 '12 at 23:51

4 Answers4

1

You're specifying dbms.output. Try dbms_output.put_line in your trigger.

EDIT:

You're calling RAISE_APPLICATION_ERROR incorrectly. First, don't use a double-quoted string here - that indicates an identifier, not a string. Second, the syntax for this procedure is:

raise_application_error(error_number, message[, {TRUE | FALSE}]);

See the documentation for a more thorough treatment.

You're also likely to hit the dreaded "ORA-04091: Table XXXXX is mutating" error, because you're selecting from the same table that the trigger is firing for. See this AskTom article for more information.

DCookie
  • 42,630
  • 11
  • 83
  • 92
0

Give this a try.

Changes:

dbms.output to dbms_output

dateOut =: to dateOut =

raise_application_error("Error"); to raise_application_error('Error');

CREATE OR REPLACE TRIGGER checkRecommendedAge
BEFORE INSERT OR UPDATE ON Loan
FOR EACH ROW
DECLARE
    borAge number;
    ex     exception;

BEGIN
    SELECT count(*) INTO borAge
    FROM Loan
    WHERE dateOut = new.dateOut;
    IF borAge < 18 THEN
        dbms_output.put_line('Row added to Book table succesful');
    ELSE
        raise ex;
    END IF;

    EXCEPTION
    WHEN EX THEN
    raise_application_error('Error');
END;
/
Dan
  • 5,081
  • 1
  • 18
  • 28
0

Also,

WHERE dateOut =: new.dateOut;

should probably be:

WHERE dateOut = new.dateOut;
eaolson
  • 14,717
  • 7
  • 43
  • 58
-2

Where's the EXIT WHEN bookCursor%NOTFOUND; ?

Ops ;-) Try to post the full trigger. What's the size of the dbms buffer ?

Levi
  • 1
  • 1