Re: PL/SQL Help (UTL_FILE)

jayanes_at_hotmail.com (Jay) wrote

> Suppose I have cursor called EMP (multiple fields) and record is
> defined as
> EMP_REC EMP%ROWTYPE;
>
> Is it possible to use UTL_FILE.PUT_LINE(file1, EMP_REC) ?
<snipped>

The default PUT_LINE procedure cannot accept a row as input. But it is possible to implement an Object layer (User Defined Types or UDT) in Oracle that allows you this. In fact, it can be done procedurally too, but an Object implementation will be many times cleaner and more flexible.

The basics of it is using DBMS_SQL. You create a cursor using DBMS_SQL (dynamic SQL). You use the DBMS_SQL.describe_columns procedure to obtain a list of columns. You fetch a row from the cursor. You loop through the list of columns, extract a column value and writing it to file. Repeat until end of cursor.

I have written a TCursor UDT that wraps DBMS_SQL into an object class. Using this to do what you want to do, I would code the following (ignore for the moment fixed white spaces as I'm simulating UTL_FILE using DBMS_OUTPUT that removes white spaces):

--
SQL> set serveroutput on
SQL> alter session set nls_date_format='dd-MON-yyyy hh24:mi:ss';

Session altered.

SQL> declare
  2          c$ TCursor;
  3          i$ integer;
  4  begin
  5          c$ := TCursor('SELECT * FROM emp' );
  6
  7          i$ := c$.Execute;
  8
  9          while ( c$.Next != 0 )
 10          loop
 11                  for i$ in 1..c$.Field.count
 12                  loop
 13                          dbms_output.put(
c$.ColumnAsString(i$)||','  );  -- pretend this is a UTL_FILE call
 14                  end loop;
 15                  dbms_output.put_line( ' ' );
 16          end loop;
 17
 18          c$.Close;
 19  end;
 20  /
7369,SMITH,CLERK,7902,17-DEC-1980 00:00:00,800,,20,
7499,ALLEN,SALESMAN,7698,20-FEB-1981 00:00:00,1600,300,30,
7521,WARD,SALESMAN,7698,22-FEB-1981 00:00:00,1250,500,30,
7566,JONES,MANAGER,7839,02-APR-1981 00:00:00,2975,,20,
7654,MARTIN,SALESMAN,7698,28-SEP-1981 00:00:00,1250,1400,30,
7698,BLAKE,MANAGER,7839,01-MAY-1981 00:00:00,2850,,30,
7782,CLARK,MANAGER,7839,09-JUN-1981 00:00:00,2450,,10,
7788,SCOTT,ANALYST,7566,19-APR-1987 00:00:00,3000,,20,
7839,KING,PRESIDENT,,17-NOV-1981 00:00:00,5000,,10,
7844,TURNER,SALESMAN,7698,08-SEP-1981 00:00:00,1500,0,30,
7876,ADAMS,CLERK,7788,23-MAY-1987 00:00:00,1100,,20,
7900,JAMES,CLERK,7698,03-DEC-1981 00:00:00,950,,30,
7902,FORD,ANALYST,7566,03-DEC-1981 00:00:00,3000,,20,
7934,MILLER,CLERK,7782,23-JAN-1982 00:00:00,1300,,10,

PL/SQL procedure successfully completed.

SQL>

The "trick" is simply making the number of columns to write, variable
- i.e. no hardcoding. Which is exactly what  DBMS_SQL.describe_columns
does by returning you a list of columns in the query.

The above code is still dirty though. Spooling the data in something
like CSV format to file should also be a class and it should do the
UTL_FILE processing for you. All you give it is the SQL statement,
directory object and filename to use - it does the rest for you.
Something like:
declare
  file$ TCSVFile;
begin
  file$ := TCSVFile( 'MY_DIR', 'EMP.CSV', 'SELECT * FROM emp' );
  file$.ColumnSeparator := '|';
  file.ColumnHeadings := FALSE;
  file.FixedColumnlength := FALSE;
  .. setting other file format properties ..
  file$.CreateFile;   -- creating the file
end;

I prefer to approach a problem like this by creating the tools (aka
building blocks) I need in the application. Once that tool is there,
it can be re-used over and over again by various apps, and improved
without having to change application code.

Never hardcode. Okay.. almost never. :-)

--
Billy