WITH clause with procedure/function problem

[twrzodak]

Hi
PL/SQL dev. 12.0.4.1826 64bit
Oracle Client & oci.dll 12.1 64bit

When the first declaration in WITH clause is PROCEDURE, I get error:

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

and sql window splits the query to WITH query and pl/sql block.
for example:

WITH
  PROCEDURE getObj(p_obj  OUT  VARCHAR2)
  IS
    l_pd_serch  INT;
  BEGIN
    p_obj := 'a';
  END;
  FUNCTION getO RETURN VARCHAR2
  IS
    l_obj VARCHAR(200) := 'a';
  BEGIN
    getObj(l_obj);
    RETURN l_obj;
  END;
SELECT getO FROM dual
/

But when the first declaration is FUNCTION, there isn't any error - a whole query is executed.
for example:

WITH
  FUNCTION xx RETURN VARCHAR2 IS
  BEGIN
    RETURN NULL;
  END;
  PROCEDURE getObj(p_obj  OUT  VARCHAR2)
  IS
    l_pd_serch  INT;
  BEGIN
    p_obj := 'a';
  END;
  FUNCTION getO RETURN VARCHAR2
  IS
    l_obj VARCHAR(200) := 'a';
  BEGIN
    getObj(l_obj);
    RETURN l_obj;
  END;
SELECT getO FROM dual
/

I do not know if this will help, but the behaviour of sql*plus 11 is similar to pl/sql developer when sqlterminator is set to on. There is no problem when sqlterminator is set to off or sqplus version is 12 (regardless of setting sqlterminator).

 

[Marco Kalter]

This seems like a parser bug. We'll fix it for the next patch release.