UTL_FILE --- Spooling to a file

[FLA]

Hello all,

I am new to using the 'UTL_FILE' package. Within an Oracle Procedure,
I am trying to spool some records from a database into a text file.
Here is what I have been trying to do:

SPOOL I:Developers\APPDEV\COREN\COA_RENEWALS\COARENEW2004\INVOICES.TXT
SELECT SPOOL_DATA FROM COA_SPOOL ORDER BY SPOOL_ID
SPOOL OFF

I know that you can't use the word SPOOL in a Oracle procedure.

Can someone please show me how to use the UTL_FILE package to
accomplish this?

Any help greatly appreciated.

Thanks

[Michael Moore]

I am fairly sure that if you google UTL_FILE that you can find many very good examples.
Mike

[FLA]

I have done that but none have showed how this works with a select
statement from a database pulling the results. The examples online
have been way too over the top for what I need. Mine is very basic.

> > Thanks- Hide quoted text -
>
> - Show quoted text -

[ora...@msn.com]

> > - Show quoted text -- Hide quoted text -

>
> - Show quoted text -

UTL_FILE works very similarly to DBMS_OUTPUT, and, as such, requires
you put your select into a cursor and then output the returned
values. As you must open a file handle before invoking the various
UTL_FILE PUT procedures it's not 'spooling' anything, really. Here is
an example using UTL_FILE to use query results:

SQL> declare
2 --
3 -- Set up a cursor to fetch data
4 --
5 cursor get_emp_info is
6 select empno, ename, job, hiredate, sal
7 from emp
8 order by empno;
9
10 --
11 -- File handle
12 --
13 outfile utl_file.file_type;
14
15 --
16 -- Directory on server where file will reside
17 --
18 dir varchar2(200):='ADMIN_DAT_DIR';
19
20 begin
21 --
22 -- Open the desired file for writing
23 --
24 -- Return the file handle so we can use it
25 --
26 outfile := utl_file.fopen(dir, 'mytestfile.lst', 'W');
27
28 --
29 -- Open the cursor and fetch the records
30 --
31 -- Output the data to our file
32 --
33 for emprec in get_emp_info
34 loop
35 utl_file.put_line(outfile, emprec.empno||' '||
emprec.ename||' '||emprec.job||' '||emprec.hiredate||' '||emprec.sal);
36 end loop;
37
38 --
39 -- Flush the output buffer
40 --
41 utl_file.fflush(outfile);
42 --
43 -- Close the file
44 --
45 utl_file.fclose(outfile);
46
47 end;
48 /

PL/SQL procedure successfully completed.

SQL>

And the generated output:

7369 SMITH CLERK 17-DEC-80 800
7499 ALLEN SALESMAN 20-FEB-81 1600
7521 WARD SALESMAN 22-FEB-81 1250
7566 JONES MANAGER 02-APR-81 2975
7654 MARTIN SALESMAN 28-SEP-81 1250
7698 BLAKE MANAGER 01-MAY-81 2850
7782 CLARK MANAGER 09-JUN-81 2450
7788 SCOTT ANALYST 09-DEC-82 3000
7839 KING PRESIDENT 17-NOV-81 5000
7844 TURNER SALESMAN 08-SEP-81 1500
7876 ADAMS CLERK 12-JAN-83 1100
7900 JAMES CLERK 03-DEC-81 950
7902 FORD ANALYST 03-DEC-81 3000
7934 MILLER CLERK 23-JAN-82 1300

I hope this helps.


David Fitzjarrell

[FLA]

That worked awesome. Thank you!

> David Fitzjarrell- Hide quoted text -

[rajshekhar gadepalli]

Hi,

 

You can use UTL_FILe.fopen procedure to open the file in which u want to write the text.

And after u finish writing the text , use utl_file.fclose procedure. this will close the file .

Pass the path of the file in the utl_file.fopen procedure as arguments and same with utl_file.fclose procedure