Skip to Main Content
  • Questions
  • ERROR Creating a VIEW that has a FUNCTION in a WITH CLAUSE

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Brian.

Asked: June 08, 2017 - 5:07 pm UTC

Last updated: August 03, 2023 - 10:34 am UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0

Viewed 1000+ times

You Asked

I am able to run a query that contains a FUNCTION inside of a WITH clause, which is a new 12c feature, but I get an error when I try to use the same SQL inside a VIEW.
I tried using LiveSQL, but I cannot even get the SQL below to run, which does work on my database.
Perhaps LiveSQL does not have this new 12c feature yet?

This command works when I comment out the 1st line (CREATE VIEW):
--CREATE OR REPLACE VIEW view_test AS
WITH
FUNCTION up(in_desc VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN UPPER(in_desc);
END;
st AS (SELECT up('Chicago') st_desc FROM dual)
SELECT * FROM st
/

But when I add the CREATE VIEW line at the top I get the error below.
ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following...

My Oracle Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0
My SQLPLUS Version on Linux: SQL*Plus: Release 11.2.0.3.0

Any help would be greatly appreciated.

and Connor said...

Make sure you use the up to date SQL Plus version as well

SQL> WITH
  2  FUNCTION up(in_desc VARCHAR2) RETURN VARCHAR2 IS
  3  BEGIN
  4  RETURN UPPER(in_desc);
  5  END;
  6  st AS (SELECT up('Chicago') st_desc FROM dual)
  7  SELECT * FROM st
  8  /

ST_DESC
-----------------------------------------------------------
CHICAGO

1 row selected.

SQL> CREATE OR REPLACE VIEW view_test AS
  2  WITH
  3  FUNCTION up(in_desc VARCHAR2) RETURN VARCHAR2 IS
  4  BEGIN
  5  RETURN UPPER(in_desc);
  6  END;
  7  st AS (SELECT up('Chicago') st_desc FROM dual)
  8  SELECT * FROM st
  9  /

View created.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, May 05, 2023 - 8:15 am UTC

Still the same issue
Chris Saxon
May 09, 2023 - 3:54 pm UTC

What exactly have you tried? Which version of the client and database are you using?

Chris Newman, July 20, 2023 - 4:24 pm UTC

I am getting the same error (ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following...) with the following versions:

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Jul 20 11:18:07 2023
Version 21.10.0.0.0

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.19.0.0.0
Connor McDonald
August 03, 2023 - 10:34 am UTC

Please give us a complete copy/paste like the one below

C:>sqlplus scott/tiger@pdb21a

SQL*Plus: Release 21.0.0.0.0 - Production on Thu Aug 3 18:33:23 2023
Version 21.8.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Thu Aug 03 2023 18:33:03 +08:00

Connected to:
Oracle Database 21c Enterprise Edition Release 21.0.0.0.0 - Production
Version 21.8.0.0.0

SQL> CREATE OR REPLACE VIEW view_test AS
  2  WITH
  3  FUNCTION up(in_desc VARCHAR2) RETURN VARCHAR2 IS
  4  BEGIN
  5  RETURN UPPER(in_desc);
  6  END;
  7  st AS (SELECT up('Chicago') st_desc FROM dual)
  8  SELECT * FROM st
  9  /

View created.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library