How to send an embedded table as a parameter to a function that receives a table?

Question

I need to use return_tbl () from the code below inside the WITH clause, and then pass the inline table created with the WITH clause as a parameter to the function. As in use_tbl_v2 (does not work at this time)

using_tbl_v1 is just an example of what works (but for me it is simple).

And I realized that by creating an embedded table, I exit PLSQL mode and go into SQL mode. But how do I return to PLSQL mode to deliver original_tbl to receive_tbl (...)

create or replace type SOME_OBJ force as object (
  SOME_VARCHAR varchar2(20 byte)
);

create or replace type SOME_TBL is table of SOME_OBJ;


create or replace function returning_tbl
  return SOME_TBL pipelined is

begin
  for current_row in (
    select
      'SOME_VALUE' as SOME_VARCHAR
    from dual
  )
  loop
    pipe row (
      SOME_OBJ(
        current_row.SOME_VARCHAR
      )
    );
  end loop;
  return;
END returning_tbl;

select * from table(returning_tbl());


create or replace function receiving_tbl(tbl SOME_TBL)
  return SOME_TBL pipelined is

begin
  for current_row in (
    with filtered_tbl as (
      select
        SOME_VARCHAR
      from table(tbl)
      where SOME_VARCHAR = 'SOME_VALUE'
    )
    select * from filtered_tbl
  )
  loop
    pipe row (
      SOME_OBJ(
        current_row.SOME_VARCHAR
      )
    );
  end loop;
  return;
END receiving_tbl;


select * from table(receiving_tbl(returning_tbl()));


create or replace function using_tbl_v1
  return SOME_TBL pipelined is

begin
  for current_row in (
    with original_tbl as (
      select
        SOME_VARCHAR
      from table(returning_tbl())
      where SOME_VARCHAR = 'SOME_VALUE'
    ),
    outside_inlined_tbl as ( --just as example
      select * from table(receiving_tbl(returning_tbl()))
    )
    select * from outside_inlined_tbl
  )
  loop
    pipe row (
      SOME_OBJ(
        current_row.SOME_VARCHAR
      )
    );
  end loop;
  return;
END using_tbl_v1;


select * from table(using_tbl_v1());


create or replace function using_tbl_v2
  return SOME_TBL pipelined is

begin

  for current_row in (
    with original_tbl as (
      select
        SOME_VARCHAR
      from table(returning_tbl())
      where SOME_VARCHAR = 'SOME_VALUE'
    ),
    outside_tbl as (
      select * from table(receiving_tbl( original_tbl ))
    )
    select * from outside_tbl
  )
  loop
    pipe row (
      SOME_OBJ(
        current_row.SOME_VARCHAR
      )
    );
  end loop;
  return;
END using_tbl_v2;


select * from table(using_tbl(_v2));

Answer 1

Replace:

with original_tbl as (
  select
    SOME_VARCHAR
  from table(returning_tbl())
  where SOME_VARCHAR = 'SOME_VALUE'
),
outside_tbl as (
  select * from table(receiving_tbl( original_tbl
  ))
)
select * from outside_tbl

WITH

with original_tbl as (
  select
    SOME_VARCHAR
  from table(returning_tbl())
  where SOME_VARCHAR = 'SOME_VALUE'
),
outside_tbl as (
  select * from table(receiving_tbl(
     (select cast(collect(SOME_OBJ(SOME_VARCHAR)) as SOME_TBL) from original_tbl)
  ))
)
select * from outside_tbl

, . , , - .