Join/ Passparemeter to SQL query which is being returned via an oracle function

Question

SELECT COLUMN_VALUE,
   SUBSTR (COLUMN_VALUE, 0, INSTR (COLUMN_VALUE, '#') - 1)
                                                          AS machine_code,
   SUBSTR (SUBSTR (COLUMN_VALUE, INSTR (COLUMN_VALUE, '#') + 1),
           0,
             INSTR (SUBSTR (COLUMN_VALUE, INSTR (COLUMN_VALUE, '#') + 1),
                    '#'
                   )
           - 1
          ) AS shift_start,
   SUBSTR (COLUMN_VALUE,
           instr2 (COLUMN_VALUE, '#', 1, 2) + 1
          ) AS shift_stop
FROM TABLE (ppc_report.get_current_shift_hourly_roll ('CRMP2', 550));

there is one function named "get_current_shift_hourly_roll" which is returning tabular data, My problem is how do i pass the parameters 'CRMP2' OR 550 using data from join table or when used in inner query from outer query or join table ?? CRMP2 is machine_code

Answer 1

Not sure this is that you mean, but pipelined table functions can corellate in FROM clause with ordinar tables, for example:

SQL> create table t_char (x int, y varchar2(10));

SQL> insert into t_char select rownum,'A'||rownum
  2  from dual connect by level <= 10;

SQL> commit;

SQL> create table t
  2  as
  3  select rownum x from dual connect by level <= 10;

SQL> create type t_vc2 is table of varchar2(10);
  2  /

SQL> create or replace function f_pipe(
  2   p_x in number
  3  )
  4  return t_vc2
  5  pipelined
  6  is
  7  begin
  8   for cur in (select * from t_char
  9     where x = p_x) loop
 10      pipe row(cur.y);
 11   end loop;
 12   return;
 13  end;
 14  /


SQL> select t.x, s.column_value from t,
  2  table(f_pipe(t.x)) s
  3  /

         X COLUMN_VAL                                                           
---------- ----------                                                           
         1 A1                                                                   
         2 A2                                                                   
         3 A3                                                                   
         4 A4                                                                   
         5 A5                                                                   
         6 A6                                                                   
         7 A7                                                                   
         8 A8                                                                   
         9 A9                                                                   
        10 A10                                                                  

SQL> insert into t_char select rownum, 'B'||rownum from dual connect by level <= 4;

SQL> commit;

SQL> select t.x, s.column_value from t,
  2  table(f_pipe(t.x)) s
  3  /

         X COLUMN_VAL                                                           
---------- ----------                                                           
         1 A1                                                                   
         1 B1                                                                   
         2 A2                                                                   
         2 B2                                                                   
         3 A3                                                                   
         3 B3                                                                   
         4 A4                                                                   
         4 B4                                                                   
         5 A5                                                                   
         6 A6                                                                   
         7 A7                                                                   
         8 A8                                                                   
         9 A9                                                                   
        10 A10