Oracle: Convert Cursor to TABLE for join

Question

I have tree different tables with equals data types.

I have to spool files but first I have to join every of this table with other one and create file with results.

Example:

TAB1

ID|CODE
-------
1 | A
2 | B
3 | A

TAB2

ID|CODE
-------
1 | C
2 | C
3 | A

TAB3

ID|CODE
-------
1 | C
2 | B
3 | B

NOM_CODES

CODE|DESC
A   | desc1
B   | desc1
C   | desc1
D   | desc1

This is one of the tree(one for every table) procedure:

procedure proc_tab1 is

  l_File_Handle Utl_File.File_Type;
  v_File_Name   VARCHAR2(100);

begin
  v_File_Name := 'TAB_1.TXT';

  IF Utl_File.Is_Open(l_File_Handle) THEN
    Utl_File.Fclose(l_File_Handle);
  END IF;

  l_File_Handle := Utl_File.Fopen(l_DIR, v_File_Name, 'W', l_Len);

  for c in (select t.ID||','||t.code||','||c.desc
            from tab1 t
            join nom_codes c
              on t.code = c.code) loop
    Utl_File.Put_Line(l_File_Handle,
                      Convert(c.file_line, 'CL8MSWIN1251') || l_cr);
  end loop;

  Utl_File.Fclose(l_File_Handle);

end proc_tab1 ;

I want to do repeated(open file, join, put_line, close file) things in one new procedure.

Something like this:

procedure proc_tab1 is

  v_File_Name   VARCHAR2(100);

begin
  v_File_Name := 'TAB_1.TXT';

  spool_file(v_File_Name, cursor(select t.id, t.code from t1));

end proc_tab1 ;

And this new procedure:

procedure spool_file (p_file_name varchar2, p_curr sys_refcursor) is

  l_File_Handle Utl_File.File_Type;

begin 

  IF Utl_File.Is_Open(l_File_Handle) THEN
    Utl_File.Fclose(l_File_Handle);
  END IF;
l_File_Handle := Utl_File.Fopen(l_DIR, p_file_name , 'W', l_Len);

  for c in (select t.ID||','||t.code||','||c.desc
            from table(p_curr) t
            join nom_codes c
              on t.code = c.code) loop
    Utl_File.Put_Line(l_File_Handle,
                      Convert(c.file_line, 'CL8MSWIN1251') || l_cr);
  end loop;

  Utl_File.Fclose(l_File_Handle);
end spool_file ;

That I want is somehow to pass rowset from tab1 (and tab2 and tab3) to spool_file. In spool_file to convert this rowset to table and make the join and other common thinks.

Actually I tried this but I can't convert SYS_REFCURSOR to TABLE. I can create one function that return pipelined result but I think that is not good idea because I will have to iterations over the data twice(one for pipeline and one for loop).

I'm open for ideas.

I think you will need to write a pipelined table function that accepts a ref cursor and returns a collection. There is no built-in functionality to do this. — William Robertson, Jan 15 '18 at 15:57
You could consider doing this with [dynamic SQL](https://docs.oracle.com/cloud/latest/db112/LNPLS/dynamic.htm#LNPLS011) instead, and have a generic function to write your files. You have plenty of resources on SO: https://stackoverflow.com/questions/3554892/static-vs-dynamic-sql ; https://stackoverflow.com/a/208892/6019417 ... — J. Chomel, Jan 15 '18 at 16:10
Actually, I have large queries and it will be ugly and unreadable to do it dynamic. — Morticia A. Addams, Jan 17 '18 at 08:09
Can you post how you would like the output file to look like? — Tenzin, Jan 17 '18 at 15:46

hekko · Answer 1 · 2018-01-21T03:10:01.100

You can do crazy things in Oracle, but often this mean what architecture is wrong:

declare
    l_cur1 SYS_REFCURSOR;
    l_cur2 SYS_REFCURSOR;
begin
--test data
open l_cur1 for select 1 id, 'A' code from dual union all select 2 id, 'B' code from dual;
open l_cur2 for select 'A' code, 'A-VALUE' name from dual union all select 'B' code, 'B-VALUE' name from dual;

--convert cursors in xml and parse and joins
for c in ( 
    select tab1.id, tab1.code, tab2.NAME
    from ( 
            select extractvalue( value( src1 ), 'ROW/ID' ) ID, extractvalue( value( src1 ), 'ROW/CODE' ) CODE 
            from table( xmlsequence( xmltype( l_cur1 ).extract( 'ROWSET/ROW' ) ) ) src1 ) tab1
    left join (
            select extractvalue( value( src2 ), 'ROW/CODE' ) CODE, extractvalue( value( src2 ), 'ROW/NAME' ) NAME 
            from table( xmlsequence( xmltype( l_cur2 ).extract( 'ROWSET/ROW' ) ) ) src2 ) tab2 on tab1.CODE = tab2.CODE
) 
loop
        dbms_output.put_line( c.id );
        dbms_output.put_line( c.code );
        dbms_output.put_line( c.name );
        dbms_output.put_line( '---------------' );
end loop;

end;

Oracle: Convert Cursor to TABLE for join

1 Answers1