CODEWITHSUNDEEP
sqloracle-databaseplsqlcursorsys-refcursor
Morticia A. Addams
Morticia A. Addams

Reputation: 383

Oracle: Convert Cursor to TABLE for join

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.

Upvotes: 1

Views: 4357

Answers (1)

hekko
hekko

Reputation: 292

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;

Upvotes: 1

Related Questions