Skip to Main Content
  • Questions
  • Inline function in a select sub query or create view

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: April 12, 2017 - 10:08 am UTC

Last updated: October 30, 2017 - 2:29 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

I need some suggestion, how can I use an inline function in a select sub query or create view, as up to my knowledge it is possible in oracle 12c.

Code:

select  /*+ WITH_PLSQL */ calc from
(
with 
function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual
);


Error:
ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:

   . ( * @ % & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || member submultiset
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:
Error at Line: 4 Column: 1


Thanks in advance

with LiveSQL Test Case:

and we said...

If you just have a straightforward SQL query, place the with clause at the top:

with function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual;

CALC  
6  


You only need the /*+ with_plsql */ hint if you're using insert, update, delete or merge.

Rating

  (5 ratings)

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

Comments

Unable to create view over inline sub function

Noman Ud Din, April 19, 2017 - 7:40 am UTC

Hi,

I am still unable to create a view or use it as a sub query

Creating view
create or replace view abc 
as 
(with function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual);


Using it as a subquery
select * from 
(with function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual)a;


Error:
rror starting at line : 1 in command -
create or replace view abc
as
(with function calculator (m number, r number) return number
is begin
return m * r
Error report -
SQL Error: ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:

   . ( * @ % & = - + ; < / > at in is mod remainder not rem
   <an exponent (**)> <> or != or ~= >= <= <> and or like like2
   like4 likec between || member submultiset
06553. 00000 -  "PLS-%s: %s"
*Cause:    
*Action:

Error starting at line : 6 in command -
end calculator
Error report -
Unknown Command

>>Query Run In:Query Result

Chris Saxon
April 19, 2017 - 8:30 am UTC

This appears to be a client issue, which environment are you running these in?

For example, in SQL*Plus the subquery works fine:

SQL> select  /*+ WITH_PLSQL */ calc from
  2  (
  3  with
  4  function calculator (m number, r number) return number
  5  is begin
  6  return m * r;
  7  end calculator;
  8  select calculator(3, 2) as calc from dual
  9  );
 10  /

      CALC
----------
         6


But in SQL Developer:

select  /*+ WITH_PLSQL */ calc from
(
with
function calculator (m number, r number) return number
is begin
return m * r;
end calculator;
select calculator(3, 2) as calc from dual
);
/

Error starting at line : 23 in command -
select  /*+ WITH_PLSQL */ calc from
(
with
function calculator (m number, r number) return number
is begin
return m * r
Error at Command Line : 26 Column : 1
Error report -
SQL Error: ORA-06553: PLS-103: Encountered the symbol "end-of-file" when expecting one of the following:


When creating the view, remove the outer parenthesis and you'll be fine in SQL*Plus:

SQL> create or replace view abc as
  2  with function calculator (m number, r number) return number is begin
  3  return m * r;
  4  end calculator;
  5  select calculator(3, 2) as calc from dual;
  6  /

View created.

SQL> select * from abc;

      CALC
----------
         6

you got same error and dont give explanation

A reader, April 19, 2017 - 9:20 am UTC

"This appears to be a client issue, which environment are you running these in?
"

I would ask you same question ;
I expect a clear explanation ; unless you don't have which is not a disaster just say I don't know why such behaviour.

In SQLPlus its working

Noman Ud Din, April 19, 2017 - 9:21 am UTC

Yes I am using oracle SQL developer.
Can you suggest some configuration with which I can develop such type of code in sql developer or any other alternative ?
Connor McDonald
April 22, 2017 - 2:29 am UTC

I've passed this demo onto the SQL Dev PM...it might be a bug in the parser.

In the interim, I think you'll need to use SQL Plus - you can link that into SQL Developer via the steps here

https://oracledeli.wordpress.com/2011/09/23/sql-developer_execute_via_sqlplus/

with SQL Dev 4.2/ SQL Plus 12.2 / SQLCI 4.2

Rajeshwaran, April 24, 2017 - 11:28 am UTC

Tried this in SQL Dev 4.2/ SQL Plus 12.2 / SQLCI 4.2 and it went fine.

from sqlplus 12.2
demo@ORA12C> create or replace view v
  2  as
  3  with
  4  function calculator (m number, r number) return number
  5  is begin
  6  return m * r;
  7  end calculator;
  8  select calculator(3, 2) as calc from dual
  9  /

View created.

demo@ORA12C> select * from v ;

      CALC
----------
         6

demo@ORA12C>

from SQLCL 4.2 (latest build from sqldeveloper.oracle.com)
C:\Users\admin>sql /nolog

SQLcl: Release 4.2.0 Production on Mon Apr 24 16:45:13 2017

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


idle> conn demo/demo@ora12c
Connected.


demo@ORA12C> create or replace view v
  2  as
  3  with
  4  function calculator (m number, r number) return number
  5  is begin
  6  return m * r;
  7  end calculator;
  8  select calculator(3, 2) as calc from dual
  9  /



View V created.


demo@ORA12C>
demo@ORA12C> select * from v;

      CALC
----------
         6


Also, Tried with SQL Developer Version 4.2.0.17.089 - no issues again.

Execute Immediate workaround for earlier client versions

Toph, October 28, 2017 - 6:45 pm UTC

Oracle Database 12.1.0.2.0
Using TOAD 12.0.0.61

I have been having an issue trying to include the WITH Function clause in a view. The SQL would execute fine until I tried to put it into a create view script. Then it would fail attempting to create the view.

@Rajeshwaran commets helped to point me in the correct direction. I do not have control over the client version where I work but wanted to create a view which used the WITH function clause. As a workaround I was able to wrap the view script in an Execute Immediate statement and created the view. The view works when tested. This helped to show it was a client side issue and not something with the database. Hope this helps others.
Connor McDonald
October 30, 2017 - 2:29 am UTC

Thanks for getting back to us

More to Explore

Hints

Need more information on hints? Check out the complete hint descriptions