MacLochlainns Weblog

Teaching PL/SQL can be fun and sometimes challenging when you need to troubleshoot a student error. Take the Oracle PLS-00103 error can be very annoying when it return like this:

24/5     PLS-00103: Encountered the symbol "LV_CURRENT_DATE" WHEN
         expecting one OF the following:
         language

Then, you look at the code and see:

22
23
24
25

   , pv_user_id             NUMBER ) IS
 
    /* Declare local constants. */
    lv_current_date      DATE := TRUNC(SYSDATE);

Obviously, there’s nothing wrong on the line number that the error message pointed. Now, here’s where it gets interesting because of a natural human failing. The student thought they had something wrong with declaring the variable and tested as stand alone procedure and anonymous block. Naturally, they were second guessing what they knew about the PL/SQL.

That’s when years of experience with PL/SQL kicks in to solve the problem. The trick is recognizing two things:

1. The error message points to the first line of code in a package body.
2. The error is pointing to the first character on the line after the error.

That meant that the package body was incorrectly defined. A quick check to the beginning of the package body showed:

1
2
3
4
5
6

CREATE OR REPLACE
  PACKAGE account_creation AS
 
  PROCEDURE insert_contact
  ( pv_first_name          VARCHAR2
  , pv_middle_name         VARCHAR2 := NULL

The student failed to designate the package as an implementation by omitting the keyword BODY from line 2. The proper definition of the package body should be:

1
2
3
4
5
6

CREATE OR REPLACE
  PACKAGE BODY account_creation AS
 
  PROCEDURE insert_contact
  ( pv_first_name          VARCHAR2
  , pv_middle_name         VARCHAR2 := NULL

That’s the resolution for the error message. I wrote this because I checked if they should have been able to find a helpful article with a google search. I discovered that there wasn’t an answer like this that came up after 10 minutes of various searches.

As always, I hope this helps those writing PL/SQL.