Thanks for the question, Puzzled.

Asked: December 13, 2017 - 4:54 pm UTC

Last updated: January 18, 2022 - 2:42 am UTC

Version: 12

Viewed 50K+ times! This question is

You Asked

I'm playing around with Oracle 12c and trying out the new features. One of the new features is that the WITH clause in a SQL query now allows for a PL/SQL function.

I created the following sample query:

WITH 
    FUNCTION get_number RETURN NUMBER IS
    BEGIN
        RETURN 12345;
    END;
    
SELECT employee_id, first_name, last_name, get_number()
FROM hr.employees

When I run the query, I get an error:

ORA-00905: missing keyword
Invalid statement
ORA-00904: "GET_NUMBER": invalid identifier

and Connor said...

Are you sure you are on the right version of the SQL Plus (or particular tool you are using) ?

I get this on mine on both 12.1 and 12.2

SQL> WITH
  2      FUNCTION get_number RETURN NUMBER IS
  3      BEGIN
  4          RETURN 12345;
  5      END;
  6  SELECT employee_id, first_name, last_name, get_number()
  7  FROM hr.employees;
  8  /

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 GET_NUMBER()
----------- -------------------- ------------------------- ------------
        100 Steven               King                             12345
        101 Neena                Kochhar                          12345
        102 Lex                  De Haan                          12345
        103 Alexander            Hunold                           12345
        104 Bruce                Ernst                            12345
        105 David                Austin                           12345
        106 Valli                Pataballa                        12345
...

whereas on SQL Plus 11 I'll get:

SQL> WITH
  2      FUNCTION get_number RETURN NUMBER IS
  3      BEGIN
  4          RETURN 12345;
    FUNCTION get_number RETURN NUMBER IS
             *
ERROR at line 2:
ORA-00905: missing keyword


SQL>     END;
SP2-0042: unknown command "END" - rest of line ignored.
SQL> SELECT employee_id, first_name, last_name, get_number()
  2  FROM hr.employees;
SELECT employee_id, first_name, last_name, get_number()
                                           *
ERROR at line 1:
ORA-00904: "GET_NUMBER": invalid identifier

Rating

(7 ratings)

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

Comments

Puzzled, December 14, 2017 - 4:51 pm UTC

OK, thanks. I think it must be the application I'm using then.

Senthilvel, February 06, 2018 - 12:39 pm UTC

Hi,

I am trying to upgrade oracle sql and pl/sql written in oracle 11g to Oracle 12c. I am looking for Oracle 12c to oracle 11g comparison tools and can't find any.

Please let me know how to do this comparison when there are large number of database objects.

Also any link to Oracle 12c coding standards will be helpful.

Thanks,
Senthil

February 07, 2018 - 12:26 am UTC

The database is backward compatible. Code you've written in 11g should run without modification in 12c.

If you want to take advantage of newer features, then check out the new features guide

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/newft/index.html

DML/DDL commands in WITH clause

A reader, August 06, 2018 - 10:19 am UTC

can we use DDL/DML queries in the WITH clause?
Will this throw any error?

August 06, 2018 - 2:10 pm UTC

Yes with a but:

- Only select works "by default"
- Like regular functions in SQL, you have to use an autonomous transaction for other DML & DDL. This is generally A Bad Thing:

create table t (
  c1 int
);

WITH 
    FUNCTION get_number RETURN NUMBER IS
      ret pls_integer;
    BEGIN
      select count(*) into ret from t;
      RETURN ret;
    END;
SELECT get_number()
FROM   dual
/

GET_NUMBER()   
             0 

WITH 
    FUNCTION get_number RETURN NUMBER IS
      ret pls_integer;
    BEGIN
      insert into t values ( 1 );
      execute immediate 'create table t2 ( c1 int )';
      
      RETURN ret;
    END;
SELECT get_number()
FROM   dual
/

ORA-14551: cannot perform a DML operation inside a query 

WITH 
    FUNCTION get_number RETURN NUMBER IS
      ret pls_integer;
      pragma autonomous_transaction;
    BEGIN
      insert into t values ( 1 );
      execute immediate 'create table t2 ( c1 int )';
      commit;
      RETURN ret;
    END;
SELECT get_number()
FROM   dual
/

GET_NUMBER()   
        <null> 

select * from t;

C1   
   1 

desc t2;

Name   Null?   Type         
C1             NUMBER(38)

You CAN do multiple functions; And also a Function and query combinations

Venkat, September 06, 2018 - 9:29 pm UTC

I was wondering if the following was possible with inline functions:

1. Can you include multiple functions in a single select statement
2. Can you include a combination of a Function and a query.

The answer is Yes to both Except that the functions must always be defined first. Otherwise, the compiler gets confused (understandably) starts throwing errors. I am on 12.1 version

with
  function f1(p_id in number) return number is
   begin
    return p_id;
  end;
  function f2(p_id in number) return number is
  begin
    return p_id;
  end;
  qry1 as (select 12 nbr from dual),
  qry2 as (select 'blah' data from dual)
select f1(id), f2(id), qry1.nbr, qry2.data
from   t, qry1, qry2
where  rownum < 3
;

This returns 2 rows (as expected)

1108 1108 12 blah
1109 1109 12 blah

However, if I try to put the query first followed by the function, I get all kinds of errors:

with 
  qry1 as (select 12 nbr from dual),
  function f1(p_id in number) return number is
   begin
    return p_id;
  end;
select f1(id), qry1.nbr
from   t, qry1
where  rownum < 3
;

ORA-00942: table or view does not exist
00942. 00000 -  "table or view does not exist"
*Cause:    
*Action:
Error at Line: 8 Column: 11

September 10, 2018 - 4:55 am UTC

The docs do suggest that ordering is mandatory

WITH_PLSQL

Blank lines are not allowed within inline PLSQL functions

Shridhar Kumar, October 22, 2021 - 7:14 pm UTC

The issue of the OP is with the blank line in between the inline PLSQL function definition and the SQL statement. Also, if there are blank lines anywhere inside the PLSQL function definition, we will get invalid statement error.

October 26, 2021 - 2:34 am UTC

I don't think thats the case

SQL> WITH
  2      FUNCTION get_number RETURN NUMBER IS
  3      BEGIN
  4          RETURN 12345;
  5      END;
  6
  7  SELECT employee_id, first_name, last_name, get_number()
  8  FROM hr.employees
  9  /

EMPLOYEE_ID FIRST_NAME           LAST_NAME                 GET_NUMBER()
----------- -------------------- ------------------------- ------------
        100 Steven               King                             12345
        101 Neena                Kochhar                          12345
        102 Lex                  De Haan                          12345
        103 Alexander            Hunold                           12345
        104 Bruce                Ernst                            12345
        105 David                Austin                           12345

Not everything is perfect with this feature

Sasha, January 13, 2022 - 4:24 pm UTC

Unfortunately this only works in SQL, i.e. you cannot have a cursor loop in PL/SQL with inline PL/SQL functions in your query like this:

begin
  for r in (
    with function inline_function(p in number) return number
    as
    begin
      return 1;
    end;
    inline_view as (
      select * from dual
    )
    select * from inline_view
  ) loop
    null;
  end loop;
end;

January 14, 2022 - 6:04 am UTC

This is known bug. Workaround is dynamic sql

another workaround would be to use Views.

Rajeshwaran, Jeyabal, January 14, 2022 - 6:58 am UTC

Another workaround would be to push with clause plsql function implementation inside the view - something like this

demo@XEPDB1> create or replace view v as
  2   with function inline_function(p in number) return number
  3      as
  4      begin
  5        return 1;
  6      end;
  7      inline_view as (
  8        select * from dual
  9      )
 10      select * from inline_view
 11  /

View created.

Then your above anonymous plsql call will become like this.

demo@XEPDB1> begin
  2    for r in (
  3      select * from v
  4    ) loop
  5      null;
  6    end loop;
  7  end;
  8  /

PL/SQL procedure successfully completed.

demo@XEPDB1>

January 18, 2022 - 2:42 am UTC

agreed

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

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

DML/DDL commands in WITH clause

You CAN do multiple functions; And also a Function and query combinations

Blank lines are not allowed within inline PLSQL functions

Not everything is perfect with this feature

another workaround would be to use Views.

More to Explore

PL/SQL demos

PL/SQL docs

Oracle 12c - SQL query with inline PL/SQL function

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

DML/DDL commands in WITH clause

You CAN do multiple functions; And also a Function and query combinations

Blank lines are not allowed within inline PLSQL functions

Not everything is perfect with this feature

another workaround would be to use Views.

More to Explore

PL/SQL demos

PL/SQL docs