How to send inline table as parameter to function that receives a table?

Question

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

using_tbl_v1 is just an example of things that work (but they are to simple for me).

And I realized that, once I create an inline table, I am exiting PLSQL mode and going into SQL mode. But how do I go back inside PLSQL mode to deliver original_tbl to receiving_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));

score 1 · Accepted Answer · answered Nov 15 '16 at 03:31

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

I'd like to add some simple explanation of what's happening here. But this example is so complicated I'm not sure if there's any easy lesson to learn here.

How to send inline table as parameter to function that receives a table?

1 Answers1