Re: PL/SQL Help (UTL_FILE)

THANKS A LOT EVERYONE! It did help me!

J

vslabs_at_onwe.co.za (Billy Verreynne) wrote in message news:<1a75df45.0403180004.4f6585bb_at_posting.google.com>...
> 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. :-)
Received on Thu Mar 18 2004 - 08:40:29 CST