Compiling a stored procedure with local functions on Oracle 9i

Question

I have a legacy application that is still running on Oracle 9i. We'll me migrating to 11g later this year, but for the moment, I need to run some test scripts on the current environment.

My test uses a stored procedure, but when I try to compile the procedure I get the following error.

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

This is strange as the stored procedure compiled on the same server about two years ago. The only difference is that the database has been overwritten with a copy of the production database since (this procedure is only used in test, so is not present in the production database).

The problem seems to stem from local functions within the procedure. Here's a very simple procedure that illustrates the error I'm getting:

create or replace procedure test
as
  l_dt date;

  function dt
  return date
  is 
  begin
    return sysdate;
  end;

begin
  l_dt := dt;
  dbms_output.put_line(to_char(l_dt, 'dd-mm-yyyy'));
end; 

Edit: Here's the complete output when I try to compile this:

1   ORA-24344: success with compilation error
10  PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
10  
10     begin function package pragma procedure form
13  ORA-06550: line 2, column 3:
13  PLS-00201: identifier 'L_DT' must be declared
13  ORA-06550: line 2, column 3:
13  PL/SQL: Statement ignored
14  ORA-06550: line 3, column 32:
14  PLS-00201: identifier 'L_DT' must be declared
14  ORA-06550: line 3, column 3:
14  PL/SQL: Statement ignored
14  SQL parse error location

It definitely seems to be the local function that's causing it, as if I take it out, it compiles just fine. Here's the above procedure with the local function removed, and that working:

create or replace procedure test
as
  l_dt date;
begin
  l_dt := sysdate;
  dbms_output.put_line(to_char(l_dt, 'dd-mm-yyyy'));
end; 

I wonder if anyone else has encountered this problem, or knows if there's any reason local functions wouldn't compile under Oracle 9i?

Thanks,

James

Answer 1

The error is being reported against line 10, after the end of the function, so the enclosing procedure isn't being parsed properly. What you're doing is fine though, and works through other clients, and with the version of SqlDbx I tried. It looks like the procedure is being treated as two statements, and sent to the database in two chunks, neither of which is valid on its own.

This problem is reported on the SqlDbx Oracle forum, which mentions the bug exists in versions 3.64 and 4.0. It doesn't appear to be specific to the Oracle version. On 2014-04-14 the response was:

This is a bug and it will be fixed in a follow up to the latest release in about two weeks.

The release notes for version 4.1 include:

Fixes:
1. Corrupted text returned in Unicode version
2. Error parsing nested function (Oracle)
...

So you need to upgrade to version 4.1 or later; the current version is 4.3, and I don't see this problem with that version against a 9i or 11g database.