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

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

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:

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

"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.

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 ?

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.

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.