Thanks for the question, Herman.

Asked: October 04, 2018 - 2:49 am UTC

Last updated: January 17, 2023 - 4:31 pm UTC

Version: 11g

Viewed 10K+ times! This question is

You Asked

Bit of a newbie, and hoping I can get pointed in the right direction. I've simplified things to demonstrate the issue I'm experiencing (and I'm really struggling to get a clear answer on other posts).

When running the following:

with
  function f_double(x in number)
    return number
    is y number(10,2);
  begin
    y:=0;
    select query_val into y from my_query;
    y:=x*y;
    return y;
  end;
  my_query as (
  select 2 as query_val from dual)
select f_double(2) as my_result from dual;

I get the error:

ORA-06552: PL/SQL: ORA-00942: table or view does not exist
06552. 00000 -  "PL/SQL: %s"
*Cause:    
*Action:
Error at Line: 2 Column: 25

When I replace the select statement in the f_double function with the following:

select 1 into y from dual;

it works - so I'm assuming my issue here is with the way I've defined and/or used the my_query section at the bottom.

Any help would be appreciated.

and Chris said...

There are a couple of issues here:

- PL/SQL in the with clause is a 12c feature. Assuming your version of 11 is correct, you can't use this yet!
- You're trying to access a named subquery in your PL/SQL block. But you define this after the function. So the code can't see it!

Put this subquery inside

with
  function f_double(x in number)
    return number
    is y number(10,2);
  begin
    y:=0;
    select query_val into y from (
      select 2 as query_val from dual
    );
    y:=x*y;
    return y;
  end;
select f_double(2) as my_result from dual;

MY_RESULT   
          4

Though I'm confused as to why you're trying to get the value 2 from the subquery inside the function...

If my_query gets the value to double, use this in your main select and pass it to the function:

with
  function f_double(x in number)
    return number
    is y number(10,2);
  begin
    y:=2;
    y:=x*y;
    return y;
  end;
  my_query as (
  select 2 as query_val from dual)
select f_double(query_val) as my_result from my_query;

MY_RESULT   
          4

Rating

(2 ratings)

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

Comments

with clause "subquery" and function together.

Rajeshwaran, Jeyabal, January 17, 2023 - 7:44 am UTC

Team,

Is it not possible to have with clause "subquery" and function together? the following (was modelled like our application code) is from ATP 19c

with rws as ( 
 select object_name r from all_objects 
 where rownum <= 10
 union all 
 select username from all_users 
 where rownum <= 10 
 ) 
, function foo( p_str varchar2 )
 return varchar2  
 as
 begin 
  return ( replace(p_str,'!@#$%^&*()') ) ;
 end;
select r, foo(r) from rws ;

but ended with errors like this

demo@PDB1> with rws as (
  2     select object_name r from all_objects
  3     where rownum <= 10
  4     union all
  5     select username from all_users
  6     where rownum <= 10
  7     )
  8  , function foo( p_str varchar2 )
  9     return varchar2
 10     as
 11     begin
 12             return ( replace(p_str,'!@#$%^&*()') ) ;
, function foo( p_str varchar2 )
           *
ERROR at line 8:
ORA-00905: missing keyword


demo@PDB1>      end;
SP2-0042: unknown command "end" - rest of line ignored.
demo@PDB1> select r, foo(r)
  2  from rws ;
from rws
     *
ERROR at line 2:
ORA-00942: table or view does not exist

so we modified like this and it worked.

demo@PDB1> with function foo( p_str varchar2 )
  2  return varchar2
  3  as
  4  begin
  5     return ( replace(p_str,'!@#$%^&*()') ) ;
  6  end;
  7  select r, foo(r)
  8  from (
  9  select object_name r from all_objects
 10  where rownum <= 10
 11  union all
 12  select username from all_users
 13  where rownum <= 10
 14      )
 15  /

R                              FOO(R)
------------------------------ ------------------------------
I_FILE#_BLOCK#                 I_FILE#_BLOCK#
I_OBJ3                         I_OBJ3
I_TS1                          I_TS1
I_CON1                         I_CON1
IND$                           IND$
CDEF$                          CDEF$
C_TS#                          C_TS#
I_CCOL2                        I_CCOL2
I_PROXY_DATA$                  I_PROXY_DATA$
I_CDEF4                        I_CDEF4
SYS                            SYS
AUDSYS                         AUDSYS
SYSTEM                         SYSTEM
OUTLN                          OUTLN
GSMADMIN_INTERNAL              GSMADMIN_INTERNAL
GSMUSER                        GSMUSER
DIP                            DIP
REMOTE_SCHEDULER_AGENT         REMOTE_SCHEDULER_AGENT
DBSFWUSER                      DBSFWUSER
ORACLE_OCM                     ORACLE_OCM

20 rows selected.

January 17, 2023 - 4:31 pm UTC

Inline WITH clause PL/SQL functions must come before named subqueries.

with clause "subquery" and function together.

Rajeshwaran, Jeyabal, January 18, 2023 - 6:06 am UTC

Having WITH clause PL/SQL function before the named subquery works. Thanks.

demo@PDB1> with function foo( p_str varchar2 )
  2    return varchar2
  3    as
  4    begin
  5       return ( replace(p_str,'!@#$%^&*()') ) ;
  6    end;
  7   rws as (   select object_name r from all_objects
  8    where rownum <= 10
  9    union all
 10    select username from all_users
 11    where rownum <= 10 )
 12  select r, foo(r)
 13  from rws
 14  /

R                              FOO(R)
------------------------------ ------------------------------
I_FILE#_BLOCK#                 I_FILE#_BLOCK#
I_OBJ3                         I_OBJ3
I_TS1                          I_TS1
I_CON1                         I_CON1
IND$                           IND$
CDEF$                          CDEF$
C_TS#                          C_TS#
I_CCOL2                        I_CCOL2
I_PROXY_DATA$                  I_PROXY_DATA$
I_CDEF4                        I_CDEF4
SYS                            SYS
AUDSYS                         AUDSYS
SYSTEM                         SYSTEM
OUTLN                          OUTLN
GSMADMIN_INTERNAL              GSMADMIN_INTERNAL
GSMUSER                        GSMUSER
DIP                            DIP
REMOTE_SCHEDULER_AGENT         REMOTE_SCHEDULER_AGENT
DBSFWUSER                      DBSFWUSER
ORACLE_OCM                     ORACLE_OCM

20 rows selected.

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

with clause "subquery" and function together.

with clause "subquery" and function together.

More to Explore

SQL

Using function in conjunction with WITH query clause

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

with clause "subquery" and function together.

with clause "subquery" and function together.

More to Explore

SQL