ORACLE: write to file result of function that return sys_refcursor

Question

I must create package that will have three procedures which will write result of another function to the file. Function get_cursor(...) return sys_refcursor looks like this:

function get_cursor(
    tabname in varchar2,
    cols    in array_t,
    vals    in array_t,
    rels    in array_t )
  return sys_refcursor
is
  cur sys_refcursor;
  where_statement varchar2(1000);
  tmp             varchar2(1000);
begin
  if cols.last                     != 0 then
    where_statement                := 'WHERE ' || cols(1) || ' ' || rels(1) || ' ';
    if (rels(1)                     = 'in' or rels(1) = 'not in') then
      where_statement              := where_statement || trim(both '''' from vals(1));
    elsif (utils.is_number(vals(1)) = 'N' and substr( vals(1), 1, 8 ) != 'TO_DATE(') then
      where_statement              := where_statement || '''' || vals(1) || '''';
    else
      where_statement := where_statement || vals(1);
    end if;
    for i in 2..cols.last
    loop
      where_statement                 := where_statement || ' AND ' || cols(i) || ' ' || rels(i) || ' ';
      if (rels(i)                      = 'in' or rels(i) = 'not in') then
        where_statement               := where_statement || trim(both '''' from vals(i));
      elsif ( utils.is_number(vals(i)) = 'N' and substr( vals(i), 1, 8 ) != 'TO_DATE(') then
        where_statement               := where_statement || '''' || vals(i) || '''';
      else
        where_statement := where_statement || vals(i);
      end if;
    end loop;
  end if;
  open cur for 'SELECT * FROM ' || tabname || ' ' || where_statement;
  return cur;
end get_cursor;

Its doesn't matter if it works correctly, it return something and i must write it to the file in procedure, which will take the same arguments which get_cursor takes + path and file name:

procedure txt_export(
    tabname   in varchar2,
    cols      in array_t,
    vals      in array_t,
    rels      in array_t,
    path      in varchar2,
    file_name in varchar2)
is
  l_file utl_file.file_type;
  tmp_file_name varchar2(4000) := file_name;
begin
  if (tmp_file_name like '%.txt') then
    l_file := utl_file.fopen(path, tmp_file_name, 'w');
  elsif (tmp_file_name not like '%.txt') then
    tmp_file_name := tmp_file_name || '.txt';
    l_file        := utl_file.fopen(path, tmp_file_name, 'w');
  end if;

  /*
  run function get_cursor(tabname, cols, vals, rels) and write result to the file .txt
  */

  utl_file.fclose(l_file);
end;

Please help me with that problem. And sorry for my english :)

regards!

Answer 1

The tricky thing is that the function returns a weak ref cursor; you can't use a strong ref cursor, because the function assembles the query's projection dynamically. So there is no way for the calling program to know the structure of the result set. This meacns you will need to use Dynamic SQL to interrogate the result set and figure out the metadata.

Fortunately you are using Oracle 11g, as Oracle introduced support for Method 4 dynamic SQL, which allows us to convert Ref Cursors into DBMS_SQL cursors. Find out more.

Given that you already know how to open and close files with UTL_FILE, I presume you know how to write out the columns with UTL_FILE.PUT() and UTL_FILE.NEW_LINE().