Writing result sets to file ..... how?

[jballum]

Does anybody have any suggestions on what method could be used inside a stored procedure to do the following:

Given a select statement (unknown) run it outputting the results to a given file location.

Any ideas?

Thanx

JB

 

[Swany]

You need to use the UTL_FILE package that comes with Oracle.

http://technet.oracle.com/doc/server.804/a58241/ch8.htm

 

[jballum]

Swany,

Thanks for responding so promptly but I don't think this will work as I am running a totally unknown SQL statement so I would not be able to cursor through the results to output to file.

Unless someone knows a trick or technique for cursoring through a unknown dynamic SQL statement.

Thanx

JB

 

[Swany]

This is going to sound seriously weird but you could take everything before the FROM in the select statement and convert it into a delimeter seperated list before you output it, then your cursor would return only one column no matter what was selected from it.

select a, b, c
from table

would become

select a ||','||b||','||c
from table

You will need to code it to ingore commas that are between parenthesis so that arguments to functions would not be affected.

This should do the trick for you...

--select statement passed in as v_select
FUNCTION flatten_SQL(v_select varchar2) return varchar2
IS
  cur_pos number;
  from_pos number;
  strlen number;
  select_clause varchar2(2000);
  remaining_sql varchar2(2000);
  new_sql varchar2(4000);
  cur_char varchar2(10);
  depth number := 0;
BEGIN
  from_pos := instr(v_select,' FROM ');
  select_clause := substr(v_select, 1, from_pos-1);
  remaining_sql := substr(v_select, from_pos, length(v_select) - from_pos +1);

  for cur_pos in 1 .. length(select_clause)
  LOOP
    cur_char := substr(select_clause, cur_pos, 1);

    if cur_char = '(' 
    THEN 
       depth := depth + 1;
    end if;

    if cur_char = ')'
    then 
       depth := depth - 1;
    end if;

    if depth = 0 then
      if cur_char = ',' 
      then 
        cur_char := '||'',''||';
      end if;
    end if;

    new_sql := new_sql || cur_char;

  END LOOP; 
  --complete SQL statement 
  return new_sql || remaining_sql;
  
END;

 

[Swany]

As an added suggestion

add a parameter to flatten_sql that specified the delimiter. That way you can make it space or tab or comma or pipe or anything else you want.

Also you will need to ignore commas that are in '' so you need to say if char = '''' or char = '(' then depth....

I hope that helps. I know it seems like a strange concept, but it should handle what you need to do quite well.

If you are using 8i, you could probably write a JAVA stored procedure to walk the recordset collection resulting from opening up your SQL statement and write the contents out that way.