Skip to Main Content
  • Questions
  • Can UTL_FILE package used to create ASCII files

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, V Siva.

Asked: May 22, 2000 - 2:09 pm UTC

Last updated: September 19, 2017 - 1:49 am UTC

Version: 7.3.2.3.0

Viewed 10K+ times! This question is

You Asked

Hi tom

Earlier I send question How to extract data from tables to text files. In this connecton, I have another question. Can I use UTL_FILE package to create ASCII files.

Thanks
Siva

and we said...

Absolutely - in fact UTL_FILE can only create ascii (as opposed to binary) text files. Here is an example of a procedure that takes most any query and will dump it to a delimited file of your choice:

ops$tkyte@8i> set echo on
ops$tkyte@8i> set serveroutput on
ops$tkyte@8i>
ops$tkyte@8i> create or replace
2 function dump_csv( p_query in varchar2,
3 p_separator in varchar2 default ',',
4 p_dir in varchar2 ,
5 p_filename in varchar2 )
6 return number
7 is
8 l_output utl_file.file_type;
9 l_theCursor integer default dbms_sql.open_cursor;
10 l_columnValue varchar2(2000);
11 l_status integer;
12 l_colCnt number default 0;
13 l_separator varchar2(10) default '';
14 l_cnt number default 0;
15 begin
16 l_output := utl_file.fopen( p_dir, p_filename, 'w' );
17
18 dbms_sql.parse( l_theCursor, p_query,
19 dbms_sql.native );
20
21 for i in 1 .. 255 loop
22 begin
23 dbms_sql.define_column( l_theCursor, i,
24 l_columnValue, 2000 );
25 l_colCnt := i;
26 exception
27 when others then
28 if ( sqlcode = -1007 ) then exit;
29 else
30 raise;
31 end if;
32 end;
33 end loop;
34
35 dbms_sql.define_column( l_theCursor, 1,
36 l_columnValue, 2000 );
37
38 l_status := dbms_sql.execute(l_theCursor);
39
40 loop
41 exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
42 l_separator := '';
43 for i in 1 .. l_colCnt loop
44 dbms_sql.column_value( l_theCursor, i,
45 l_columnValue );
46 utl_file.put( l_output,
47 l_separator || l_columnValue );
48 l_separator := p_separator;
49 end loop;
50 utl_file.new_line( l_output );
51 l_cnt := l_cnt+1;
52 end loop;
53 dbms_sql.close_cursor(l_theCursor);
54
55 utl_file.fclose( l_output );
56 return l_cnt;
57 end dump_csv;
58 /

Function created.

ops$tkyte@8i>
ops$tkyte@8i> declare
2 l_rows number;
3 begin
4 l_rows := dump_csv( 'select *
5 from all_users
6 where rownum < 5',
7 ',',
8 '/tmp',
9 'test.dat' );
10 dbms_output.put_line( to_char(l_rows) ||
11 ' rows extracted to ascii file' );
12 end;
13 /
4 rows extracted to ascii file

PL/SQL procedure successfully completed.

ops$tkyte@8i>
ops$tkyte@8i> host cat /tmp/test.dat
SYS,0,20-APR-99
SYSTEM,5,20-APR-99
OUTLN,11,20-APR-99
DBSNMP,18,20-APR-99


Rating

  (183 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Thanks

A reader, June 04, 2001 - 10:56 am UTC


CSV Dump

Jessica Wilson, July 12, 2001 - 1:46 pm UTC

I was able to modify the code in a short period of time to what my company needed done.



CSV Dump

Jessica Wilson, July 12, 2001 - 1:47 pm UTC

I was able to modify the code in a short period of time to accomplish what my company needed done.



function dump_csv

Charles Bankes, February 05, 2002 - 9:35 am UTC

Superb solution

Is it possible using native dynamic sql though?

Tom Kyte
February 05, 2002 - 11:51 am UTC

no, you need to know at compile time how many / what type of outputs you are getting from NDS with plsql. You need the procedural access afforded by dbms_sql.

what would NDS buy you here?

Excellent Procedure

Prakash, April 01, 2002 - 1:37 am UTC

The solution is terrific. Can we have generic procedure to print output in fixed length format.

A reader, April 02, 2002 - 10:36 am UTC

is there any way to create directory with utl_file

Tom Kyte
April 02, 2002 - 10:42 am UTC

No, there is not.

You would typically use a java stored procedure to perform that operation.

The "bad" news is that even if utl_file could create a directory -- it would require a database RESTART in order to utl_file to read or write files into it.

UTL_FILE package used to create ASCII files- function dump_csv

vivek shenoy, April 23, 2002 - 2:09 pm UTC

Thanks Tom for the great function for dumping file in
ascii format.

i require little help in the matter. i could dump my file
in ascii in the Server (we are using UNIX platform) from
the clint side. But i would like to dump the ascii file
at the client side. how to do it.

Tom Kyte
April 23, 2002 - 2:38 pm UTC

Success!!

jimmy, June 27, 2002 - 6:55 am UTC

I did this successfully..
The UTL_file_dir wasnt setup properly.
That was the problem.
I need to know some more information.
I want to retrieve data from database on some criteria and will be concatinating it to a single string and have to move LOB file and from the LOB i should move to a .csv file.
How should I accomplish this. Can you provide the sample.
Thanx a lot!!

Tom Kyte
June 27, 2002 - 8:34 am UTC

You can use dbms_lob.substr to pick off pieces of your lob and utl_file to write these pieces.

Bfile

Jimmy, June 27, 2002 - 10:16 pm UTC

Does the BFILE is a read-only??
Then how to create a BFILE where it is coming from??
How it is generated??

Tom Kyte
June 28, 2002 - 6:49 am UTC

You put a file on the server.

You create a bfile object (which is nothing more then a directory and a filename).

How does the file get to the server? Anyway you would like to get it there -- that is outside of our control.

UTL_FILE buffer

jimmy, June 27, 2002 - 10:24 pm UTC

I have a query regarding the buffer size of UTL_FILE.
This has a limitation of 32787.
I am reading a value thru cursor and PUTing into the UTL_FILE record-by-record (cursor facility).
Once the record is PUT using UTL_FILE.PUT command. After this command will be buffer will be flushed automatically or do I need to do it explicitly?

Tom Kyte
June 28, 2002 - 6:50 am UTC

It'll be flushed with the internal IO buffer fills, or you call utl_file.fflush, or you close the file.

The exact behavior of FFLUSH

Andrew, July 30, 2002 - 2:58 pm UTC

Tom
/*
** FFLUSH - Force physical write of buffered output
**
** IN
** file - File handle (open in write/append mode)
** EXCEPTIONS
** invalid_filehandle - not a valid file handle
** invalid_operation - file is not open for writing/appending
** write_error - OS error occured during write operation
*/

I have a situation where I use utl_file to write a file and immediately after that I call a Java stored procedure to access the file.

My question is - when I FFLUSH (and then close) a file, is it simply written to the OS buffer - or is it physically flushed to disk by the OS too. I want to ensure that the file is visible to the Java stored proc. I want to avoid having to include a sleep() to wait for the file to become ready. The OS is Solaris.

Tom Kyte
July 30, 2002 - 5:04 pm UTC

You need not fflush if you are going to close.

As soon as you close the file, Java can "see it" -- definitely. It is most likely buffered by the OS in some way but that is beyond our control.

Close it and you can see it.

Handling the Tab separated data in a FIle

Siva Kumar, October 28, 2002 - 12:34 am UTC

Hi Sir,

I have a problem like in a file I will the header and contect , first line is the header and from the second line data will be there . The data will be in tab separated data.
Please let me know How do I read the header which contains some column names to load the data in to the database , based on the header how can I handle the tab separated data using UTL_FILE and load into the database . Or any other way to do it .... Please it is very critical for me and very urgent . please give me some tips to handle and if you any example of code to handle it let me know .......

I will be grateful if you help me on this regard

Thanks
Siva Kumar




Tom Kyte
October 28, 2002 - 7:22 am UTC

If you know how to write procedural code, implement algorithms and logic -- and you know PLSQL, this is something you need to do. I mean, come on -- you have an example that loads delimited data above, a 100% implementation, and you cannot modify it to fit your needs????

If you do not know how to write procedural code, implement algorithms and logic, you need to hire someone that does to help you out.

how to handle tab separated data using UTL_FILE

Siva Kumar, October 28, 2002 - 11:56 pm UTC

Hi Sir,

I have a problem like in a file how to read the tab separated data using UTL_FILE package.

Thanks
Siva Kumar




Tom Kyte
October 29, 2002 - 6:30 am UTC

read
</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:464420312302 <code>
use chr(9) -- the tab character -- as the delimiter.

dump_csv

Paige, October 30, 2002 - 7:16 pm UTC

I've been using the dump_csv function to extract a temp table from my database for use as a text file for a word merge. It's great. However, I have one small problem, MS Word keeps using the first row of data as the header row. Is there an easy way to modify the dump_csv function so that the text file is created with the temp table column names as a header row in the text file?

thanks:)

Tom Kyte
October 31, 2002 - 8:50 pm UTC

You got lucky- i already made this modification for something else a while ago:


ops$tkyte@ORA920.US.ORACLE.COM> create or replace procedure dump_table_to_csv( p_tname in varchar2,
  2                                                 p_dir   in varchar2,
  3                                                 p_filename in varchar2 )
  4  authid current_user  /* <<<== if you want... runs as "invoker" and runs with ROLES */
  5  is
  6      l_output        utl_file.file_type;
  7      l_theCursor     integer default dbms_sql.open_cursor;
  8      l_columnValue   varchar2(4000);
  9      l_status        integer;
 10      l_query         varchar2(1000)
 11                      default 'select * from ' || p_tname;
 12      l_colCnt        number := 0;
 13      l_separator     varchar2(1);
 14      l_descTbl       dbms_sql.desc_tab;
 15  begin
 16      l_output := utl_file.fopen( p_dir, p_filename, 'w' );
 17      execute immediate
 18      'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';
 19
 20      dbms_sql.parse(  l_theCursor,  l_query, dbms_sql.native );
 21      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
 22
 23      for i in 1 .. l_colCnt loop
 24          utl_file.put( l_output,
 25              l_separator || '"' || l_descTbl(i).col_name || '"' );
 26          dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
 27          l_separator := ',';
 28      end loop;
 29      utl_file.new_line( l_output );
 30
 31      l_status := dbms_sql.execute(l_theCursor);
 32
 33      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
 34          l_separator := '';
 35          for i in 1 .. l_colCnt loop
 36              dbms_sql.column_value( l_theCursor, i, l_columnValue );
 37              utl_file.put( l_output, l_separator || l_columnValue );
 38              l_separator := ',';
 39          end loop;
 40          utl_file.new_line( l_output );
 41      end loop;
 42      dbms_sql.close_cursor(l_theCursor);
 43      utl_file.fclose( l_output );
 44
 45      execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 46  exception
 47      when others then
 48          execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
 49          raise;
 50  end;
 51  /

Procedure created.

ops$tkyte@ORA920.US.ORACLE.COM> exec dump_table_to_csv( 'SCOTT.EMP', '/tmp', 'tkyte.emp' );

PL/SQL procedure successfully completed.

ops$tkyte@ORA920.US.ORACLE.COM> host cat /tmp/tkyte.emp
"EMPNO","ENAME","JOB","MGR","HIREDATE","SAL","COMM","DEPTNO"
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

ops$tkyte@ORA920.US.ORACLE.COM> 

File Handle is assignable ?

Robert, November 11, 2002 - 4:10 pm UTC

Tom, File handles are totally assignable, right ?
e.g:

l_Read UTL_FILE.FILE_TYPE ;
l_OutSouth UTL_FILE.FILE_TYPE ;
l_OutNorth UTL_FILE.FILE_TYPE ;
l_OutTemp UTL_FILE.FILE_TYPE ;
l_line varchar2(1000);

BEGIN
l_Read :=
UTL_FILE.FOPEN('/user/home', 'usa.txt', 'R');

l_OutSouth :=
UTL_FILE.FOPEN('/user/home', 'south.txt', 'W');

l_OutNorth :=
UTL_FILE.FOPEN('/user/home', 'north.txt', 'W');

-- Loop thru and read each line
.....snip
IF SUBSTR(l_line, 10, 2) := 'VA' THEN
l_OutTemp := l_OutSouth ;
ELSIF SUBSTR(l_line, 10, 2) := 'MA' THEN
l_OutTemp := l_OutNorth ;
......snip

END IF;

UTL_FILE.PUT_LINE (l_OutTemp, l_line);
.......

END;

I want to verify with you that the file handle assignment
code:

l_OutTemp := l_OutSouth ;
l_OutTemp := l_OutNorth ;

are totally legal, and that file handle can be treated just like any assignable/passable variable.

It's just I don't think I ever read about this specific action being OK.
I tested this and it seems to work as I expected.

Thanks

Tom Kyte
November 11, 2002 - 6:34 pm UTC

yes, they are just record structures -- they can be assigned.

UTL_FILE package

Awesh Ahmad, November 18, 2002 - 6:20 pm UTC

Hi Tom,
I used UTL_FILE package example given by you and modify the code very easily as per my requirement. Thanks for your valuable information. But I have one problem, In my most of the tables few fields are notes around varchar2(2000) and user had pressed ENTER key "carriage return-line feed" and because of this rest of the fields of record started in new lines. For e.g below user entered "Test for data before any stock splits." and then press enter and added "This is test for pressing the "Enter" key.,". Output of .dat file is

110269,,Test - Jann 3.0,1,01-JAN-97,2,Test for data before any stock splits.

This is test for pressing the "Enter" key.,

Tom Kyte
November 18, 2002 - 8:55 pm UTC

Uh huh - and what would you like to have happen here?

You can use translate(l_output , chr(13)||chr(10), 'XY' )

to have carriage returns (chr(13)) turned into X (or whatever of course) and linefeeds (newlines) turned into Y (or whatever)

UTL_FILE

Awesh Ahmad, November 19, 2002 - 5:00 pm UTC

Thanks Tom,

I did change and working fine. But I have one more problem.My table size is very big having >50 columns and one is notes field of varchar2(4000) and few of them are varchar2 50-100 size. It's working for records those have few characters in notes field but failing if notes field is bigger. is there any limit of file size. Could you please help how to solve this issue. I am getting following error
ORA-06510: PL/SQL: unhandled USER-defined EXCEPTION
ORA-06512: AT "SYS.UTL_FILE", line 100
ORA-06512: AT "SYS.UTL_FILE", line 222
ORA-06512: AT "EPROSPER.DUMP_TABLE_TO_CSV", line 46
ORA-06510: PL/SQL: unhandled USER-defined EXCEPTION
ORA-06512: AT line 2
Thnaks


Tom Kyte
November 19, 2002 - 5:33 pm UTC

16 l_output := utl_file.fopen( p_dir, p_filename, 'w', 32000 );
17

your output line is probably exceeding 1022 bytes -- the default max width. change the open call.

Very Helpful

Mike, November 21, 2002 - 1:19 pm UTC

Tom:

This and other threads on utl_file have been very help to me. Thank you.

However, I still have a high-level question regarding concurrency and the use of utl_file. My assumption is that the file opened by ult_file is only meant for a single writer. If my assumption is correct then should I worry about below local code fragment using only USER to uniquely identify the file:

-- Open file using newer fopen() to get around 1KB max. line size.
m_sFileName := ‘cool_tool_’ || lower( user ) || ‘.txt’;
m_hFile := utl_file.fopen( m_sFileDir, m_sFileName, m_sFileOpenMode, 32767 );

If user JOE is running multiple sessions then obviously the file name: “cool_app_joe.txt is non-unique. Will utl_file attempt multiple concurrent opens/writes on the same file? Are we using a poor file naming practice or is it OK (i.e. maybe ult_file.putf waits for the blocking transaction to close the file)?

I’m curious about your opinion on this.

Thanks for your time,

-Mike


Tom Kyte
November 21, 2002 - 1:35 pm UTC

yes it will (attempt and on many platforms succeed) in doing multiple file opens on that same file.

If each SESSION is to have a unqiue file, use

'cool_tool_' || userenv('sessionid') || '.txt'

instead.

Thanks again

MIke, November 25, 2002 - 11:07 am UTC

Tom:

> yes it will (attempt and on many platforms succeed)
> in doing multiple file opens on that same file.

Slick! Another great Oracle feature.

I'm curious if you think it's a valid PL/SQL design direction (at our site) to use UTL_FILE without worrying about the implimentation details of multiple writers or multiple sessions opening the same file? Delegate that to ULT_FILE and cease to worry about unique file names/session?

Thanks,

-Mike

Tom Kyte
November 25, 2002 - 11:24 am UTC

actually -- this:

> yes it will (attempt and on many platforms succeed)
> in doing multiple file opens on that same file.

is a bad thing -- happens all of the time on unix unless you explicitly use file locking. *its a bad thing*


You cannot delegate that to utl_file, sorry if my other answer wasn't clear. You'll end up with garbage in that file -- one session will see another sessions stuff. It'll be nasty. Thats why I recommend:

If each SESSION is to have a unqiue file, use

'cool_tool_' || userenv('sessionid') || '.txt'

instead.

to AVOID that.



Thanks

Mike, November 25, 2002 - 5:37 pm UTC

Tom: this is crystal clear now. Thank you. -Mike

utl_File

mo, January 06, 2003 - 12:06 pm UTC

Tom:

I have a requirement to convert several C programs that were used with informix to create query and create fixed width data on unix to ones that work with oracle.

Now, I am not familiar with C but looking at your above solution it looks to me that it is much easier for me to take the queries from C program and plug them into the procedure and get my output files.

Do you agree or there is something I am missing?

Thank you,

Tom Kyte
January 06, 2003 - 12:25 pm UTC

an informix esql c program should run almost unchanged (or at least the changes will be the same time after time).

C is faster.

If you want a generic C program that dumps data -- you would have to modify it -- it would be:

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:459020243348 <code>

it dumps delimited data -- simply mods you can make to have it be fixed width.

utl_file

mo, January 06, 2003 - 12:30 pm UTC

Tom:

OK but Can I still take the query from the C program, plug it into the above procedure using utl_file and create an ASCII text file in unix for the query results?

Tom Kyte
January 06, 2003 - 1:08 pm UTC

Yes, of course -- think about it -- it is just a stored procedure to dump a query.

I'm afraid however people will look at it and say "oh, informix was faster" cause that c program will be much faster.

utl file

mo, January 06, 2003 - 2:08 pm UTC

TOm:

We are only talking about a database with hundreds of records. They should not notice anything. PL/sql is more flexible and easier in my opinion.

Is C faster bcause it is already compiled to binary and you just run it, rather than pl/sql which is interpreted language and you always have to compile/run?

Tom Kyte
January 06, 2003 - 3:23 pm UTC

C is faster because in this case, it is C and the major time consumption spent on dumping to flat files is in the writing of the data -- something C is slightly more efficient than PLSQL at doing.


fixed width text

mo, January 06, 2003 - 10:53 pm UTC

Tom:

Your example show comma-delimited output. If I want to create fixed width data output would I just take the ',' from the p_separator.

THanks

Tom Kyte
January 07, 2003 - 6:21 am UTC

umm, no. that would not do it unless you passed it a query like:


select rpad( decode( c1, null, ' ', c1 ), 20 ) ||
rpad( decode( c2, null, ' ', c2 ), 15 ) ||
.......
from t;


then the routine will work "as is" unchanged. If you just want to pass it:

select * from t;

and have it figure out how wide each field should be -- you'll be writing some more code.

spool file

mo, January 07, 2003 - 11:06 am UTC

Tom:

Can't you get the same result by creating SQL*plus script where you simply spool output to a file and then run the query.

To run both ways (utl_file or sqlplus script) from unix prompt you have to use sqlplus anyway?

Thanks

Tom Kyte
January 08, 2003 - 2:22 pm UTC

see
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

I have solutions using sqlplus, plsql, C. I'm an equal opportunity tools user.

Can UTL_FILE package used to create ASCII files

Ramesh Golkonda, January 08, 2003 - 11:48 am UTC

I thought writing to a text file using UTL_FILE package is much much slower than Java Print statement because each "UTL_FILE.write" has to go to operating system and append the file. Tom could shed some light if iam wrong.

Thanks
RameshG

Esql prgram

mo, January 08, 2003 - 2:55 pm UTC

Tom:

I found that the C/informix program had fours cursors and more logic to create each record in the final output. There is a lot of literal insertions based on values of each record.

It seems to me now that utl_file is not the right tool since it is only expecting one sql query. Am I wrong?

2. You said that the C code should work with oracle. Would I test that out by running the DLL file from the unix prompt? DO I need to read your chapter on External Procedure s to figure out how to convert ESQL to PROC or PLSQL?

3. Can you point me to any reference material/books on conversion of esql to proc or plsql.

Thank you,

Tom Kyte
January 08, 2003 - 4:43 pm UTC

1) i don't know -- use your power of analysis to determine the right path. It is our (your) job. This is just really an exercise in programming after all.

2) You would read the pro*c documentation and port it to pro*c. it would not be an external routine, it is a command line, standalone program

3) they (pro*c and esql-c) are basically the same. See otn.oracle.com, search for esql

porting to plsql is known as "rewrite"

utl_file

mo, January 14, 2003 - 4:34 pm UTC

Tom:

I am testing utl_file trying to write to a file in windows and I get an erros. I also got it in unix. Any ideas?


1 declare
2 l_rows number;
3 begin
4 l_rows := dump_csv( 'select *
5 from all_users
6 where rownum < 5',
7 ',',
8 'c:\tmp',
9 'test.dat' );
10 dbms_output.put_line( to_char(l_rows) ||
11 ' rows extracted to ascii file' );
12* end;
13 /
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "IMSADMIN.DUMP_CSV", line 15
ORA-06512: at line 4



Tom Kyte
January 14, 2003 - 8:30 pm UTC

and so, what is your utl_file_dir init.ora set to?

show parameter utl_file_dir


what does that return in plus/svrmgrl?

utl_file

mo, January 14, 2003 - 7:02 pm UTC

Tom:

I added exceptions to check where the error is coming from and here are the results.  I did change the parameter utl_file_dir.

SQL> exec test_utl;
BEGIN test_utl; END;

*
ERROR at line 1:
ORA-20003: INVALID_OPERATION: The file could not be opened or
operated on as requested.
ORA-06512: at "IMSADMIN.TEST_UTL", line 19
ORA-06512: at line 1
                               

Tom Kyte
January 14, 2003 - 8:42 pm UTC

so, what is your utl_file_dir

utl_file

mo, January 14, 2003 - 9:08 pm UTC

Tom:

I believe it is set to /tmp (not on server now).

but also I was trying to write to a local file on my pc. Would I be able to do this or I can not add a local directory to the parameter too?

Tom Kyte
January 15, 2003 - 8:03 am UTC

but you are writing to c:\tmp...

utl_file writes to the SERVER only -- the file system must be visible to the Oracle server process. Your local filesystem, probably not visible. You need a CLIENT tool to write CLIENT files.



utl_file

mo, January 15, 2003 - 11:00 am UTC

Tom:

Here is my parameter. IT still does not work.

SQL> show parameter utl_file_dir

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
utl_file_dir                         string  /ppcs
IMSD> execute test_utl_file;
BEGIN test_utl_file; END;

      *
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'TEST_UTL_FILE' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

PROCEDURE TEST_UTL as
l_rows   number;
begin
  l_rows  := dump_csv('select * from all_users where rownum <5',',','/ppcs','test.dat' );
dbms_output.put_line(to_char(l_rows) || 'rows extracted to ascii file' );
end;
 

Tom Kyte
January 15, 2003 - 11:15 am UTC

read the error message again -- ok....

procedure test_utl


identifier 'TEST_UTL_FILE' must ....

(slow down...)

utl_file

mo, January 15, 2003 - 11:22 am UTC

Tom:

sorry I copied the wrong text.

SQL> execute test_utl;
BEGIN test_utl; END;

*
ERROR at line 1:
ORA-20003: INVALID_OPERATION: The file could not be opened or
operated on as requested.
ORA-06512: at "IMSADMIN.TEST_UTL", line 19
ORA-06512: at line 1

 

Tom Kyte
January 15, 2003 - 11:36 am UTC

see the error - perhaps the oracle account does not have rwx on that directory or the file exists and oracle doesn't have rw- on it.


ops$tkyte@ORA817DEV> set echo on
ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> !rmdir /export/home/tkyte/tmp
rmdir: directory "/export/home/tkyte/tmp": Directory not empty

ops$tkyte@ORA817DEV> !mkdir /export/home/tkyte/tmp
mkdir: Failed to make directory "/export/home/tkyte/tmp"; File exists

ops$tkyte@ORA817DEV> !chmod a=,u=rwx /export/home/tkyte/tmp

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          l_output                utl_file.file_type;
  3  begin
  4          l_output := utl_file.fopen( '/export/home/tkyte/tmp', 'foo.txt', 'w', 32765 );
  5          utl_file.fclose( l_output );
  6  exception
  7          when utl_file.invalid_operation then
  8                  raise_application_error( -20003, 'lose' );
  9  end;
 10  /
declare
*
ERROR at line 1:
ORA-20003: lose
ORA-06512: at line 8


ops$tkyte@ORA817DEV> !chmod a=rwx /export/home/tkyte/tmp

ops$tkyte@ORA817DEV>
ops$tkyte@ORA817DEV> declare
  2          l_output                utl_file.file_type;
  3  begin
  4          l_output := utl_file.fopen( '/export/home/tkyte/tmp', 'foo.txt', 'w', 32765 );
  5          utl_file.fclose( l_output );
  6  end;
  7  /

PL/SQL procedure successfully completed.
 

utl_file

mo, January 15, 2003 - 11:38 am UTC

Tom:

I wonder if the above problem is related to access privileges to the /ppcs directory. When I telnet to it and create a text file it works. However I am logging in with a unix user account. From oracle I do not know how is oracle logging into unix or how unix checks for access privileges for that utl_file user?



utl_file

mo, January 15, 2003 - 11:56 am UTC

Tom:

IT works.

SQL> !chmod a=rwx /ppcs

SQL> execute test_utl

PL/SQL procedure successfully completed.

SQL> !cat test.dat
SYS,0,10/12/2001
SYSTEM,5,10/12/2001
OUTLN,11,10/12/2001
IMSADMIN,12,10/12/2001      


How is oracle connecting to unix. What account does it use. Is there a way to know oracle user privileges on unix.

Thanks a lot, 

What account does it use...

Mark A. Williams, January 15, 2003 - 12:14 pm UTC

Mo,

From the documentation for utl_file is this:

On UNIX systems, the owner of a file created by the FOPEN function is the owner of the shadow process running the instance. Normally, this owner is ORACLE.

So, the answer to the question "what account does it use?" is "the oracle software account." As Tom said two answers above. The 'ls' command will show "owner", "group", and "other" permissions for directories and files, so you can use it to surmise "oracle user privileges on unix"...

HTH,

Mark

NOrmally owner is oracle, but not always

paul, January 15, 2003 - 1:37 pm UTC

On Sequent/dynix, and I suppose other unix, if you connect via sqlnet the shadow process is owned by oracle. If your client runs on the database server you have the option of not using sqlnet, instead using what I vaguely remember as named pipes, in which case the shadow process is run as the owner of the client process. More or less the process involves setting ORACLE_SID, running ora_env, then connecting to oracle without using "@dbalias" in the connect string. I (vaguely again) recall that this process ownership behavior is optional, controlled somehow by an Oracle parameter. I prefer this mode especially in development, because it allows one to clean up one's own shadow processes as needed. Not that I ever need to, of course.

I haven't used utl_file, but perhaps this would cause files to be created as a user other than oracle.



Actually, always owned by oracle

A reader, January 15, 2003 - 3:40 pm UTC

Out of curiosity, did a small test, connected locally, shadow process owned by me (pkelley) not by oracle.  The utl file output file is still owned by oracle.   

onyx 212% whoami
pkelley
onyx 213% cora TS
onyx 214% sqlplus blah/blah

SQL*Plus: Release 8.1.7.0.0 - Production on Wed Jan 15 15:33:36 2003

(c) Copyright 2000 Oracle Corporation.  All rights reserved.


Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

SQL> !ps -ef | grep pkelley | egrep "sqlplus|oracle"  
  pkelley 15787 15176   1  15:33:36 ttyAF/AGFB  0:00 sqlplus blah/blah 
  pkelley 15788 15787   0  15:33:36 ?           0:00 oracleTS (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq))) 

SQL> set echo on
SQL> @utlfile
SQL> declare
  2  
  3      l_write utl_file.file_type;
  4  
  5  begin
  6  
  7   l_write := utl_file.fopen('/usr/local/logs','delete.me.pkelley','W');
  8  
  9   utl_file.put_line(l_write, 'delete.me - but first ,who owns the file');
 10  
 11   utl_file.fclose(l_write);
 12  end;
 13  /

PL/SQL procedure successfully completed.

SQL> 
SQL> 
SQL> !ls -ltr /usr/local/logs/delete.me.pkelley
-rw-rw-r--    1 oracle   oracle        41 Jan 15 15:34 /usr/local/logs/delete.me.pkelley

So, even though I owned the shadow process, oracle owned the output file.

 

Tom Kyte
January 16, 2003 - 8:00 am UTC

that is because the oracle binary will be "setuid" -- else it cannot attach the shared memory segment. The oracle binary runs with an effective uid of the Oracle software.

$ ls -l $ORACLE_HOME/bin/oracle

-rwsr-s--x 1 ora920 ora920 49501171 Nov 26 13:33 oracle


but how to write from unix to NT

Tong Siew Pui, February 04, 2003 - 5:08 am UTC

I want to UTL_FILE (from unix) and output an ascii text file to an NT machine directly, no ftp, no nothing, using nfs.
I already tried this, no pbm.
The file need to be viewed as any normal text file,
on a notepad, or opened in Excel, or any other application.

The pbm is, there is this special character at the end of each line (I think it is a ^M).
I know I can use unix2dos to change/modify the file to remove the special character, so that everything look OK on a notepad.

But, is there anyway that I can achive the same by doing it in one step, by using 'UTL_FILE' alone, without using "unix2dos" or any other additiional steps ??

The text output program is to be a monthly automatic process. I do not wish to call an external program to perform the stripping job.

thanks

Tom Kyte
February 04, 2003 - 7:59 am UTC



try

utl_file.put_line( your_text || chr(13) );

We'll put the linefeed (char(10)), bill gates long ago decided we needed two characters CARRIAGE_RETURN/LINE_FEED (chr(13)||chr(10)) to terminate a line. Add the chr(13) and they windows people may be apeased.

Is any work around to write a file to local system

Ashiq Shamsudeen, February 04, 2003 - 12:02 pm UTC

Hello Tom,

from ur quote

"utl_file writes to the SERVER only -- the file system must be visible to the Oracle server process. Your local filesystem, probably not visible. You need a
CLIENT tool to write CLIENT files. "

My requirement is to write a file to any systems in the network.Is it any work around way to accomplish this??Because FE(Front End) guys are requesting this option and they're saying if this is possible in SQL server .In sql server they're using BCP command ( i don't know how the way it works in SQL server and i doesn't want to know also).
The same functionallity they're asking in oracle.Is it possible thru PL/SQL or any other way??If yes give me example to get thru.
From ur quote u said Clent tools are required to do,what do u mean by Clent tool??

DB is Oracle8i(8.1.7) on NT.


Tom Kyte
February 04, 2003 - 12:57 pm UTC

besides a virus?

bcp is just a tool that runs on a client, it dumps data.

sqlplus is a tool we have that runs on a client. it can be used to accomplish the same feat of magic (which is NO MAGIC, they run bcp on a machine that can see the file system, they are not running bcp from a server and writing to any filesystem anywhere)

So, you need the client tools installed (just as you would with sqlserver to get bcp, a client tool). then just run sqlplus.

If you need a flat file (comma delimited) see:
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

has a sqlplus script to do that nicely.


Again writing a file to local system

Ashiq Shamsudeen, February 04, 2003 - 1:32 pm UTC

Hi Tom,

As u said they're not running bcp from server ,but what they're doing here is writing file to another machine.To be clear there're 3 nodes A, B , and C.They're running bcp in node A and writing the file to the folder of Node B.Of course directory where they going to write should be shared.
Here they run bcp from node A and write to the directory in node B(for eg \\b\output) they'll writeand again they change the direcoty to write the file from node B to C (for eg \\c\output)
Is this same functionallity possible in sqlloader.Where we can specify the directory to write.If it is not possible then how can we do achieve this in oracle??


Tom Kyte
February 04, 2003 - 1:59 pm UTC

that is using NETWORK DISKS.

if you can do \\b\output \\c\output -- you can write to it as well with anything.

that is just a unc filename, a NETWORK DISK -- like NFS

how not to use unix2dos

tong siew pui, February 04, 2003 - 10:24 pm UTC

thanks very much.
your solution works.

thanks.

Dynamically pass sql statement

A reader, April 03, 2003 - 11:47 am UTC

Tom,

I am trying to pass variable to dump_csv function, like that

v_sql:='select * from my_table where datecompleted between
:p_start_date and :p_end_date' using
p_start_date,p_end_date.

But it does not seem to be working...
So, is it possible to pass sql into dump_csv function, where
in sql you'd change dates dynamically?

Much Thanks


Tom Kyte
April 03, 2003 - 1:17 pm UTC

you could:

create context my_ctx using your_procedure;

and then in your_procedure code:

...

dbms_session.set_context
( 'my_ctx', start_date, to_char( p_start_date, 'dd-mon-yyyy' ) );
dbms_session.set_context
( 'my_ctx', end_date, to_char( p_end_date, 'dd-mon-yyyy' ) );


v_sql := 'select *
from my_table
where datecompleted between
to_date( sys_context(''my_ctx'',''start_date''), ''dd-mon-yyyy'' )
and
to_date( sys_context(''my_ctx'',''end_date''), ''dd-mon-yyyy'' )';

x := dump_csv( v_sql, ....


OR
you can code:


execute immediate 'alter session set cursor_sharing=force';

v_sql := 'select *
from my_table
where datecompleted between
to_date( ''' || to_char( p_start_date, 'dd-mon-yyyy' ) || '
, ''dd-mon-yyyy'' )
and
to_date( ''' || to_char( p_end_date, 'dd-mon-yyyy' ) || '
, ''dd-mon-yyyy'' );

dump_csv( v_sql ....

execute immediate 'alter session set cursor_sharing=exact';

I'm partial to the first one with sys_context, it is what I use on this site in fact.

A reader, April 03, 2003 - 3:34 pm UTC

Tom,

I am getting following message:
15:31:57 Execution failed: ORA-00907: missing right parenthesis

I can not find where in following query:

v_sql:='SELECT
''Actual'' as SCENARIO,
''Data Load'' as VERSION,
A.YEAR,
.....

and dtd.DTD_FULL_DATE BETWEEN to_date( to_char( ''' || p_start_date || ', ''dd-mon-yyyy'' ) , ''dd-mon-yyyy'' )
and to_date( to_char( ''' || p_end_date || ', ''dd-mon-yyyy'' )
, ''dd-mon-yyyy'' )

Please, help


Tom Kyte
April 03, 2003 - 3:50 pm UTC

sigh, decided to do it the wrong way eh.  Ok, you didn't use my example.  The to_char() on the dates goes outside the string, the date formats for that to_char go outside the string.. It looks like this:

ops$tkyte@ORA920> create or replace procedure p ( p_str in varchar2 )
  2  is
  3     l_str   long := p_str;
  4  begin
  5     loop
  6        exit when l_str is null;
  7        dbms_output.put_line( substr( l_str, 1, 250 ) );
  8        l_str := substr( l_str, 251 );
  9     end loop;
 10  end;
 11  /

Procedure created.

ops$tkyte@ORA920>
ops$tkyte@ORA920> declare
  2      p_start_date date := sysdate-5;
  3      p_end_date date := sysdate-5;
  4      v_sql long;
  5  begin
  6  v_sql := 'select *
  7              from my_table
  8             where datecompleted between
  9               to_date( ''' ||
 10                   to_char( p_start_date, 'dd-mon-yyyy' ) ||
 11                   ', ''dd-mon-yyyy'' )
 12               and
 13               to_date( ''' ||
 14                   to_char( p_end_date, 'dd-mon-yyyy' ) ||
 15                   ', ''dd-mon-yyyy'' )';
 16
 17      p(v_sql);
 18  end;
 19  /
select *
            from my_table
           where datecompleted between
             to_date( '29-mar-2003, 'dd-mon-yyyy' )

and
             to_date( '29-mar-2003, 'dd-mon-yyyy' )

PL/SQL procedure successfully completed.

 

(1): PLS-00307: too many declarations of 'TO_CHAR' match this call

A reader, April 03, 2003 - 4:42 pm UTC

Tom,

I am sorry, but now I am getting
(1): PLS-00307: too many declarations of 'TO_CHAR' match this call

Does it matter that I have group by like:

and dtd.DTD_FULL_DATE BETWEEN
to_date( ''' ||
to_char( p_start_date, 'dd-mon-yyyy' ) ||
', ''dd-mon-yyyy'' )
and
to_date( ''' ||
to_char( p_end_date, 'dd-mon-yyyy' ) ||
', ''dd-mon-yyyy'' ))

group by
dtd.dtd_year ,
dtd.dtd_month_number_in_year,
(CASE
WHEN (cst.cst_chain_code in (3,4,9,20,22,33,37,38,47,50,104,110,352,370,405,408,554,607,652,708,734)) THEN (cst.cst_chain_code)
ELSE (99999)
END),
pr.prd_item_code ,
(CASE
WHEN (pr.prd_stop_ship_date = ''0001-01-01'' and pr.prd_status_code = ''Y'') THEN (''D'')
WHEN (substr(pr.prd_stop_ship_date,1,4) <= dtd.dtd_year) and (pr.prd_stop_ship_date <> ''0001-01-01'') THEN (''D'')
WHEN (substr(pr.prd_launch_date,1,4) >= dtd.dtd_year) THEN (''N'')
ELSE (''E'')
END) ,
(CASE
WHEN (pr.prd_in_out_code = ''Y'') THEN (''D'')
WHEN (ord.ordd_prepack = ''N'') THEN (''O'')
WHEN (ord.ordd_prepack = ''Y'' and ppr.prd_promotion_cycle_code = ''ASSORT'') THEN (''O'')
ELSE (''D'')
END)) A

Tom Kyte
April 03, 2003 - 9:16 pm UTC

p_start_date must not be a date then is it.

you must be trying to to_char a char. I assummed that a variable named P_start_date would be a date.




Is there any way we can generate CSV file from web Forms

A reader, April 03, 2003 - 5:43 pm UTC

Hi Tom,
This is great . Is there any way we can generate .CSV
or SYLK file from web forms ? Thanks in advance

Tom Kyte
April 03, 2003 - 9:18 pm UTC

</code> http://otn.oracle.com/ <code>has great discussion forums where people who use forms hang out...

create directory for utl_file

A reader, April 15, 2003 - 10:05 am UTC

I am runing 9.2.0.1. The utl_file_dir parameter is static an is not defined:

SQL> select * from v$parameter where name like '%utl%';


       NUM NAME                                                                   TYPE
---------- ---------------------------------------------------------------- ----------
VALUE
----------------------------------------------------------------------------------------------------
ISDEFAULT ISSES ISSYS_MOD ISMODIFIED ISADJ DESCRIPTION
--------- ----- --------- ---------- ----- ---------------------------------------------------------
UPDATE_COMMENT
----------------------------------------------------------------------------------------------------
       516 utl_file_dir                                                              2

TRUE      FALSE FALSE     FALSE      FALSE utl_file accessible directories list

I would have to stop the instance to set it. In the thread: 
http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:8506429011995,

you said: 
> create directory directory1 as '/foo';
> create directory directory2 as '/bar';
> just create as many as you need. 

So I tried:

SQL> create directory d_test as '/ora/92/utl_file';

Directory created.

SQL> var l number
SQL> exec :l := dump_csv ('select * from tab where rownum < 5',',','/ora/92/utl_file','test.txt')
BEGIN :l := dump_csv ('select * from tab where rownum < 5',',','/ora/92/utl_file','test.txt'); END;

*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "APP_TICKET_REDE.DUMP_CSV", line 15
ORA-06512: at line 1

File permissions seem ok:

$ ls -ld /ora/92/utl_file
drwxr-xr-x   2 oracle   dba          512 Apr 14 18:08 /ora/92/utl_file

I think I have to associate the created directory with my utl_file_dir somehow...

What happened ?  

Tom Kyte
April 15, 2003 - 10:10 am UTC

try giving the NAME of the directory object you created.

exec :l := dump_csv ('select * from tab where rownum <
5',',','D_TEST','test.txt')

in uppercase.

string too long maybe ?

A reader, April 16, 2003 - 1:07 pm UTC

I am trying to run both your dump_csv and owa_sylk and I am getting the following error, which I think has something to do with the string length. Is that a problem with utl_file, dbms_sql or your procedures ?

I am trying the reproduce a small test case:

SQL> exec :v:=dump_csv('select rpad(''*'',2000,''*'') from dual',',','TMPDIR','foo.txt');
BEGIN :v:=dump_csv('select rpad(''*'',2000,''*'') from dual',',','TMPDIR','foo.txt'); END;

*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 562
ORA-06512: at "APP_TICKET_REDE.DUMP_CSV", line 54
ORA-06512: at line 1


I thought it had to do with the 2000-byte lenghts, but with a smaller size, the same happens:


SQL> exec :v:=dump_csv('select rpad(''*'',700,''*''), rpad(''*'',1000,''*'') from dual',',','TMPDIR','foo.txt');
BEGIN :v:=dump_csv('select rpad(''*'',700,''*''), rpad(''*'',1000,''*'') from dual',',','TMPDIR','fo*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 562
ORA-06512: at "APP_TICKET_REDE.DUMP_CSV", line 54
ORA-06512: at line 1

Finally it works with 1000:

SQL> exec :v:=dump_csv('select rpad(''*'',1000,''*'') from dual',',','TMPDIR','foo.txt');

PL/SQL procedure successfully completed.
 

Tom Kyte
April 16, 2003 - 6:29 pm UTC

15 begin
16 l_output := utl_file.fopen( p_dir, p_filename, 'w', 32000 );
^^^^^^


default line width is "small"

its not working for me... help please

Sonali Kelkar, June 19, 2003 - 4:03 pm UTC

create or replace procedure dump_table_to_csv( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
authid current_user /* <<<== if you want... runs as "invoker" and runs with ROLES */
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' , 64000);
execute immediate
'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
utl_file.put( l_output,
l_separator || '"' || l_descTbl(i).col_name || '"' );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;
/



execute dump_table_to_csv( 'NGITT.mwebwork', 'd:/oracle/oradata', 'Sonalitest.txt' );


I get this error
SQLWKS> execute dump_table_to_csv( 'NGITT.mwebwork', 'd:/oracle/oradata', 'Sonalitest.txt' );
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYSTEM.DUMP_TABLE_TO_CSV", line 49
ORA-06512: at line 2

in init.ora file I added this and restarted the server

utl_file_dir=D:\Oracle\oradata

AM I missing something ? NGITT is the schema name its on Newton instance...

Tom Kyte
June 20, 2003 - 3:58 pm UTC

try coding a teeny tiny UTL_FILE snipped that tries to open and close a file in this directory.

But anyway, maybe it doesn't like you using unix / in one place and windows \ in another.

simplify the example...
isolate the problem piece of code...
develop the tiny-est of test cases...
and you'll find your mistake (about 9999 times out of 10,000 anyway)....

not having windows installed anywhere, I cannot test that theory (that it is the /\ issue) but you can certainly set up a small, isolated, 3 line of code example to test with.



I guess I was missing oradba permission

sonali kelkar, June 20, 2003 - 12:11 pm UTC

I was missing oradba full permission on OS to do this.

regarding utl_file

sam, June 26, 2003 - 11:54 am UTC

Tom,

I am facing a unique problem.
When I run procedure through toad a file gets created on unix box with oracle as a owner. that's fine.

but when I try to run procedure from unix box itself it gives me following error

ERROR at line 1:
ORA-20001: invalid_operation
ORA-06512: at "U994983.UTL_EXP", line 122
ORA-06512: at line 1

Any clue??



Tom Kyte
June 26, 2003 - 12:28 pm UTC

your permissions don't sound like they are set entirely right. "on the box", it seems you might not be using "net" - hence you are forking the oracle executable off and it is running as you. when you use the net -- the listener (running as oracle presumably) forks this process.

To test this theory, connect using "user/pass@db" on that machine.

Then, check your perms on the oracle binaries, should look something like:

[tkyte@tkyte-pc Desktop]$ ls -l $ORACLE_HOME/bin/oracle
-rwsr-s--x 1 ora920 ora920 51766646 Mar 31 13:03

if the setuid thingys aren't on, it is running "as you" and won't have write privs.






A reader, August 18, 2003 - 8:45 am UTC


java stored procedure version of dump_csv

A reader, September 03, 2003 - 2:10 pm UTC

Tom, would you happen to have a java equivalent of the dump csv procedure?

Tom Kyte
September 04, 2003 - 8:31 am UTC

sure, it'll be like one line of sqlj code "begin dump_csv(:query); end"

:)

no, i don't have a full up java implementation, never will -- plsql is adept at this and if plsql weren't fast enough, i'd be using either sqlplus or C at that point.

very good, but is there a catalog view

hannibal, October 24, 2003 - 2:02 am UTC

hi tom,
thanks for the advice with the
create directory command. it proofed to be most usefull to me. much more than editing the init.ora file. is there a catalog view to see all created directory objects? i searched in user_object (and some others) for it but did not find a 'directory object' any way ...

thx

hannibal

Tom Kyte
October 24, 2003 - 9:32 am UTC

all_directories
dba_directories

How to do this

Pawan, November 14, 2003 - 9:30 am UTC

Thanks. This utility is really great. I have a small question. If my UTL FILE directory is set to /data/out and this and other information is available in an environment file (say myora.env on the unix box). This .env file is called by the shell script that also calls the UTL_FILE script. Is there a way to say
l_output := utl_file.fopen( UNIX ENV FILE VARIABLE NAME, p_filename, 'w' );

The environment file has enteries like

ORACLE_BASE=/opt/oracle;export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/ora817; export ORACLE_HOME
ORACLE_BIN=$ORACLE_HOME/bin; export ORACLE_BIN
ORACLE_DBA=$ORACLE_BASE/admin;export ORACLE_DBA
ORACLE_SID=ORA;export ORACLE_SID

Can we add ORACLE_UTLFILE= /data/out; export ORACLE_UTLFILE

Tom Kyte
November 14, 2003 - 10:21 am UTC



sqlplus u/p <<EOF
exec dump_csv( 'select * from emp', ',', '$ORACLE_UTLFILE', 'foo.dat' );
exit
EOF



Util_file

vani, January 28, 2004 - 6:50 pm UTC

This is really helpful.whenever I get any error.
one and only onething strikes me "ASK TOM"

Wonderful code-but i got an error !!

sathya, March 03, 2004 - 7:03 am UTC

The code provided for creating CSV is perfect. But when I ran the code, I got and error message:

ORA-06562: type of OUT argument must match type of column or bind variable

This error comes in the COLUMN_VALUE procedure...

please help...

Tom Kyte
March 03, 2004 - 10:00 am UTC

hows about a teeny teeny tiny example (you should just need to create a one column table for us to demonstrate)

generate csv

feefee, May 07, 2004 - 5:58 am UTC

It's good.

But why I simply spool the resulted set into a text file instead of using utl tools ???

If my sql include special characters, I have to do a lot of reformat job to replace ' with chr(39).

create file at client

mo, May 11, 2004 - 4:34 pm UTC

Tom:

1. Can the above "dump_csv" function be used to create a .txt file at the cient PC.

I have an HTML page that displays a label for a user. User has a special label printer next to his PC that can only print from the printer editor software. I need to create a button on the web page, that creates a CSV or comma delimited file on the PC and then have the user run the label printer editor software and do an export from the file and then print the label.

2. Can I also control whether I want to add data to existing file (add multiple addresses) several time or re-write the file from scratch.

Thank you,

Tom Kyte
May 11, 2004 - 8:57 pm UTC

1) that would be "a virus" -- if the server could reach out and put a file on your machine.


if the user is running a browser, you could either

o search for owa_sylk on this site, give them a spreadsheet.
o modify dump_csv to use htp and print it back to them.

dump address

mo, May 13, 2004 - 7:16 pm UTC

Tom:

Can you elaborate more on what you mean.
1. Do you mean dumping an oracle record data into a spreadsheet file on the client PC?
2. What do you mean print it back to them? I already show them the address info on the web page using htp. I am trying to find a way to take that info and export/paste it to the label editor software. It is more like "COPY" from one application and "PASTE" into the other.

Tom Kyte
May 14, 2004 - 10:00 am UTC

no i mean they click on a link and that link runs mod_plsql telling it to run dump_csv and you "print" the data back to them. they file save the data.

how to have new line when we have union all in the queru

rakesh, June 18, 2004 - 3:58 pm UTC

l_rows := dump_csv( 'select *
from all_users
where rownum < 5 union all select * from emp where 1=1',
',',
'/tmp',
'test.txt' );
end;
the problem is that it is openning it in one line
and now giving a new line character i.e chr(10) after each record

how am i supposed to get the new line at the end of each record

so in notepad i will see all the rows in different lines


Tom Kyte
June 18, 2004 - 4:35 pm UTC

if you do this on unix, unix smartly uses "\n" -- a single character as the end of line.

if you pull this file up in windows, it was decided long ago that a newline should be \r\n

try "write" instead of "notepad".. write does the right thing. or use a TEXT ftp to convert the \n to \r\n

or create the file on windows where utl_file will do that as a matter of fact.

rakesh, June 18, 2004 - 4:00 pm UTC

i meant how to get new line after each record
when i open it in notepad

Tom Kyte
June 18, 2004 - 4:36 pm UTC

o use write
o ftp with type "text"

can this be done

rakesh, June 22, 2004 - 12:23 pm UTC

i have a table where data is like this

lpn_id inventory_item_id carton_serial_number
228 2 100
228 2 200
228 2 300
228 2 400
228 2 500

228 4 100
228 4 200
228 4 300
228 4 400
228 4 500

230 2 100
230 2 200
230 2 300
230 2 400
230 2 500

230 4 100
230 4 200
230 4 300
230 4 400
230 4 500

now lpn_id, inventory_item_id, carton_serial_numbers make a unique key

i want a flat file to be created like this uing the dump_csv function

2
100
200
300
400
500
4
100
200
300
400
500
2
100
200
300
400
500
4
100
200
300
400
500

CAN THIS BE DONE IN A SELECT STATEMENT WHICH CAN BE PASSED TO THE DUMP_CSV



Tom Kyte
June 22, 2004 - 2:08 pm UTC

YES (in easier in 9i, but doable before)....

and if you supply me the create table statement and insert into's, I'll be glad to demonstrate.

A reader, June 22, 2004 - 2:41 pm UTC

drop table test_1;

create table test_1
(lpn_id number,
inventory_item_id number,
carton_serial_number varchar2(30),
buyer_part_number varchar2(30) ,
packing_slip_number varchar2(30)
)

insert into test_1 values
( 228, 2, 100, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 228, 2, 200, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 228, 2, 300, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 228, 2, 400, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 228, 4, 100, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 228, 4, 200, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 228, 4, 300, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 228, 4, 400, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 228, 4, 500, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 228, 4, 600, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 228, 4, 700, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 230, 2, 100, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 230, 2, 200, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 230, 2, 300, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 230, 2, 400, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 230, 2, 500, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 230, 2, 600, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 230, 2, 700, '00000005-10A-JP', 'PAK001' ) ;

insert into test_1 values
( 230, 4, 100, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 230, 4, 200, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 230, 4, 300, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 230, 4, 400, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 230, 4, 500, '00000006-10A-US', 'PAK001' ) ;

insert into test_1 values
( 230, 4, 600, '00000006-10A-US', 'PAK001' ) ;


lpn_id, inventory_item_id, carton_serial_number is unique
wanted a falt file like this
i was tinking of creatin a temporary table and inserting
parent child relation just like emp table
and finally using connect by prior to develop such a flat file


00000005-10A-JP PAK001
100
200
300
400
00000006-10A-US PAK001
100
200
300
400
500
600
700
00000005-10A-JP PAK001
100
200
300
400
500
600
700
00000006-10A-US PAK001
100
200
300
400
500
600

thanks for your help..


A reader, June 22, 2004 - 4:46 pm UTC

i was able to achieve this using group by rollup


select
decode(leve_l, ''0400'',to_char(''0400'',''fm0000''),null )
,decode(leve_l, ''0400'', buyers_part_number,null)
,decode(leve_l ,''0400'', no_of_units_shipped, null )
,decode(leve_l ,''0400'', cum_qty_shipped, null )
,decode(leve_l ,''0400'', PACKING_SLIP_NUMBER, null )
,decode(leve_l ,''0400'', no_of_containers, null )
,decode(leve_l ,''0400'', no_of_units_per_container, null )
,decode(leve_l ,''0500'', ''0500''||carton_serial_number, null )
,null
,null
from
(
select
buyers_part_number
,no_of_units_shipped
,cum_qty_shipped
,PACKING_SLIP_NUMBER
,no_of_containers
,no_of_units_per_container
,leve_l
,pallet_lpn_id
,inventory_item_id
,carton_serial_number
,count(*)
from
(
select outer.BUYERS_PART_NUMBER
,to_char(nvl(outer.NUMBER_OF_UNITS_SHIPPED_QTY,0),''fm0000000000'') no_of_units_shipped
,to_char(nvl(outer.CUM_QTY_SHIPPED,0),''fm0000000000'') cum_qty_shipped
,outer.PACKING_SLIP_NUMBER
,to_char(nvl(outer.NO_OF_CONTAINERS,0 ),''fm00000'') no_of_containers
,to_char(outer.NO_OF_UNITS_PER_CONTAINER) no_of_units_per_container
,( case when grouping(outer.carton_serial_number) = 1 then ''0400'' else ''0500'' end ) leve_l
,outer.outermost_lpn_id pallet_lpn_id
,outer.inventory_item_id
,outer.carton_serial_number
,count(*)
from KCUS_DANA_PO_DETAILS outer
where already_run_flag is null
and delivery_id = ' || in_delivery_id || '
group by rollup ( outer.BUYERS_PART_NUMBER
,to_char(nvl(outer.NUMBER_OF_UNITS_SHIPPED_QTY,0),''fm0000000000'')
,to_char(nvl(outer.CUM_QTY_SHIPPED,0),''fm0000000000'')
,outer.PACKING_SLIP_NUMBER
,to_char(nvl(outer.NO_OF_CONTAINERS,0 ),''fm00000'')
,to_char(outer.NO_OF_UNITS_PER_CONTAINER)
,outermost_lpn_id
,inventory_item_id
,carton_serial_number )
having grouping(inventory_item_id) <> 1
order by outermost_lpn_id , inventory_item_id, carton_serial_number
)
group by buyers_part_number
,no_of_units_shipped
,cum_qty_shipped
,PACKING_SLIP_NUMBER
,no_of_containers
,no_of_units_per_container
,leve_l
,pallet_lpn_id
,inventory_item_id
,carton_serial_number
)

utl_file and binary files?

A reader, July 11, 2004 - 1:05 pm UTC

"in fact UTL_FILE can only create ascii "

Hi tom
In utl_file, we have functions such as get_raw/put_raw that suggest that utl_file can be now used to read/write
binary files...Is this true?

Tom Kyte
July 11, 2004 - 1:41 pm UTC

new in 9ir2, yes.

so 9i /10g doc is incorrect?

A reader, July 11, 2004 - 1:45 pm UTC

9i r2 doc:
"With the UTL_FILE package, your PL/SQL programs can read and write operating system *text* files."
In 10g r1 doc:
"With the UTL_FILE package, PL/SQL programs can read and write operating system text files."



Tom Kyte
July 12, 2004 - 11:00 am UTC

9ir2 added the _raw functions as a new feature, yes.

yes - it looks like the docs are incorrect

A reader, July 11, 2004 - 2:09 pm UTC

I just verified reading from and writing to a
binary file...it works...

thanx!

Operation System Info

Vikas Sharma, July 13, 2004 - 10:46 am UTC

Hi Tom,

I have set the utl_file_dir parameter to *.

I want to read a file stored on the server. I know the file location which is in the temp dir ie it will be either in c:\temp (WIN OS) or /temp (unix OS). But i dont know the OS of the server so i am not able to the correct path of the file when opening for reading it. using

v_file := utl_file.fopen(l_loc,l_fileName,'r',32000);

Can you please suggest me how can i find programatically the OS of the server so that i can code like following

if l_serverOs like '%WINDOWS%' THEN

v_file := utl_file.fopen('c:\temp',l_fileName,'r',32000);
else
v_file := utl_file.fopen('/temp',l_fileName,'r',32000);

end if;

Thanks and regards

Vikas Sharma



Tom Kyte
July 13, 2004 - 12:20 pm UTC

*, hmm, if I was on your system, and I got mad at you, maybe I would open "system.dbf" for writing ..... poof goes system.dbf

anyway, you might want to rethink that.

Here is a snippet of my login.sql where I setup my editor based on OS of server and a temp spool file name:

define e=vi
column E new_value E
column S new_value SETTINGS
select decode( substr( dbms_utility.port_string, 1, 5 ), 'IBMPC', 'notepad', 'vi' ) E,
decode( substr( dbms_utility.port_string, 1, 5 ),
'IBMPC', 'c:\temp\xtmpx.sql', '/tmp/xtmpx.sql' ) S
from dual;
define _editor=&E



Thanks

vikas Sharma, July 14, 2004 - 9:52 am UTC

Hi Tom,

Thanks,

I would change the * to a specified directory.

Thanks.

Reading file from 2 or more servers using UTL_File

A reader, July 22, 2004 - 6:21 pm UTC

Hi Tom,
One of my developers asked me resolve a issue while using UTL_File. In this case we want to use 2 servers that can read and/or write to the directory on the other server.

For e.g.:
Server_1 Directory srv1
Server_2 Directory srv2
All the permissions on the above mentioned directories are there and the directories are shared on the OS level.
Now what we want is the files generated in the directory srv1 or srv2 can be read and/or written to by Server_1 and Server_2. That is the any server can read/write the files on the other server.
They tried it using UTL_file but it failed. They also tried using the full path but still no success. Although they can open the above mentioned directories by mentioning the path on the OS level.
Any ideas on how to resolve this issue.
Oracle 9iR2 on Windows 2000 Advanced Server.

Hope I have not made it sound complicated :D
Thanks as always for your help.



Tom Kyte
July 23, 2004 - 8:23 am UTC

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:241814624807 <code>

windows makes this really hard - but it is "doable"

A reader, September 01, 2004 - 5:49 pm UTC

Tom --

I was able to re-create your example. Thank you! However, I need this file to look like a report (I know, I know, how stupid, but..) Please advise on how can I create a report heading for this file, have column headings, page numbers, etc. Is it possible? If not, then, can I use EXTERNAL TABLE feature to dump my data into and then use SQL*PLus report formating command on it? Otherwise, I 'd have to dump it all in the physical table and then use SQL*Plus from there.
Please, advise..

Tom Kyte
September 01, 2004 - 8:51 pm UTC

using utl_file, you can "print" anything you want. been years (more than double digit years) since I've written a report generator (i was famous for doing report "5" in my first job, that was it's name...) but - i would suggest looking at one of literally billions of report writing tools out there?

what is forking?

daniel, September 15, 2004 - 9:08 am UTC

Tom, could you explain what forking means?
Thanks

Tom Kyte
September 15, 2004 - 10:03 am UTC

it is the unix way of creating a process

NAME
fork - create a child process

SYNOPSIS
#include <sys/types.h>
#include <unistd.h>

pid_t fork(void);

DESCRIPTION
fork creates a child process that differs from the parent process only
in its PID and PPID, and in the fact that resource utilizations are set
to 0. File locks and pending signals are not inherited.

Under Linux, fork is implemented using copy-on-write pages, so the only
penalty incurred by fork is the time and memory required to duplicate
the parent’s page tables, and to create a unique task structure for the
child.

RETURN VALUE
On success, the PID of the child process is returned in the parent’s
thread of execution, and a 0 is returned in the child’s thread of exe-
cution. On failure, a -1 will be returned in the parent’s context, no
child process will be created, and errno will be set appropriately.


so...

daniel, September 15, 2004 - 12:49 pm UTC

so what is the application forking?
and does it somehow bypass the net8 and just go directly to the database?

I'm just trying to get the understanding, because we have a 3rd party app that is having problems connecting to the database after so many users are connected, and oracle support is saying that it is because they're forking.

Tom Kyte
September 15, 2004 - 1:00 pm UTC

In unix if you are on the machine and not connecting over net8, and you connect to the database using sqlplus -- then sqlplus will fork() (copy itself) and then exec( $ORACLE_HOME/bin/oracle ) directly.

Normally when you connect over net8, the listener will fork() itself, and then exec( $ORACLE_HOME/bin/oracle ).

if sqlplus does the forking, and the permissions on the oracle binary are not set right -- the oracle binary will be running with YOUR permissions (it is supposed to be setuid, it is support to run "as oracle"). In this case -- you won't be able to attach the SGA and it'll fail.

That is why in this case using "toad" over the network worked -- the listener was running as oracle, the oracle binary was therefore running as oracle and it was able to attach the SGA.

UTL file - enter key

Riyaz, October 25, 2004 - 10:27 am UTC

This is terrific solution.

But I have problem. When input captured through GUI, users used <ENTER KEY>. The same also gets stored in the table field. Now when extracted through UTL FILE, the same field is getting into the next line, which makes the format useless. So I used replace(file_name,chr(135), ' ') - replacing enter key with space, which is NOT working. copying the space charactor directly in the place of chr(135) is also not working.

1 select replace(address,'
',' ') from customer
3* where cust_code='S00002'
SCOTT@ORCL> /

REPLACE(ADDRESS,'','')
--------------------------------
SCO 489-90
SECTOR 35 A

Is there any way out?

Tom Kyte
October 25, 2004 - 11:08 am UTC

chr(135) isn't the "enter key"

chr(13)||chr(10)

or

chr(10)

is.

Nice

Riyaz, October 27, 2004 - 8:02 am UTC

Thanks, it is working.

dump_csv: faster but at what cost?

Jim Dickson, November 03, 2004 - 8:14 am UTC

We have been using dump_csv for a while with great success.

One of our Java developers recently reviewed code and pointed out a change which nearly doubles performance.
The change was to concatenate the columnValue into a 4K varchar2 and write string out once per row.

I am sure you considered and rejected this option - can you give reasons why?
Is there any reason why we should not implement this change to dump_csv?

Clearly this would fail for rows > 4K in length (including separators and newline). In our environment, this is not a problem - and we would catch any exception.

Taking this approach to the next step, could write every 4K instead of every row - reducing number of I/Os.

We are typically unloading 100000 - 1000000 rows, 150 - 200 columns, date, number, varchar2 holding UTF8 data.
Typically average row length < 1K

NB We have no PL/SQL developers here.
I am aware of faster Pro*C option available on this site.

Running 9.2.0.3.0 and 9.2.0.5.0 Standard on Solaris 2.8

l_separator := '';
l_output_str := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i,
l_columnValue );
l_output_str := l_output_str || l_separator || l_columnValue ;
l_separator := p_separator;
end loop;
utl_file.put_line( l_output, l_output_str );
l_cnt := l_cnt+1;

Tom Kyte
November 03, 2004 - 10:18 am UTC

i didn't consider and reject it. i just didn't implement it.

it is very valid. in fact -- go for 32k and concat multiple lines.
in fact -- go for array fetches!

if I needed raw speed, i didn't use plsql, i used pro*c.

</code> http://asktom.oracle.com/~tkyte/ <code>

aka SQL*Unloader

Edgar, November 03, 2004 - 12:26 pm UTC

It is really hard task to code more or less universal aka SQL*Unloader indeed using PL/SQL.

Just imagine, if it have also to manage records and field separators, line feeds, trailing blanks in VARCHAR2(2000) columns... (*)

And what about batch fetches, to achive some reasonable level of performance?

I had tryed it.
Finally implemented in Java using not csv, but INFILE "var N" file format with VARCHARC(n,m) fields..

My initial try - SQL*Plus script with refcursor, spool, set arraysize, anonymous PL/SQL block with dynamic SQL, some SQL*Plus formatting trics - was failed, becouse of poor performance of 3 nested "replace" SQL function calls per each CHAR,VARCHAR2 value :(
(but requirement * was satisfied, indeed)



Tom Kyte
November 04, 2004 - 9:29 am UTC

no more or less "harder" than in c, vb, java, etc etc etc etc.

but I gave you

plsql
sqlplus
c

in order of speed from slow to fast -- all waiting for you to just "use".

need to create pdf,word or Excel files

S.S., November 03, 2004 - 6:00 pm UTC

Hi Tom,

Would like to extract the blob to create(recreate) the files which may be either pdf, word or excel spreadsheets.

Thanks.


Flat file to Tables

pooja, November 05, 2004 - 1:04 am UTC

hi tom,
we used the UTL_FILE to dump the data from tables to flat file. now we want to transfer the flat file data to tables but in the other database viz: AS400.
how can we do this by using Oracle.

Plz shed a light on this

pooja

Tom Kyte
November 05, 2004 - 4:18 pm UTC

how can you load an AS400 database using Oracle.

Hmm.

I'd ask IBM personally. there are gateways and such but hey, if you've unloaded the data OUT of the database -- and want to get it into an AS400 database -- well, Oracle doesn't really have any tools for that.

Directing output to local folder

Karen Warcup, November 18, 2004 - 10:42 am UTC

This has been a really useful thread. I would like to use dump_csv to create a file I can open in Excel. I apologise if the following is a stupid question but is there any way to create the csv file on the local windows workstation? I am in the UK accessing a remote database server in the US and have very basic user rights.

Thanks

Tom Kyte
November 18, 2004 - 10:57 am UTC

if you use mod_plsql and a webbrowser -- it is easy.

if you do not, you need to run software on the client that connects to oracle and creates a file locally.

ignore last post

Karen Warcup, November 18, 2004 - 10:49 am UTC

Hi, I have found the answer in some of your other threads. I need to do this using SQLPlus.

Thanks


Column Name, too?

Bo, December 16, 2004 - 12:01 pm UTC

Hi, Tom.
Is that possible to include column names from SQL query in dump_csv function as the first row into the output ASCII file?

Thanks,

Tom Kyte
December 16, 2004 - 1:26 pm UTC

create or replace procedure dump_table_to_csv( p_tname in varchar2,
p_dir in varchar2,
p_filename in varchar2 )
authid current_user /* <<<== if you want... runs as "invoker" and runs with ROLES */
is
l_output utl_file.file_type;
l_theCursor integer default dbms_sql.open_cursor;
l_columnValue varchar2(4000);
l_status integer;
l_query varchar2(1000)
default 'select * from ' || p_tname;
l_colCnt number := 0;
l_separator varchar2(1);
l_descTbl dbms_sql.desc_tab;
begin
l_output := utl_file.fopen( p_dir, p_filename, 'w' );
execute immediate
'alter session set nls_date_format=''dd-mon-yyyy hh24:mi:ss'' ';

dbms_sql.parse( l_theCursor, l_query, dbms_sql.native );
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );

for i in 1 .. l_colCnt loop
utl_file.put( l_output,
l_separator || '"' || l_descTbl(i).col_name || '"' );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
end loop;
utl_file.new_line( l_output );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
l_separator := '';
for i in 1 .. l_colCnt loop
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( l_output, l_separator || l_columnValue );
l_separator := ',';
end loop;
utl_file.new_line( l_output );
end loop;
dbms_sql.close_cursor(l_theCursor);
utl_file.fclose( l_output );

execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
exception
when others then
execute immediate 'alter session set nls_date_format=''dd-MON-yy'' ';
raise;
end;
/


A reader, December 17, 2004 - 9:02 am UTC


ORA-29280: invalid directory path, ORA-06512

Dawar Naqvi, March 27, 2005 - 3:46 pm UTC

Tom,

My OS is Linux.
Database version is 10.1.0.3.0.

My goal is to open the file (table data) in txt format on windows OS.

Action:

create or replace
    function  dump_csv( p_query        in varchar2,
                        p_separator in varchar2 default ',',
                      p_dir       in varchar2 ,
                       p_filename  in varchar2 )
    return number
    is
        l_output        utl_file.file_type;
        l_theCursor     integer default dbms_sql.open_cursor;
       l_columnValue   varchar2(2000);
       l_status        integer;
       l_colCnt        number default 0;
       l_separator     varchar2(10) default '';
       l_cnt           number default 0;
   begin
       l_output := utl_file.fopen( p_dir, p_filename, 'w' );
   
       dbms_sql.parse(  l_theCursor,  p_query,
                                            dbms_sql.native );
   
       for i in 1 .. 255 loop
           begin
               dbms_sql.define_column( l_theCursor, i,
                                       l_columnValue, 2000 );
               l_colCnt := i;
           exception
               when others then
                   if ( sqlcode = -1007 ) then exit;
                   else
                       raise;
                   end if;
           end;
       end loop;
   
       dbms_sql.define_column( l_theCursor, 1,
                               l_columnValue, 2000 );
   
       l_status := dbms_sql.execute(l_theCursor);
   
       loop
           exit when ( dbms_sql.fetch_rows(l_theCursor) <= 0 );
           l_separator := '';
           for i in 1 .. l_colCnt loop
               dbms_sql.column_value( l_theCursor, i,
                                      l_columnValue );
               utl_file.put( l_output,
                             l_separator || l_columnValue );
               l_separator := p_separator;
           end loop;
           utl_file.new_line( l_output );
           l_cnt := l_cnt+1;
       end loop;
       dbms_sql.close_cursor(l_theCursor);
   
       utl_file.fclose( l_output );
       return l_cnt;
   end dump_csv;
   /

Function Created
*********************************************************
I ran below as

 declare 
 l_rows number;
 begin 
 l_rows := dump_csv( 'select *  
                  from employee_items', 
                 ',',

  '/opt/oracle', 
 'test.dat' ); 
 dbms_output.put_line( to_char(l_rows) || 
 ' rows extracted to ascii file' ); 
 end; 
 / 


 declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at "WITS.DUMP_CSV", line 15
ORA-06512: at line 4


SQL>

Regards,
Dawar
 

Tom Kyte
March 27, 2005 - 5:22 pm UTC

so, what is your utl_file_dir init.ora set to? (or use a directory object instead in 9i and above to avoid having to set that parameter)

ORA-29280: invalid directory path, ORA-06512

Dawar, March 27, 2005 - 8:12 pm UTC

Tom,

Thanks, I have not set my utlfile parameter yet.
I will do that.

Anyway, how to use 9i directory object?
Probably I will go for your next option.
Please give me some hints about it.


Regards,
Dawar

Tom Kyte
March 28, 2005 - 7:16 am UTC

ctl-f for:

create directory

Dawar, March 28, 2005 - 3:54 pm UTC

Tom,
My DB version is 10.1.0.3.
Database server is Linux.

Here I wrote UTL_FILE procedure to perform read/write operations on text files. but getting below error.

CONNECT sys/password@linuxprod AS SYSDBA
CREATE OR REPLACE DIRECTORY EXTRACT_DIR AS '/oracle/extract';
GRANT READ, WRITE ON DIRECTORY EXTRACT_DIR TO dawar;

CREATE OR REPLACE PROCEDURE EMP_CSV AS
CURSOR c_data IS
Select
EMP_NO,
DEPT_NO,
PAYROLL_ITEM_NO ,
ITEM_LETTER ,
START_DATE,
START_JOB_STATUS_REASON,
END_DATE ,
END_JOB_STATUS_REASON,
SPECIALTY,
SUB_SPECIALTY,
STEP_NO,
JOB_CODE,
ASSIGNED_GROUP_ID,
BUDGETED_ITEM_NO,
BUDGETED_GROUP_ID,
PAY_LOCATION,
PERFORMANCE_BASED_PAY_SALARY,
ALTERNATE_SCHEDULE_SALARY,
Y_RATE_SALARY,
PROBATION_END_DATE,
PRIOR_ITEM_INDICATOR,
CASCADE_EXEMPTION ,
CREATOR_ID,
CREATOR_DATE,
UPDATER_ID,
UPDATER_DATE,
CASCADE_TRANSFER_TO_PAY_LOC
From employee_items

ORDER BY emp_no;

v_file UTL_FILE.FILE_TYPE;
BEGIN
v_file := UTL_FILE.FOPEN(location => 'EXTRACT_DIR',
filename => 'emp_csv.txt',
open_mode => 'w',
max_linesize => 32767);
FOR cur_rec IN c_data LOOP

UTL_FILE.PUT_LINE(v_file,
cur_rec.EMP_NO,
cur_rec.DEPT_NO,
cur_rec.PAYROLL_ITEM_NO,
cur_rec.ITEM_LETTER, cur_rec.START_DATE,
cur_rec.START_JOB_STATUS_REASON,
cur_rec.END_DATE,
cur_rec.END_JOB_STATUS_REASON,
cur_rec.SPECIALTY,
cur_rec.SUB_SPECIALTY,
cur_rec.STEP_NO,
cur_rec.JOB_CODE,
cur_rec.ASSIGNED_GROUP_ID,
cur_rec.BUDGETED_ITEM_NO,
cur_rec.BUDGETED_GROUP_ID,
cur_rec.PAY_LOCATION,
cur_rec.PERFORMANCE_BASED_PAY_SALARY,
cur_rec.ALTERNATE_SCHEDULE_SALARY,
cur_rec.Y_RATE_SALARY,
cur_rec.PROBATION_END_DATE,
cur_rec.PRIOR_ITEM_INDICATOR,
cur_rec.CASCADE_EXEMPTION,
cur_rec.CREATOR_ID,
cur_rec.CREATOR_DATE,
cur_rec.UPDATER_ID,
cur_rec.UPDATER_DATE,
cur_rec.CASCADE_TRANSFER_TO_PAY_LOC);

END LOOP;
UTL_FILE.FCLOSE(v_file);

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20000, 'File location is invalid.');

WHEN UTL_FILE.INVALID_MODE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20001, 'The open_mode parameter in FOPEN is invalid.');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20002, 'File handle is invalid.');

WHEN UTL_FILE.INVALID_OPERATION THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20003, 'File could not be opened or operated on as requested.');

WHEN UTL_FILE.READ_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20004, 'Operating system error occurred during the read operation.');

WHEN UTL_FILE.WRITE_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20005, 'Operating system error occurred during the write operation.');

WHEN UTL_FILE.INTERNAL_ERROR THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20006, 'Unspecified PL/SQL error.');

WHEN UTL_FILE.CHARSETMISMATCH THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20007, 'A file is opened using FOPEN_NCHAR, but later I/O ' ||
'operations use nonchar functions such as PUTF or GET_LINE.');

WHEN UTL_FILE.FILE_OPEN THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20008, 'The requested operation failed because the file is open.');

WHEN UTL_FILE.INVALID_MAXLINESIZE THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20009, 'The MAX_LINESIZE value for FOPEN() is invalid; it should ' ||
'be within the range 1 to 32767.');

WHEN UTL_FILE.INVALID_FILENAME THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20010, 'The filename parameter is invalid.');

WHEN UTL_FILE.ACCESS_DENIED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20011, 'Permission to access to the file location is denied.');

WHEN UTL_FILE.INVALID_OFFSET THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20012, 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid; ' ||
'it should be greater than 0 and less than the total ' ||
'number of bytes in the file.');

WHEN UTL_FILE.DELETE_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20013, 'The requested file delete operation failed.');

WHEN UTL_FILE.RENAME_FAILED THEN
UTL_FILE.FCLOSE(v_file);
RAISE_APPLICATION_ERROR(-20014, 'The requested file rename operation failed.');

WHEN OTHERS THEN
UTL_FILE.FCLOSE(v_file);
RAISE;
END;


Line # = 43 Column # = 5 Error Text = PLS-00306: wrong number or types of arguments in call to 'PUT_LINE'
Line # = 43 Column # = 5 Error Text = PL/SQL: Statement ignored


Please note: Once its created I will move this file to windows OS and wante dto open file in excel or txt format.

Regards,
Dawar



Tom Kyte
March 28, 2005 - 6:20 pm UTC

first

stop using sys, just stop, don't do that. ever. you don't need to, it is dangerous, stoip


did you see the documentation for utl_file? put_line takes a file and a string, not a big list of stuff.


see
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>
maybe you can just use that.

Thanks so much

Robert, April 26, 2005 - 8:17 pm UTC

Tom,

Thank you so much. This is exactly what I needed and it took me only about 30 minutes to apply to my environment.



Thanks Dawar

Rajat, May 11, 2005 - 8:06 am UTC

Hi Tom
Not sure if Mr Dawar got his answer or not, but his exception handling did help me in getting my answer. Using his exceptions I got to know where I was going wrong. i had tried everything under the sun from init.ora to create directory and what not..But I was trying to open a non existent file. Typo of 'r' instead or 'w'. errrrr..

multiple sessions writing to a file...

k., May 27, 2005 - 9:36 am UTC

hi tom,

we are rewriting log procedures. the logs are to be written
to text files. we have been using pipes so far - a job
was listening in an infinite loop and wrote the data to
a text file.

now - to remove an infinite job + the pipe from the system -
i rewrote the logging package to use autonomous transactions
and a job is run once every 20 minutes that flushes the currently
inactive log table to file.

the problem is that we have cca 12 million log entries a day,
so we are trying to find a solution whose impact on the database will
be the lowest possible.

i proposed that we use solely utl_file to log. all sessions
would open the same file to write to, and flush the data every now
and then.

i ran some tests and utl_file seems to behave the way that would
suit me. we don't mind log entries not being ordered
by time in the log file.

the problem is that my boss considers this to be quite dangerous,
as he has never seen utl_file used this way. i haven't either, and
i have no arguments to support this solution, as i myself
do not know if doing it this way would be safe.


here is a short script i used to test this approach:



CREATE OR REPLACE PACKAGE files AS
PROCEDURE Test(id IN VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY files AS

gfil_log utl_file.file_type;

PROCEDURE OpenLogFile AS
BEGIN
gfil_log := utl_file.fopen('LOGS','kukiii','a');
END;

PROCEDURE Log(ptxt_msg IN VARCHAR2) AS
BEGIN
utl_file.put_line(gfil_log,ptxt_msg);
END;

PROCEDURE Test(id IN VARCHAR2) AS
BEGIN
FOR i IN 1..10
LOOP
FOR e IN 1..100
LOOP
Log(id||'.'||e||'.duuudydaaaadyddaaaaaaadyyyydaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa');
END LOOP;
utl_file.fflush(gfil_log);
dbms_lock.sleep(0.3);
END LOOP;
END;

BEGIN
OpenLogFile;
END;
/


now i can run
files.test('p1');
files.test('p2');
....
from multiple sessions.



could you please comment on this and maybe show me the possible problems?

also i would like to ask if it is safe to leave a file open and let oracle
close it when the session is over?

thanks is advance
k.

Tom Kyte
May 27, 2005 - 9:52 am UTC

(databases where born to write to you know! why files at all?????)

as long as everyone writes to their own file, this will work.

It will however not be "super fast".

I would log to a table, if you need to roll it off, great, log to a table partitioned by hour and have a C program or something "fast for writing to the file system" retrieve, write and truncate the partition.

...

k., May 27, 2005 - 10:39 am UTC

<quote>
as long as everyone writes to their own file, this will work.
</quote>

i intended to use 1 file for all - oracle apparently allows for this so i wonder what might be the problem (apart from the synchronization required for the flush)


<quote>
I would log to a table, if you need to roll it off, great, log to a table
partitioned by hour and have a C program or something "fast for writing to the
file system" retrieve, write and truncate the partition. </quote>

i suggested that we use c to do this, but was told that no external routines are allowed (more problems for the administrators, non-plsql code, etc, etc.)
so this has to be written in plsql anyway.
still the main problem lies not in the "flushing" phase (ie in writing the data to a file) but in the inserts to the log table themselves.

i ran some tests and writing to a text file appears to be cca 2x faster than inserting to a table with 1 commit at the end of the process and (more importantly) more than 4x faster than inserts with 1 commit per insert.

i suggested that we only use autonomous transactions for logging of certain events (ERRORS, DEBUG, etc - where you need to see the log even if the client transaction rolls back) and the remaining "levels" would be inserted without AT and commit. then i found that most of the inserts are "DEBUG" entries, so this would not help much.

i can't change the way logging is used in this application.
all i can do is try to find the fastest way to store the data. with the tests showing that utl_file.put_line outperformed insert two (or four) times, i thought it would be the best way to implement it.

now if we do the inserts i will try to persuade my boss to leave the AT (except for ERRORS) and potentionally lose some entries if the transaction rolls back.

i was thinking of adding a parameter to the log procedure, that would explicitly tell it to commit and let false be its default value - but i can't afford to go through all code that uses logging and check whether a commit is really necessaty or not...

would you still do the inserts?





Tom Kyte
May 27, 2005 - 12:32 pm UTC

<quote>i intended to use 1 file for all </quote>

you better serialize that yourself using dbms_lock, else you'll get garbage at some point. very unsafe to have >1 person writing to a buffered text file like that.

i don't like the file idea at all, no. Not that I don't believe you -- but opening a file, writing file, close file -- you are saying you find that to be much faster than insert/commit???

1 file

k., May 28, 2005 - 5:44 pm UTC

the file would be opened on the first call to the logging package and then oracle would close it when the session is over (i am not sure if it always will - but i tested this and it worked fine).

as far as serialization is concerned - of course i do not know the internals but again from the tests it seemed to me that oracle serializes access to a file when a "fflush" is called by itself - and put_line called from multiple sessions does not cause collisions - each process writes to their own buffer...

from what you said ("...very unsafe to have >1 person writing to a buffered text file like
that...") it seems my assumtions were wrong..is there a document that describes internals of utl_file..i didnot find the information i needed anywhre so i tested - and maybe i was just lucky to get the results i hoped for
(3 sessions writing to a text file concurrently,
utl_file.put_line non-blocking,
utl_file.fflush serialized,
file closed when the session is over)

regardless of how many tests i run, i won't dare to use this solution in production, unless i can be certain that i wasn't just being lucky [of course, unless the tests prove me wrong - that would solve my problem prettty fast :-)]..but then again if more tests show that behaviour of utl_file is ok for this purpose, i won't be able to get this solution out of my head.






Tom Kyte
May 28, 2005 - 7:35 pm UTC

this would and should be considered extremely unsafe -- period. Multiple sessions writing to a buffered file, this isn't going to be "out of sync", this is going to be scrambled bytes..

Utl_file internals aside, this is really dependent on the OS. You will not get me to say "this is a good idea or safe".

so -- entirely your call at this point. speaking as a C programmer, not a plsql programmer, this is very dangerous.

You do not control the flush entirely, sure you can MAKE it happen, but it can happen whenver we like. two people writing to the same file -- bad idea all around.

how to make a file path relative to some initialization or environmental parameter

Flavio Casetta, June 01, 2005 - 6:20 am UTC

Hi Tom,
I'd like to locate a text file (either in input or output)using UTL_FILE, assuming that it resides in a given directory whose location is relative to the Oracle Home value, for instance.

Is there any way for a normal user to retrieve the current value of an environment setting like ORACLE_HOME or ORACLE_BASE from within PL/SQL?
I mean something cross-platform if possible.

To make an example, I'd like to read or write to a file located in a directory called 'adhoc', located at the same level of 'bdump' or 'cdump', like
'D:\Oracle\Ora81\admin\testdb\adhoc'.

This technique would allow me to read or write that file without reconfiguring some parameter if I had to install another database on a different machine having different installation paths.
Of course I'll need to set UTL_FILE_DIR = *
to make it work or I could devise a stored procedure runnning the necessary CREATE DIRECTORY statements for later versions of oracle, where I set the expected path basing on the same mechanism.

The only way I can see is to retrieve the value of parameter 'background_dump_dest' by means of a special function running as user system and granted to my app user, then replacing "bdump" with "adhoc".
Or, alternatively, grant select on v$parameter to the app user.

Is there any other way to achieve this without special grants?

Thanks,
Flavio



Tom Kyte
June 01, 2005 - 10:05 am UTC

well, there is no rule stating bdump or cdump or udump will be under the Oracle home (in fact, a common practice has it "not be there" -- not under the Oracle home itself but else where)

would using the utl_file_dir itself be sufficient? Just read out it's first value and say "that is where you shall find this file"?

I said oracle_home, but...

Flavio, June 01, 2005 - 10:41 am UTC

Well,
I wrote Oracle_home just to make an example of an environment variable.

Actually the most likely candidate would be a directory sibling of bdump or cdump, because I am sure that I can read those values from the dictionary in some way.

In this case, as I said, it's easy to replace "bdump" with "adhoc" after reading the value of background_dump_dest from v$parameter.
It's not important to me where bdump is located, it's important to know that wherever it is, there is a sibling directory called adhoc where I can find my stuff.
And if it is not there, I can take action, issue a warning, or whatever.

My question was rather how to retrieve such values, do I have to grant select on sys.v$_parameter?
I was just wondering if there was some function "sys_context-like" returning that value without having to explicitly grant the privilege to the app developer.
My current understanding is there is not such function, right?

Thanks,
Flavio

Tom Kyte
June 01, 2005 - 11:31 am UTC

if you can rely on an "environment variable", then you can rely on a row in a table just as easily -- I mean, if they have to set something, they have to set something. I simply "insert where I should put this stuff into this configuration table" would be my approach.


but you can use dbms_utility to retrieve bdump without any grants.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:3307613572466#3325972144548 <code>

A reader, June 01, 2005 - 12:10 pm UTC

Is there a way to capture data of more than 1,000,000 characters either to the screen or a file on the client machine? spool and dbms_output won't work because of the buffer limit. I know utl_file works on the server. Is there something similar that works on the client?

Thanks for your help.

Tom Kyte
June 01, 2005 - 1:49 pm UTC

spool works fine? spool can create multi-megabyte files easily.
see
</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

A reader, June 01, 2005 - 2:56 pm UTC

I'm sorry for not being too clear about what I asked. Please look at the simple script below:

set serveroutput on format word_wrap;
exec dbms_output.enable(1000000);
declare
s_text varchar2(2000);
n number := 0;
begin
while true loop
n := n + 1;
s_text := rpad('X', 238, 'a') || 'X';
dbms_output.put_line(n || ' - ' || s_text);
end loop;
end;
/

When run in SQLPlus, it gives the error:

declare
*
ERROR at line 1:
ORA-20000: ORU-10027: buffer overflow, limit of 1000000 bytes
ORA-06512: at "SYS.DBMS_OUTPUT", line 91
ORA-06512: at "SYS.DBMS_OUTPUT", line 58
ORA-06512: at line 8

How can I display more than the limit?

Thanks.


Tom Kyte
June 01, 2005 - 5:19 pm UTC

by not using dbms_output. there are many ways not to use it

pipelined function pops into mind.


select * from table( your_function_that_pipes_text_to_display_here() );

for example.

But you'd have to tell us what you are trying to do, if it is to dump a table to a file, spool is what you want.

Thanks for the tip Tom!

Flavio, June 01, 2005 - 5:11 pm UTC

I wonder why I am always overlooking dbms_utility...

Bye,
Flavio


Error in using UTL_FILE

AV, June 03, 2005 - 10:53 am UTC

I am using the following code in Oracle 9i.

declare
output utl_file.file_type;
.
.
begin
output := utl_file.fopen( 'c:', '7427.txt', 'w',32000 );
.
.
utl_file.put(output, "some text");

I am getting the following error:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at line 32

I am running the following PL/SQL script through SQLPlus, and would like the output sent to the machine on which I am running the script from. Is this possible?

Tom Kyte
June 03, 2005 - 12:15 pm UTC

won't happen. plsql runs in the server, plsql and utl_file write only to the disks on the server. You'd have to use SQLPlus things to write to the local system (like spool)

More Info

AV, June 03, 2005 - 4:05 pm UTC

Thanks. That explains the error.

In having an issue writting to the flat file in the case below.

SET SERVEROUTPUT ON size 100000;
spool c:\7427_Output.txt

begin
.
.
while loop1%found loop
while loop2%found loop
end loop;
dbms_output.put_line("Updated blah, blah, blah");
end loop;

In the following case, the text gets to the console but not to the file. However, if I did the same from within the inner loop, then it goes to the console. What am I doing wrong?



Update to the description from above.

AV, June 03, 2005 - 4:07 pm UTC

I meant to say that if I added the output line from within the inner loop then it goes to both the console as well as the flat file.

Tom Kyte
June 03, 2005 - 5:31 pm UTC

not that i don't believe you but.....

lets see a real example, the above isn't real code. it won't matter WHERE the dbms_output is, only IF the dbms_output is executed.

I will use it!

Rafael Martinez, June 15, 2005 - 6:34 pm UTC

is exactly what I was looking for.

Thanks.

Ravi, July 04, 2005 - 1:49 pm UTC

Is UTL_FILE output serialized, if multiple sessions are appending the same file using UTL_FILE, is the write in a first come first written process.

If yes, is this an argument against using UTL_FILE to write out log /audit from PL/SQL programs, the ones who do it concurrently?

Tom Kyte
July 04, 2005 - 2:21 pm UTC

it would be up to you to serialize the access to the file via dbms_lock or some other technique.


This would be an argument against using a SINGLE FILE, not utl_file in general. It would apply to any logging technique whereby multiple writers tried to use the same file simultaneously. You would have to serialize:

open
seek end
write
close


and opening a file is expensive.

Piotro, July 05, 2005 - 5:33 am UTC

Hi Tom

I've noticed, that if I write to then new file

utl_file.fopen(...,'w');
utl_file.put(...);
utl_file.close(...);

an extra chr(10) is added at the end of that file.
Is it possible to avoid it?

Tom Kyte
July 05, 2005 - 7:44 am UTC

in 10g, using the new "wb" mode, yes:

ops$tkyte@ORA10G> create or replace directory MY_DIR as '/tmp'
  2  /
 
Directory created.
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2      l_output        utl_file.file_type;
  3  begin
  4      l_output := utl_file.fopen( 'MY_DIR', 'test.dat', 'w' );
  5      utl_file.put( l_output, 'hello world' );
  6      utl_file.fclose( l_output );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> host od -c /tmp/test.dat
0000000   h   e   l   l   o       w   o   r   l   d  \n
0000014
 
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G>
ops$tkyte@ORA10G> declare
  2      l_output        utl_file.file_type;
  3  begin
  4      l_output := utl_file.fopen( 'MY_DIR', 'test.dat', 'wb' );
  5      utl_file.put_raw( l_output, utl_raw.cast_to_raw('hello world') );
  6      utl_file.fclose( l_output );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
ops$tkyte@ORA10G> host od -c /tmp/test.dat
0000000   h   e   l   l   o       w   o   r   l   d
0000013


(but you might have characterset issues to deal with).  In 9i, utl_file is "a text thing" - the raw IO routines still are working on a text file and the newline is added. 

what about for LOTS of columns?

A reader, July 20, 2005 - 10:58 am UTC

I've got requirement for an extract that has 140 columns, some of which are quite wide. I normally just use SQL*Plus to spool out files, but what to do if the linesize is (much) greater than the 32767 limitation in SQL*Plus?

Thanks again

Tom Kyte
July 21, 2005 - 7:23 am UTC

</code> http://asktom.oracle.com/~tkyte/flat/index.html <code>

you can use pro*c, you could do something in java. plsql is out (32k limit).

I have a pro*c app there for you to start with if interested.

security concern about the directory object

Jianhui, July 21, 2005 - 3:24 pm UTC

Hi Tom,
When a directory is created, anyone who has read privilege granted actually can see the files or directories as what oracle operating system user can see. Isn't it a big problem?

For example, developer Tom has an unix account in the same box where Oracle server runs, and Tom also has privilege to access the external directory called EXTDIR, which points to
/home/tom/shared. Then Tom can create a symbolic link to oracle owned file systems, such as $ORACLE_HOME/xxx, then Tom can see those files within the database by UTL_FILE or DBMS_LOB as the same privileges of OS user oracle, while they were not permitted to be seen by Tom on OS level. And virtually, Tom becomes sysdba now, he can read/write all file systems owned by OS user oracle. It seems scary to use DIRECTORY object.

Sincerely


Tom Kyte
July 21, 2005 - 5:13 pm UTC

if you GRANT someone ACCESS to SOMETHING?

don't grant it?

but, seems to me that you have a problem here at the OS level -- scary is when you don't understand. But you do, so you can obviously implement with the tools correctly no? (yes).

Why would a developer have an account on the server?

Why would the people in charge let them have a directory object pointing to their private area?

If you over grant, if you don't consider things -- everything is "scary"

Using your logic, I could make everything "scary".



directory object and security

Jianhui, July 22, 2005 - 4:20 pm UTC

Tom,
Developers need to load data from flat files into database via external tables, they need to have write access to the OS directory in order to ftp or cp files there, even you can create directory object under oracle user's file system, you'll have to chmod o+rw to developers unix IDs.

That's why developers need unix IDs, need access to directory objects. The example in previous thread was an extreame scenario(points to developer's private area), but you 'll have to give them read and write. Otherwise, DBAs have to ftp or cp those files per developer's request. Besides, even we can elimate using developer's ID, we have to use application's unix ID, similar as well.

Best Regards,

Tom Kyte
July 22, 2005 - 6:49 pm UTC

and with ftp do they need access or visibility to anything else?
can they have ftp access without system access?
should a developer be able to read/execute the Oracle home directory?

I'll reiterate -- anything can be scary. GRANT SELECT is scary. GRANT CREATE SESSION is scary.

You can secure anything.

And normally, no developers won't be ftp'ing files to do external tables, that is generally in someone elses domain of control.

And if you used nfs and cp -- then you really have no worries.


Create Directory compatibility

veera, January 24, 2006 - 4:05 pm UTC

Is Create Directory backward compatible?

We have so many UTL_DIR's in our init.ora, and we have all procedures reading and writing the old way i.e putting the full path in the FOPEN

v_back_file_handle := UTL_FILE.FOPEN('/test/oracle/files',
'junk.txt',
'w',
2000);

The above FOPEN works with UTL_DIR way. But does not work with CREATE DIRECTORY way.

But with CREATE DIRECTORY we need to code the Directory_Name but not Directory_Path.

CREATE DIRECTORY foo as '/test/oracle/files';
GRANT READ ON DIRECTORY foo to public;
GRANT WRITE ON DIRECTORY foo to public;

v_back_file_handle := UTL_FILE.FOPEN('foo',
'junk.txt',
'w',
2000);

Is there any way creating existing UTL_DIR to Create Directory and using the existing procedures without changing the location parameter from the Directort_Path to Directory_Name

Tom Kyte
January 24, 2006 - 8:59 pm UTC

if you use platform dependent paths "/test/oracle/files", you will be using utl_file_dir.

If you use the directory name - 'FOO' - then you can use a directory object. It is not a matter of "backwards" compatibility - a directory name is turned into a path, a path is not turned into a directory name.

New Line Character

Nishma Jose, January 30, 2006 - 7:42 am UTC

How to add a new line character in a PL/SQL varchar variable.
eg: v1 := 'Millenium \n Towers';

result should be as shown below
-------------------------------

Millenium
Towers

Tom Kyte
January 30, 2006 - 9:04 am UTC

depends on your OS, it is either

chr(10)

or

chr(13) || chr(10)


dbms_output.put_line( replace( v1, '\n', chr(10) ) );

for example

How to generate a text file for whole schema ?

Raman, March 28, 2006 - 11:20 am UTC

Hello Tom,

I have been following this thread, and I don't get it any where how to generate a text file for whole schema in oracle 9.2.0.4 database, is there any mechanism or missed your thread?

Please help me.

thanks,
-Raman

Tom Kyte
March 28, 2006 - 7:59 pm UTC

you'd have to do it for a table at a time (script it)

Raman, March 28, 2006 - 12:40 pm UTC

Sorry Tom, that was a stupid question ....
I was supposed to ask like this .....

Is there any mechanism to produce CSV files for whole schema ?

A reader, March 29, 2006 - 12:27 pm UTC



I have used OEM 9i to produce txt/csv files ....

extract data from entire schema or from selected tables

Lonnie, April 11, 2006 - 1:06 am UTC

We are using fastreader to unload oracle tables into ascii text for fast backup and archiving. Fastreader is able to extract selected tables or all tables for entire schema owner into text or pipe. By the way fastreader is really much faster than UTL_FILE. It is third party tool though: www.wisdomforce.com

Tom Kyte
April 11, 2006 - 2:23 pm UTC

it is funny how these "fastreader" ads that keep popping up from time to time

a) use an ip address that isn't associated with a company
b) yet they all appear to be from "a company"
c) is used by different names "Lonnie", "Slobo", "Gordon"
d) from different parts of the world! (this ip address really gets around) Canada, Denver, New Jersey....


please stop - I'll just be deleting them in the future otherwise. No problem suggesting a tool for a problem, but this is bordering on "advertising"


ORA-20000 & ORA-06512

A reader, April 11, 2006 - 8:55 am UTC

Hello Tom,

Not sure what are we doing wrong here.

We have following execption defined in the procedure:

EXCEPTION

WHEN UTL_FILE.INVALID_PATH THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Invalid Path');

WHEN UTL_FILE.INVALID_OPERATION THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Invalid Operation');

WHEN UTL_FILE.INVALID_MODE THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Invalid Mode');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Invalid Filehandle');

WHEN UTL_FILE.READ_ERROR THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Read Error');

WHEN UTL_FILE.WRITE_ERROR THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Write Error');

WHEN UTL_FILE.INTERNAL_ERROR THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Internal Error');

WHEN OTHERS THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> Read_source_file ' || SQLERRM);

END test_read ;


Basically we are executing the procedure and from a box and trying to read datafile which is on other server. While executing the procedure we got following error

BEGIN
*
ERROR at line 1:
ORA-20000: -20000ORA-20000: J00003OF Main Procedure ERROR ->ORA-20000:
J00003OF
ERROR -> File Invalid Operation on
/u03/users/ofsa/ofsatest/oracle_utl_data/cbssplit.dat
ORA-06512: at line 11

As per my understanding when we are using UTL_FILE, file /u03/users/ofsa/ofsatest/oracle_utl_data/cbssplit.dat should reside on the server where databse is running. Infact /u03/users/ofsa/ofsatest/oracle_utl_data/cbssplit.dat file is on different box.

Is this the reason of getting the error?

File has following permissions:

-rw-r--r--

Any suggestion why are we getting error?

Cheers,

Tom Kyte
April 11, 2006 - 4:09 pm UTC

if the file IS NOT accessible to the oracle server process - running on the machine with the oracle software - that file is NOT ACCESSIBLE.

you don't mention what operation you are trying to do either.

but, if the file isn't on the database servers set of accessible filesystems, that file isn't going to be accessed by utl_file

ORA-20000 & ORA-06512

A reader, April 12, 2006 - 8:44 am UTC

Hello Tom,

Thanks for the reply.

Sorry as I provided half information.

Actually we were reading from the file,cbssplit.dat, and were trying to load it contains into database.
Now, /u03/users/ofsa/ofsatest/oracle_utl_data file system is mounted on one box and database is running on another box. BUT from the box where database is running I, as Oracle super user for UNIX, could read the file(Though it is remotely mounted on different server).
We have /u03/users/ofsa/ofsatest/oracle_utl_data directory defined in UTL_FILE init.ora parameter.

In first instance I thought, the file was corrupt but my procedure gives me error while reading the file (please see exception defined in procedure in last post so it was able to open the file.

I think that was due to slowness of network as file is 300 MB, Oracle was not able to transfer data "quickly" enough as we were reading from remotely mounted file system.

Have you ever experienced such problem before?

(I know it's hard to say YES, but a good "guess" would do!)

Thanks for your all support.

Cheers,



Tom Kyte
April 12, 2006 - 9:21 am UTC

I am getting fairly confused here at this point.

OK, you have an NFS mounted file system (assumption). Oracle would be able to see that normally.

Now, what I don't understand is are you observing

a) invalid operation, error, no go OR
b) poor performance



ORA-20000 & ORA-06512

A reader, April 12, 2006 - 11:19 am UTC

Hello Tom,
Sorry for the confusion.

I think (Could be wrong), I received "Invalid operation" due to poor performance (as Oracle could not read file fast!) but procedure doesn't get finished (It was deemed to read data from the file, from NFS mounted file system, and was deemed to load data into database!)

Cheers,

Tom Kyte
April 12, 2006 - 11:25 am UTC

invalid operation means you could not perform the requested operation - it is not a performance thing.

so, what line of code is getting this.

How to change the permission/owner of the file created by utl_file?

Marcio, April 18, 2006 - 11:08 am UTC

I've learned this about utl_file over here
</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/whatsnew.htm#970251 <code>
<ctrl-f> "Enhanced UTL_FILE package"

<quote>
The UTL_FILE package has a number of new functions for performing popular file operations. You can seek, auto-flush, read and write binary data, delete files, change file permissions, and more. You should begin using the CREATE DIRECTORY statement (using double quotation marks around any lowercase names), rather than the UTL_FILE_DIR initialization parameter.
</quote>

Is it possible to create a file and get it away from oracle user owner/permission?

Regards,


Tom Kyte
April 18, 2006 - 2:23 pm UTC

using a java stored procedure - you would be able to change the permissions but changing ownership is typically a "privileged" (root) sort of operation.



Sample of changing permission

Marcio, April 20, 2006 - 5:06 pm UTC

Do you have any example of utl_file changing permission of the file? Say the umask of oracle is 022 and I want to change file's permission to 777, how would be?

Tom Kyte
April 20, 2006 - 5:46 pm UTC

"using a JAVA STORED PROCEDURE..."

not using utl_file. utl_file doesn't have a "chmod" like routine.

Is the manual wrong?

Marcio, April 20, 2006 - 6:11 pm UTC

Did I interpret wrong this?

</code> http://download-west.oracle.com/docs/cd/B10501_01/appdev.920/a96590/whatsnew.htm#970251 <code>
<ctrl-f> "Enhanced UTL_FILE package"

...
You can seek, auto-flush, read and write binary data, delete files, change file permissions, and more. You should begin using the CREATE DIRECTORY statement
...

Does that "change file permissions" has nothing to do with utl_file? I do not understand!

I thought you suggested java stored procedure for change *owner* not for permission.

Regards,

Tom Kyte
April 21, 2006 - 7:20 am UTC

I do not think you misinterpreted that, rather that "it" is in error. There is no API to change permissions.

quote:

... using a java stored procedure - you would be able to change the permissions ...


and then I said "but changing ownership is typically a ROOT operation (and hence you won't be doing it)"

ORA-20000 & ORA-06512

A reader, April 21, 2006 - 8:09 am UTC

Hello Tom,

Sorry for getting back to you late.

Following code generate error:


<<Read_File_Loop>>
LOOP
BEGIN

v_cntr := v_cntr + 1;

-- Used to partially load a file, if the full file is to be loaded
-- set c_load_limit to a large number
if v_cntr >= C_load_limit then
EXIT;
end if;

-- read line
utl_file.get_line(v_filehandle,v_fileline);

-- Retrieve values from source file
----------------------------------------------------------------------------

v_T_ACCOUNT_NUMBER := RTRIM(SUBSTR(v_fileline, 1, 11)) ;
v_T_DSE_ID_NUMBER := RTRIM(SUBSTR(v_fileline, 12, 13)) ;
v_T_DOMICILE_BUS_UNIT := RTRIM(SUBSTR(v_fileline, 25, 4)) ;
v_T_CUR_BOOK_BAL := LTRIM(SUBSTR(v_fileline, 29, 17)) ;
v_T_AVG_CR_BAL := LTRIM(SUBSTR(v_fileline, 46, 17)) ;
v_T_AVG_DR_BAL := LTRIM(SUBSTR(v_fileline, 63, 17)) ;
v_T_AVG_BOOK_BAL := LTRIM(SUBSTR(v_fileline, 80, 17)) ;
v_T_DEBIT_INT_AMT := LTRIM(SUBSTR(v_fileline, 97, 17)) ;
v_T_CREDIT_INT_AMT := LTRIM(SUBSTR(v_fileline, 114, 17)) ;
v_T_ACCOUNT_OPEN_DATE := RTRIM(SUBSTR(v_fileline, 131, 8)) ;
v_T_ORG_UNIT_ID := RTRIM(SUBSTR(v_fileline, 139, 5)) ;
v_T_OPERATIONAL_PROD := RTRIM(SUBSTR(v_fileline, 144, 10)) ;
v_T_MATURITY_DATE := RTRIM(SUBSTR(v_fileline, 154, 8)) ;
v_T_ORIGINATION_DATE := RTRIM(SUBSTR(v_fileline, 162, 8)) ;
v_T_IMPAIRED_IND := RTRIM(SUBSTR(v_fileline, 170, 1)) ;
v_T_ISSUE_DATE := RTRIM(SUBSTR(v_fileline, 171, 8)) ;
v_T_SECURITISED_IND := RTRIM(SUBSTR(v_fileline, 179, 1)) ;
v_T_PRODUCT_TYPE := RTRIM(SUBSTR(v_fileline, 180, 5)) ;
v_T_FXD_VAR_INTEREST_TYPE := RTRIM(SUBSTR(v_fileline, 185, 1)) ;
v_T_TERM := RTRIM(SUBSTR(v_fileline, 186, 6)) ;
v_T_TOTAL_MONTHLY_INSTALMENT := LTRIM(SUBSTR(v_fileline, 192, 17)) ;
v_T_TERM_INSTALMENT_FREQ_CODE := RTRIM(SUBSTR(v_fileline, 209, 5)) ;
v_T_RECON_SET_OFF_CD := RTRIM(SUBSTR(v_fileline, 214, 3)) ;
v_T_NEXT_LIMIT_EXP_DATE := RTRIM(SUBSTR(v_fileline, 217, 8)) ;
v_T_ANZSIC_CD := RTRIM(SUBSTR(v_fileline, 225, 4)) ;
v_T_ORG_BOOK_BAL := LTRIM(SUBSTR(v_fileline, 229, 17)) ;
v_T_NOTIONAL_DEBIT_INT_AMT := LTRIM(SUBSTR(v_fileline, 246, 17)) ;
v_T_MGMT_BUSINESS_UNIT_NO := RTRIM(SUBSTR(v_fileline, 263, 4)) ; --v1.12
v_T_limit := LTRIM(SUBSTR(v_fileline, 267, 17)) ; --v1.13
v_T_LIMIT_TYPE_CODE := RTRIM(SUBSTR(v_fileline, 284, 4)) ; --v1.14
v_T_EFF_INTR := RTRIM(SUBSTR(v_fileline, 288, 8)) ;-- v2.00
v_T_BRK_IND := RTRIM(SUBSTR(v_fileline, 296, 1)) ;-- v2.00
v_T_DRWD_ROLL_SWTC_CD := RTRIM(SUBSTR(v_fileline, 297, 2)) ;-- v2.03
v_T_RISK_GRADE_CODE := RTRIM(SUBSTR(v_fileline, 299, 3)) ;-- v2.05
v_T_INT_CUST_EXP_DATE := RTRIM(SUBSTR(v_fileline, 302, 8)) ;-- v2.08
v_t_securitised_series_no := RTRIM(SUBSTR(v_fileline, 310, 4)) ; --v2.08
v_t_set_of_books_code := LTRIM(SUBSTR(v_fileline, 314, 2)) ; --v3.03
v_t_limit_eff_date := RTRIM(SUBSTR(v_fileline, 316, 8)) ; --v3.05

----------------------------------------------------------------------------
v_IN_ACCOUNT_NUMBER := TO_NUMBER( RTRIM(SUBSTR(v_fileline, 1, 11))) ;
v_IN_DSE_ID_NUMBER := RTRIM(SUBSTR(v_fileline, 12, 13)) ;
v_IN_DOMICILE_BUS_UNIT := TO_NUMBER( RTRIM(SUBSTR(v_fileline, 25, 4))) ;
v_IN_CUR_BOOK_BAL := TO_NUMBER(LTRIM(SUBSTR(v_fileline, 29, 17))) ;
v_IN_AVG_CR_BAL := TO_NUMBER(LTRIM(SUBSTR(v_fileline, 46, 17))) ;
v_IN_AVG_DR_BAL := TO_NUMBER(LTRIM(SUBSTR(v_fileline, 63, 17))) ;
v_IN_AVG_BOOK_BAL := TO_NUMBER(LTRIM(SUBSTR(v_fileline, 80, 17))) ;
v_IN_DEBIT_INT_AMT := TO_NUMBER(LTRIM(SUBSTR(v_fileline, 97, 17))) ;
v_IN_CREDIT_INT_AMT := TO_NUMBER(LTRIM(SUBSTR(v_fileline, 114, 17))) ;
v_IN_ORG_UNIT_ID := TO_NUMBER( RTRIM(SUBSTR(v_fileline, 139, 5))) ;
v_IN_OPERATIONAL_PROD := RTRIM(SUBSTR(v_fileline, 144, 10)) ;
v_IN_IMPAIRED_IND := RTRIM(SUBSTR(v_fileline, 170, 1)) ;
v_IN_SECURITISED_IND := RTRIM(SUBSTR(v_fileline, 179, 1)) ;
v_IN_PRODUCT_TYPE := RTRIM(SUBSTR(v_fileline, 180, 5)) ;
v_IN_FXD_VAR_INTEREST_TYPE := RTRIM(SUBSTR(v_fileline, 185, 1)) ;
v_IN_TERM := TO_NUMBER( RTRIM(SUBSTR(v_fileline, 186, 6))) ;
v_IN_TOTAL_MONTHLY_INSTALMENT := TO_NUMBER(LTRIM(SUBSTR(v_fileline, 192, 17))) ;
v_IN_TERM_INSTALMENT_FREQ_CODE := RTRIM(SUBSTR(v_fileline, 209, 5)) ;
v_IN_RECON_SET_OFF_CD := RTRIM(SUBSTR(v_fileline, 214, 3)) ;
v_IN_ANZSIC_CD := RTRIM(SUBSTR(v_fileline,225,4)) ;
v_IN_ORG_BOOK_BAL := TO_NUMBER( LTRIM(SUBSTR(v_fileline, 229, 17))) ;
v_IN_NOTIONAL_DEBIT_INT_AMT := TO_NUMBER( LTRIM(SUBSTR(v_fileline, 246 ,17))) ;
v_IN_MGMT_BUSINESS_UNIT_NO := RTRIM(SUBSTR(v_fileline, 263, 4)) ; --v1.12
v_IN_limit := TO_NUMBER( RTRIM(SUBSTR(v_fileline, 267, 17))) ; --v1.13
v_IN_LIMIT_TYPE_CODE := RTRIM(SUBSTR(v_fileline, 284, 4)) ; --v1.14
v_IN_EFF_INTR := TO_NUMBER( RTRIM(SUBSTR(v_fileline, 288, 8))) ;-- v2.00
v_IN_BRK_IND := RTRIM(SUBSTR(v_fileline, 296, 1)) ;-- v2.00
v_IN_DRWD_ROLL_SWTC_CD := TO_NUMBER( RTRIM(SUBSTR(v_fileline, 297, 2))) ;-- v2.03
v_IN_RISK_GRADE_CODE := RTRIM(SUBSTR(v_fileline, 299, 3)) ;-- v2.05
v_IN_securitised_series_no := RTRIM(SUBSTR(v_fileline, 310, 4)) ; --v2.08
v_IN_SET_OF_BOOKS_CODE := TO_NUMBER(v_T_SET_OF_BOOKS_CODE) ; --v3.03
----------------------------------------------------------------------------

IF length(nvl(v_T_ACCOUNT_OPEN_DATE,0)) <> 8 THEN
v_IN_ACCOUNT_OPEN_DATE := NULL ;
ELSE
v_IN_ACCOUNT_OPEN_DATE := TO_DATE(v_T_ACCOUNT_OPEN_DATE,'YYYYMMDD') ;
END IF;

IF length(nvl(v_T_MATURITY_DATE,0)) <> 8 THEN
v_IN_MATURITY_DATE := NULL ;
ELSE
v_IN_MATURITY_DATE := TO_DATE(v_T_MATURITY_DATE,'YYYYMMDD') ;
END IF;

IF length(nvl(v_T_ORIGINATION_DATE,0)) <> 8 THEN
v_IN_ORIGINATION_DATE := NULL ;
ELSE
v_IN_ORIGINATION_DATE := TO_DATE(v_T_ORIGINATION_DATE,'YYYYMMDD') ;
END IF;

IF length(nvl(v_T_ORIGINATION_DATE,0)) <> 8 THEN
v_IN_ORIGINATION_DATE := NULL ;
ELSE
v_IN_ORIGINATION_DATE := TO_DATE(v_T_ORIGINATION_DATE,'YYYYMMDD') ;
END IF;

IF length(nvl(v_T_ISSUE_DATE,0)) <> 8 THEN
v_IN_ISSUE_DATE := NULL ;
ELSE
v_IN_ISSUE_DATE := TO_DATE(v_T_ISSUE_DATE,'YYYYMMDD') ;
END IF;

IF length(nvl(v_T_NEXT_LIMIT_EXP_DATE,0)) <> 8 THEN
v_IN_NEXT_LIMIT_EXP_DATE := NULL ;
ELSE
v_IN_NEXT_LIMIT_EXP_DATE := TO_DATE(v_T_NEXT_LIMIT_EXP_DATE,'YYYYMMDD') ;
END IF;

IF length(nvl(v_T_INT_CUST_EXP_DATE,0)) <> 8 THEN
v_IN_INT_CUST_EXP_DATE := NULL ;
ELSE
v_IN_INT_CUST_EXP_DATE := TO_DATE(v_T_INT_CUST_EXP_DATE,'YYYYMMDD') ;
END IF;

IF length(nvl(v_T_LIMIT_EFF_DATE,0)) <> 8 THEN
v_IN_LIMIT_EFF_DATE := NULL ;
ELSE
v_IN_LIMIT_EFF_DATE := TO_DATE(v_T_LIMIT_EFF_DATE,'YYYYMMDD') ;
END IF;


-- Debugging messages
IF debug_on then
dbms_output.put_line('---> DATA LINE NUMBER : ' || TO_CHAR(v_cntr) ) ;
dbms_output.put_line('v_T_ACCOUNT_NUMBER : ' || v_T_ACCOUNT_NUMBER );
dbms_output.put_line('v_T_DSE_ID_NUMBER : ' || v_T_DSE_ID_NUMBER );
dbms_output.put_line('v_T_DOMICILE_BUS_UNIT : ' || v_T_DOMICILE_BUS_UNIT );
dbms_output.put_line('v_T_CUR_BOOK_BAL : ' || v_T_CUR_BOOK_BAL );
dbms_output.put_line('v_T_AVG_CR_BAL : ' || v_T_AVG_CR_BAL );
dbms_output.put_line('v_T_AVG_DR_BAL : ' || v_T_AVG_DR_BAL );
dbms_output.put_line('v_T_AVG_BOOK_BAL : ' || v_T_AVG_BOOK_BAL);
dbms_output.put_line('v_T_DEBIT_INT_AMT : ' || v_T_DEBIT_INT_AMT);
dbms_output.put_line('v_T_CREDIT_INT_AMT : ' || v_T_CREDIT_INT_AMT );
dbms_output.put_line('v_T_ACCOUNT_OPEN_DATE : ' || v_T_ACCOUNT_OPEN_DATE );
dbms_output.put_line('v_T_ORG_UNIT_ID : ' || v_T_ORG_UNIT_ID );
dbms_output.put_line('v_T_OPERATIONAL_PROD : ' || v_T_OPERATIONAL_PROD);
dbms_output.put_line('v_T_MATURITY_DATE : ' || v_T_MATURITY_DATE );
dbms_output.put_line('v_T_ORIGINATION_DATE : ' || v_T_ORIGINATION_DATE);
dbms_output.put_line('v_T_IMPAIRED_IND : ' || v_T_IMPAIRED_IND );
dbms_output.put_line('v_T_ISSUE_DATE : ' || v_T_ISSUE_DATE);
dbms_output.put_line('v_T_SECURITISED_IND : ' || v_T_SECURITISED_IND );
dbms_output.put_line('v_T_PRODUCT_TYPE : ' || v_T_PRODUCT_TYPE);
dbms_output.put_line('v_T_FXD_VAR_INTEREST_TYPE : ' || v_T_FXD_VAR_INTEREST_TYPE );
dbms_output.put_line('v_T_TERM : ' || v_T_TERM );
dbms_output.put_line('v_T_TOTAL_MONTHLY_INSTALMENT : ' || v_T_TOTAL_MONTHLY_INSTALMENT);
dbms_output.put_line('v_T_TERM_INSTALMENT_FREQ_CODE : ' || v_T_TERM_INSTALMENT_FREQ_CODE);
dbms_output.put_line('v_T_RECON_SET_OFF_CD : ' || v_T_RECON_SET_OFF_CD );
dbms_output.put_line('v_T_NEXT_LIMIT_EXP_DATE : ' || v_T_NEXT_LIMIT_EXP_DATE );
dbms_output.put_line('v_T_ANZSIC_CD : ' || v_T_ANZSIC_CD );
dbms_output.put_line('v_T_ORG_BOOK_BAL : ' || v_T_ORG_BOOK_BAL );
dbms_output.put_line('v_T_NOTIONAL_DEBIT_INT_AMT : ' || v_T_NOTIONAL_DEBIT_INT_AMT );
dbms_output.put_line('v_T_mgmt_business_unit_no :' || v_IN_MGMT_BUSINESS_UNIT_NO ); --v1.12
dbms_output.put_line('v_T_limit : ' || v_T_limit ); --v1.13
dbms_output.put_line('v_T_limit_type_code : ' || v_T_limit_type_code ); -- v1.14
dbms_output.put_line('v_T_EFF_INTR : ' || v_T_EFF_INTR); -- v2.00
dbms_output.put_line('v_T_BRK_IND : ' || v_T_BRK_IND); -- v2.00
dbms_output.put_line('v_T_DRWD_ROLL_SWTC_CD : ' || v_T_DRWD_ROLL_SWTC_CD); -- v2.03
dbms_output.put_line('v_T_RISK_GRADE_CODE : ' || v_T_RISK_GRADE_CODE); -- v2.05
dbms_output.put_line('v_T_INT_CUST_EXP_DATE : ' || v_T_INT_CUST_EXP_DATE); -- v2.08
dbms_output.put_line('v_T_securitised_series_no : ' || v_T_securitised_series_no ); --v2.09
dbms_output.put_line('v_T_SET_OF_BOOKS_CODE : ' || v_T_set_of_books_code ); --v3.03
dbms_output.put_line('v_T_LIMIT_EFF_DATE : ' || v_T_limit_eff_date); --v3.05
END IF;

-------------------------------------------------------------
-- MAP VALUES TO OUTPUT FILE VARIABLES
-------------------------------------------------------------

IF debug_on then
dbms_output.put_line('Setting instrument table code');
END IF;

-- look up ofdm_data-by-product using operational_prod return instrument_table_code
-- note that this lookup is done before the CUR_BOOK_VAL is set to a positve number
-- since the lookup depends on the value of CUR_BOOK_VAL for the DR or CR flag
V_INSTRUMENT_TABLE_CODE :=Get_Data_By_Product_ak('INSTRUMENT_TABLE_CODE', v_IN_OPERATIONAL_PROD, v_current_date);

IF debug_on then
dbms_output.put_line('1. Set Instrument Table Business Rules');
END IF;

Set_Inst_Bus_Rules_ak;

IF debug_on then
dbms_output.put_line('2. Set Global Business Rules');
END IF;

Set_Global_Bus_Rules_ak;

IF debug_on then
dbms_output.put_line('3. Set_Instrument_Table_Defaults');
END IF;

Set_Instrument_Table_Defaults;

IF debug_on then
dbms_output.put_line('4. Set_Global_Defaults');
END IF;

Set_Global_Defaults;

IF debug_on then
dbms_output.put_line('5. Set_Defaults_Overwrite');
END IF;

Set_Defaults_Overwrite; -- v2.06

IF debug_on then
dbms_output.put_line('Set_Output_line');
END IF;

Set_Output_line;

-------------------------------------------------------------
-- WRITE OUTPUT LINE TO APPROPRIATE OUTPUT FILE NAME
-------------------------------------------------------------

Set_Output_File_Name; -- Works out file name by setting value of v_file_number

-- write outoput line to output file
IF V_FILE_NUMBER = 1 THEN
utl_file.put_line(v_filehandle_out1, v_fileline_out);
utl_file.fflush(v_filehandle_out1);
ELSIF V_FILE_NUMBER = 2 THEN
utl_file.put_line(v_filehandle_out2, v_fileline_out);
utl_file.fflush(v_filehandle_out2);
ELSIF V_FILE_NUMBER = 3 THEN
utl_file.put_line(v_filehandle_out3, v_fileline_out);
utl_file.fflush(v_filehandle_out3);
ELSIF V_FILE_NUMBER = 4 THEN
utl_file.put_line(v_filehandle_out4, v_fileline_out);
utl_file.fflush(v_filehandle_out4);
ELSIF V_FILE_NUMBER = 5 THEN
utl_file.put_line(v_filehandle_out5, v_fileline_out);
utl_file.fflush(v_filehandle_out5);
ELSIF V_FILE_NUMBER = 6 THEN
utl_file.put_line(v_filehandle_out6, v_fileline_out);
utl_file.fflush(v_filehandle_out6);
ELSIF V_FILE_NUMBER = 7 THEN
utl_file.put_line(v_filehandle_out7, v_fileline_out);
utl_file.fflush(v_filehandle_out7);
ELSIF V_FILE_NUMBER = 8 THEN
utl_file.put_line(v_filehandle_out8, v_fileline_out);
utl_file.fflush(v_filehandle_out8);
END IF;

EXCEPTION
WHEN NO_DATA_FOUND THEN
-- UTL_FILE.GET_LINE will raise this exception when EOF
EXIT;
WHEN OTHERS THEN
Close_all_files ;
dbms_output.put_line('---> DATA LINE NUMBER : ' || TO_CHAR(v_cntr) ) ;
dbms_output.put_line('v_T_ACCOUNT_NUMBER : ' || v_T_ACCOUNT_NUMBER );
dbms_output.put_line('v_T_DSE_ID_NUMBER : ' || v_T_DSE_ID_NUMBER );
dbms_output.put_line('v_T_DOMICILE_BUS_UNIT : ' || v_T_DOMICILE_BUS_UNIT );
dbms_output.put_line('v_T_CUR_BOOK_BAL : ' || v_T_CUR_BOOK_BAL );
dbms_output.put_line('v_T_AVG_CR_BAL : ' || v_T_AVG_CR_BAL );
dbms_output.put_line('v_T_AVG_DR_BAL : ' || v_T_AVG_DR_BAL );
dbms_output.put_line('v_T_AVG_BOOK_BAL : ' || v_T_AVG_BOOK_BAL);
dbms_output.put_line('v_T_DEBIT_INT_AMT : ' || v_T_DEBIT_INT_AMT);
dbms_output.put_line('v_T_CREDIT_INT_AMT : ' || v_T_CREDIT_INT_AMT );
dbms_output.put_line('v_T_ACCOUNT_OPEN_DATE : ' || v_T_ACCOUNT_OPEN_DATE );
dbms_output.put_line('v_T_ORG_UNIT_ID : ' || v_T_ORG_UNIT_ID );
dbms_output.put_line('v_T_OPERATIONAL_PROD : ' || v_T_OPERATIONAL_PROD);
dbms_output.put_line('v_T_MATURITY_DATE : ' || v_T_MATURITY_DATE );
dbms_output.put_line('v_T_ORIGINATION_DATE : ' || v_T_ORIGINATION_DATE);
dbms_output.put_line('v_T_IMPAIRED_IND : ' || v_T_IMPAIRED_IND );
dbms_output.put_line('v_T_ISSUE_DATE : ' || v_T_ISSUE_DATE);
dbms_output.put_line('v_T_SECURITISED_IND : ' || v_T_SECURITISED_IND );
dbms_output.put_line('v_T_PRODUCT_TYPE : ' || v_T_PRODUCT_TYPE);
dbms_output.put_line('v_T_FXD_VAR_INTEREST_TYPE : ' || v_T_FXD_VAR_INTEREST_TYPE );
dbms_output.put_line('v_T_TERM : ' || v_T_TERM );
dbms_output.put_line('v_T_TOTAL_MONTHLY_INSTALMENT : ' || v_T_TOTAL_MONTHLY_INSTALMENT);
dbms_output.put_line('v_T_TERM_INSTALMENT_FREQ_CODE : ' || v_T_TERM_INSTALMENT_FREQ_CODE);
dbms_output.put_line('v_T_RECON_SET_OFF_CD : ' || v_T_RECON_SET_OFF_CD );
dbms_output.put_line('v_T_NEXT_LIMIT_EXP_DATE : ' || v_T_NEXT_LIMIT_EXP_DATE );
dbms_output.put_line('v_T_ANZSIC_CD : ' || v_T_ANZSIC_CD );
dbms_output.put_line('v_T_ORG_BOOK_BAL : ' || v_T_ORG_BOOK_BAL );
dbms_output.put_line('v_T_NOTIONAL_DEBIT_INT_AMT : ' || v_T_NOTIONAL_DEBIT_INT_AMT );
dbms_output.put_line('v_T_limit : ' || v_T_limit ); --v.1.13
dbms_output.put_line('v_T_mgmt_business_unit_no :' || v_T_MGMT_BUSINESS_UNIT_NO ); --v1.12
dbms_output.put_line('v_T_limit_type_code : ' || v_T_limit_type_code ); --v1.14
dbms_output.put_line('v_T_EFF_INTR : ' || v_T_EFF_INTR); --- v2.00
dbms_output.put_line('v_T_BRK_IND : ' || v_T_BRK_IND); --- v2.00
dbms_output.put_line('v_T_DRWD_ROLL_SWTC_CD : ' || v_T_DRWD_ROLL_SWTC_CD); -- v2.03
dbms_output.put_line('v_T_RISK_GRADE_CODE : ' || v_T_RISK_GRADE_CODE); -- v2.05
dbms_output.put_line('v_T_INT_CUST_EXP_DATE : ' || v_T_INT_CUST_EXP_DATE); -- v2.08
dbms_output.put_line('v_T_securitised_series_no : ' || v_T_securitised_series_no ); --v2.08
dbms_output.put_line('v_T_SET_OF_BOOKS_CODE : ' || v_T_set_of_books_code ); --v3.03
raise_application_error(-20000,
C_PROGRAM_NAME ||
' ERROR -> Read_source_file Read Loop ' ||
SQLERRM);

END;

Clear_variables;

END LOOP Read_File_Loop ;

Close_all_files ;

EXCEPTION

WHEN UTL_FILE.INVALID_PATH THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Invalid Path');

WHEN UTL_FILE.INVALID_OPERATION THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Invalid Operation on ' || v_data_dir || '/' || v_file_name );

WHEN UTL_FILE.INVALID_MODE THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Invalid Mode');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Invalid Filehandle');

WHEN UTL_FILE.READ_ERROR THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Read Error');

WHEN UTL_FILE.WRITE_ERROR THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Write Error');

WHEN UTL_FILE.INTERNAL_ERROR THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> File Internal Error');

WHEN OTHERS THEN
Close_all_files ;
raise_application_error(-20000, C_PROGRAM_NAME || ' ERROR -> Read_source_file ' || SQLERRM);


Please help me.

Thanks

Tom Kyte
April 21, 2006 - 8:50 am UTC

make this example SIGNIFICANTLY smaller - you never said what line of code was getting the error.

This should be TINY - remove every bit of code you can except for the part that gets the error.

test cases - all about making them tiny. Most of the time I find my own mistake easily when removing everything that is not relevant to the problem at hand.

follow up: Manual wrong.

Marcio Portes, April 21, 2006 - 11:38 am UTC

Thanks Tom for the follow up. Clear now.


yes but

Scott, May 17, 2006 - 7:18 pm UTC

I can tell from this article that you would favour logging to table rather than utl_file. However, I am still wondering whether you can clarify how much impact utl_file logging may have on system performance. I have just started working on a system where developers are writing approx 2MB/Min of debug/trace logging in the PRODUCTION code. This seems excessive to me and I am wondering whether there is a point in tuning the PL/SQL and sql when it may be that a great deal of time is being spent logging largely unecessary trace info that was mainly useful during dev.

Any advice would be greatly appreciated.

Thanks

Tom Kyte
May 18, 2006 - 10:36 am UTC

I use files for "trace/debug" information.

I use tables for audit trail, long term performance metrics.

utl_file

Babar ALi, May 22, 2006 - 3:16 am UTC

hi tom ...
how we can create text file USING utl_file on a remote machine .... i dont wannt to user create directory then define i have to set utl_file_dir .. plz guide me related this topic
regards
babar ali

Tom Kyte
May 22, 2006 - 7:50 am UTC

why not look at the other place you sort of asked this exact same question?

Further to question above about debug/trace logging

Scott Duncan, May 22, 2006 - 3:26 pm UTC


Thanks for advising on what you would do.

Developers do have a trace switch which is usually disabled in the code but currently they are also logging too much as information that is supposedly required. I am therefore curious whether you have any thoughts on likely performance impact of writing with utl_file vs logging to a table.

Thanks,

Scott

Tom Kyte
May 22, 2006 - 4:10 pm UTC

I use files for trace.

I use tables for audit trails.


I would not give much consideration to a table for "trace" data generally.


Reading Special Characters from a File.

Rajesh, July 11, 2006 - 10:50 am UTC

WE have got a procedure which opens a file and uploads the data from the file to various tables depending upon the format of the file. We have uploaded several files into the database which contains regional specific characters. The package works perfectly with several files.
But now we have got a sequential file which contains some encrypted data(ASCII characters). That file is generated from a UNIX 386 system.We have to upload this file also using the same package.

Steps we did are
1) For this we got the file format.
2) Converted the file into Line Sequential.
(a) Used dd utility in unix. But the line format of the output file was not correct. We used putty for this. Then we gave Thai Font support to Unix and changed the LANG value. Then we got the line sequential file format correct without any problem. Now every line contains 80 chacters.
3) The package which does the upload is rejecting the file because oracle calculates each record's length as 79 instead of 80.In HPUX the records length is 80 exactly.
4) The oracle code calculates the length of each record by using LENGTHB and also we use SUBSTRB for getting each data from the record in the file.
5) Now how can we come through this problem?
We are using UTL_FILE.get_LINE to read each record.
The confusion here for us is we have already uploaded several files which contains regional specific characters. Why we are getting this problem for this file.
Any problem in line sequential conversion?
Any probelm in using UTL_FILE?


Last few characters in each record are the special characters.

Eg:- Sequential File Size : 320bytes (4 records)
Line Seq, File Size : 324bytes (4 records)

These are some of the records in the file.

402004288014013513510295937070001700010923005000402000002700000 (A
400000888001000999999999916170001700000000000000402000003702800 ö^Vº<ñw
400000888001000999999999916170001700000000000000402000003702800 ö^Vº<ñw
402004288011088888888888888888888700010923005000402000004464900 -hº
¿ï]7





Tom Kyte
July 12, 2006 - 3:04 pm UTC

... contains some encrypted data(ASCII
characters). ...

oxymoron there - encrypted data is binary, ASCII data is specifically NOT binary.

Oracle is not "calculating" a file length? I don't know what you mean by that.

really issuficient data here, but I don't really see what it has to do with Oracle? You'll be reading RAW data from that file (encrypted data = RAW data, it is NOT character data) and the file is the "file", whatever you give us, we read.

UTL_FILE

sam, July 14, 2006 - 1:46 pm UTC

Tom:

Does UTL_FILE use FTP if you schedule it to run one unix machine and then instruct it to create the files on another unix machine?

Tom Kyte
July 14, 2006 - 1:58 pm UTC

how do you "instruct it" to create files on another unix machine????



utl_file

sam, July 16, 2006 - 12:50 pm UTC

Tom:
Can't you specify the location in the FOPEN procedure.

UTL_FILE.FOPEN (
location IN VARCHAR2,
filename IN VARCHAR2,
open_mode IN VARCHAR2,
max_linesize IN BINARY_INTEGER)
RETURN file_type;

or

use FCOPY to move the file from one machine to the other
fcopy
procedure fcopy (
src_location in varchar2 ,
src_filename in varchar2 ,
dest_location in varchar2 ,
dest_filename in varchar2 ,
start_line in binary_integer default,
end_line in binary_integer default
);



Tom Kyte
July 16, 2006 - 4:17 pm UTC

on THE LOCAL FILE SYSTEM - sure. absolutely.

On some other machine elsewhere in the network? No - that would be very "virus" like wouldn't it....


This works just like C's fopen
Or file open calls in Java
Or in VB
Or in whatever language.

Only the file systems visible to the program are accessible. And here the program is PLSQL run on the server. Hence, the servers file systems - what you can see on the server - that is it.

UTl_FILE

sam, July 16, 2006 - 10:27 pm UTC

Tom:

To do that, can you in the pl/sql procedure that creates the text file on machine A, run a unix shell script that FTP the file from machine A to machine B? is ther any other way in pl/sql only?

or you could try...

Chris Poole, July 18, 2006 - 5:24 am UTC

@ sam

You could try using this (free) FTP PL/SQL client I wrote,
xutl_ftp. One of the available put procedures takes a ref_cursor, looping through the result set FTP'ing it straight out to a file *on the remote server*, so there is no need for an intermediate staging file locally.

</code> http://www.chrispoole.co.uk/apps/xutlftp.htm <code>

@ Tom

Apologies if you believe this to be spam, I thought it was relevant to sams' question. Feel free to delete if you don't like :)

HTH

Chris


Alexander the ok, August 17, 2006 - 3:53 pm UTC

Hi Tom,

I can you please help me figure out why I can't get your sweet dump_csv function to work?  I'm sure I'm doing something really stupid.  

I'm on windows.  My database is on my pc so I know darn well what the file system looks like.  I've tried every combination of input strings for the directory but it still cannot find the directory. 

I have not touched a line of your function.  Cut and paste directly from above.  Here's what I get:

  1  declare
  2        l_rows    number;
  3    begin
  4        l_rows := dump_csv( 'select * from mytable',
  5                            ',',
  6                            'C:\temp',
  7                            'test.dat' );
  8        dbms_output.put_line( to_char(l_rows) ||
  9                              ' rows extracted to ascii file' );
 10*   end;
SQL> /
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at "MSWP2A_ADMIN.DUMP_CSV", line 16
ORA-06512: at line 4 

Tom Kyte
August 17, 2006 - 4:20 pm UTC

c:\temp is not in your utl_file_dir

but - don't put it there, instead do this:

create directory c_temp as 'c:\temp'
/

and pass the string 'C_TEMP' (in upper case) as the directory name. (or add c:\temp to utl_file_dir and restart the instance, but use the directory - easier, better)

Hi..

Joy, August 20, 2006 - 6:33 am UTC

This is Fine.. Now I have a question..
I want to identify unique records from a given file.. & store it in another file.. (based on particular field..)

Kindly let me know the possibilitites for this.

Thanks In advance

Tom Kyte
August 20, 2006 - 8:15 am UTC

files? or do you really mean tables?

Replacing utl_file_dir with create directory

Manish, August 22, 2006 - 9:49 am UTC

Hi Tom,
I am thinking to replace the utl_file_dir parameter in init.ora in all of our 9iR2 databases, with create directory . As this would eliminate the need to restart the database after every change in utl_file_dir.

If we have 10 dir's setup in utl_file_dir, then do we have to create those same 10 in create directory ??

In utl_file_dir we can use /oradmn1/developer/app_name/*, which would give permissions to all subfolders under this app_name, instead of specifying each sub folder, how we can do the same in create directory ??

My question is in order to implement this change the developers has to change their code, where they have to put the directory name instead of full path in their utl_file package call. Am I right ??

Is there any way to implement this change without affecting any change on developers side.

Also please let me know what other things I need to look/check before I go ahead with this.

Will Oracle desupport the use of utl_file_dir in future.

Thanks in advance

Tom Kyte
August 27, 2006 - 2:44 pm UTC

you cannot use /a/b/c/d/* to do all subfolders.  

ops$tkyte%ORA9IR2> connect / as sysdba
Connected.
ops$tkyte%ORA9IR2> alter system set utl_file_dir = '/tmp/*' scope=spfile;

System altered.

ops$tkyte%ORA9IR2> startup force
ORACLE instance started.

Total System Global Area  403772680 bytes
Fixed Size                   451848 bytes
Variable Size             369098752 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
ops$tkyte%ORA9IR2> connect /
Connected.
ops$tkyte%ORA9IR2> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /tmp/*
ops$tkyte%ORA9IR2> !mkdir /tmp/a

ops$tkyte%ORA9IR2> !mkdir /tmp/b

ops$tkyte%ORA9IR2> !chmod a+rwx /tmp/a /tmp/b

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> declare
  2          l_file utl_file.file_type;
  3  begin
  4          l_file := utl_file.fopen( '/tmp/', 'test.dat', 'w' );
  5          utl_file.fclose(l_file);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at line 4


ops$tkyte%ORA9IR2> declare
  2          l_file utl_file.file_type;
  3  begin
  4          l_file := utl_file.fopen( '/tmp/a', 'test.dat', 'w' );
  5          utl_file.fclose(l_file);
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-29280: invalid directory path
ORA-06512: at "SYS.UTL_FILE", line 18
ORA-06512: at "SYS.UTL_FILE", line 424
ORA-06512: at line 4


ops$tkyte%ORA9IR2> !ls -lag /tmp/a /tmp/b /tmp/test.dat
ls: /tmp/test.dat: No such file or directory
/tmp/a:
total 16
drwxrwxrwx   2 tkyte 4096 Aug 27 14:40 .
drwxrwxrwt  13 root  4096 Aug 27 14:40 ..

/tmp/b:
total 16
drwxrwxrwx   2 tkyte 4096 Aug 27 14:40 .
drwxrwxrwt  13 root  4096 Aug 27 14:40 ..

ops$tkyte%ORA9IR2> connect / as sysdba
Connected.
ops$tkyte%ORA9IR2> alter system set utl_file_dir = '/tmp/' scope=spfile;

System altered.

ops$tkyte%ORA9IR2> startup force
ORACLE instance started.

Total System Global Area  403772680 bytes
Fixed Size                   451848 bytes
Variable Size             369098752 bytes
Database Buffers           33554432 bytes
Redo Buffers                 667648 bytes
Database mounted.
Database opened.
ops$tkyte%ORA9IR2> connect /
Connected.
ops$tkyte%ORA9IR2> show parameter utl_file_dir

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /tmp/
ops$tkyte%ORA9IR2> declare
  2          l_file utl_file.file_type;
  3  begin
  4          l_file := utl_file.fopen( '/tmp/', 'test.dat', 'w' );
  5          utl_file.fclose(l_file);
  6  end;
  7  /

PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> !ls -lag /tmp/a /tmp/b /tmp/test.dat
-rw-rw-r--  1 ora9ir2    0 Aug 27 14:40 /tmp/test.dat

/tmp/a:
total 16
drwxrwxrwx   2 tkyte 4096 Aug 27 14:40 .
drwxrwxrwt  13 root  4096 Aug 27 14:40 ..

/tmp/b:
total 16
drwxrwxrwx   2 tkyte 4096 Aug 27 14:40 .
drwxrwxrwt  13 root  4096 Aug 27 14:40 ..



<b>what you MIGHT have accidently done was set /oradimn1/developer/app_name AND *, meaning the entire file system is open!!!!!</b>

 

UTl_FILE

sam, August 28, 2006 - 5:33 pm UTC

Tom:

How does UTL_FILE get installed in 9i. DBA installed 9i but package body is missing under SYS schema.

Does it have to do with priviledges assigned?

Tom Kyte
August 29, 2006 - 6:41 am UTC

are you sure the package body is "missing".

it would be normal for someone with execute on a package to only see the header, not the body.

if you try to use it, does it "work"?

utl_file

Sam, August 29, 2006 - 12:10 pm UTC

Tom:

yes it does not work. Here is a sample. DBA says he assigned priviledge. Does it look like it.

SQL> execute BER_CHEQUE ;
BEGIN BER_CHEQUE ; END;

*
ERROR at line 1:
ORA-04068: existing state of packages has been discarded
ORA-04067: not executed, package body "SYS.UTL_FILE" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "DEVADMIN.BER_CHEQUE", line 327
ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 1
 

Tom Kyte
August 29, 2006 - 4:21 pm UTC

and your dba isn't able to figure out what has happened?

but anyway - what's up with the "exact fetch returns...." bit there??

that would not be expected.

I would expect this:

sys%ORA10GR2> drop package utl_file;

Package dropped.

sys%ORA10GR2> @utlfile

Package created.


Grant succeeded.


Synonym created.

sys%ORA10GR2> connect /
Connected.


ops$tkyte%ORA10GR2> declare x utl_file.file_type; begin x := utl_file.fopen( 'x', 'x', 'x' ); end;
  2  /
declare x utl_file.file_type; begin x := utl_file.fopen( 'x', 'x', 'x' ); end;
*
ERROR at line 1:
ORA-04067: not executed, package body "SYS.UTL_FILE" does not exist
ORA-06508: PL/SQL: could not find program unit being called: "SYS.UTL_FILE"
ORA-06512: at line 1

<b>and in order to correct that, the DBA would put back that which they (or someone with their powers) got rid of:</b>

ops$tkyte%ORA10GR2> connect / as sysdba
Connected.

sys%ORA10GR2> @prvtfile.plb

Library created.


Package body created.

No errors.
sys%ORA10GR2> connect /
Connected.
ops$tkyte%ORA10GR2> declare x utl_file.file_type; begin x := utl_file.fopen( 'x', 'x', 'x' ); end;
  2  /
declare x utl_file.file_type; begin x := utl_file.fopen( 'x', 'x', 'x' ); end;
*
ERROR at line 1:
ORA-29281: invalid mode
ORA-06512: at "SYS.UTL_FILE", line 33
ORA-06512: at "SYS.UTL_FILE", line 436
ORA-06512: at line 1


<b>now it is there again</b>
 

utl_file

sam, August 29, 2006 - 4:38 pm UTC

Tom:

DBA does not know how to fix it.

1. Does the above apply also for 9i too? I guess al he has to do (since package header is there) is run
@prvtfile.plb


2. Why did you get an error again when you tried a second run?

Thanks,




Tom Kyte
August 29, 2006 - 5:36 pm UTC

Time for a new DBA? (I'm dead serious, if they cannot fix something as simple as this - something they probably did themselves - if not them, someone they work with...)


2) because I sent the letter 'x' when it wanted to have an 'r' or a 'w' for the file open mode. And if you fix that, it would complain about the path being not found - it was just to show the package body "was back"

utl_file

Manish Sharma, August 30, 2006 - 8:50 am UTC

Hi Sam,
You could be having zero bytes for file $OH/rdbms/admin/prvtfile.plb. Check it out.

If yes bring this file from another server with same Oracle Home version and platform.

I had the same issue on AIX 9205.

Thanks
Manish

Replacing utl_file_dir with create directory

Manish Sharma, August 30, 2006 - 8:55 am UTC

Hi Tom,
Going back to my thread of Aug 22nd.

1. If we have /var/opt1 & /var/opt2 folders, can we specify like utl_file_dir=/var/opt* ???

2. So for all the subfolders we have to specify each subfolder in utl_file_dir ??

3. Is above 2 is true for create directory too ???

4. Is there any way where we can specify all the subfolders in one line for create directory ???

Thanks
Manish

Tom Kyte
August 30, 2006 - 9:02 am UTC

1) no
2) yes
3) yes
4) no

Replacing utl_file_dir with create directory

Manish Sharma, August 30, 2006 - 9:06 am UTC

Very quick reply.

Thanks
Manish



Here is another painless technique..............

George Lewycky, August 30, 2006 - 12:00 pm UTC



SET SERVEROUTPUT ON
SET ECHO OFF
SET VERIFY OFF
SET Heading OFF
SET LINESIZE 2000
SET NEWPAGE NONE
SET PAGESIZE 100
SET Heading OFF
SET COLSEP , <- this saves you the headache of coding each column!!!
spool c:\myfile.txt
select * from tablename <---- select all from your table
spool off

NOTE: if any of the columns have ',' embedded in them like the address you might be in a bit of a bind. So you might need a unique delimiter like '|' or ']'
You must include tick marks with this delimiter !!! See below line:

SET COLSEP '|' or SET COLSEP ']'

your file will look something like this:
92877|S SHECTER |EMGINEERING & TECHNICAL FIELD
92881|S SHECTER |ENGINEERING & TECHNICAL FIELD


Another qry on UTL_FILE package.

Rijesh, November 21, 2006 - 10:00 am UTC

Hi Tom,

Here is another question regarding UTL_FILE.

Can I read/write a file using UTL_FILE from/to a sub-folder of an OS folder for which I created an oracle directory mapped to it?

For eg:
I have created an oracle directory named "LOAD_TEST" as DBA user and granted read write privileges for that directory to the user name "LOADDATA"

This "LOAD_TEST" directory is mapped to the windows OS folder "D:\TEST\".

In the Windows folder 'D:\TEST' there is a sub-folder named 'MASTER_PKG' which has a text file named 'Src_file.txt'

All I wanted to know is can I read this 'Src_file.txt' in the sub-folder using the UTL_FILE through "LOAD_TEST" oracle directory?

Similarly can I create a new file in the sub-folder using the same oracle directory?


please let me know.
Thanks in advance!

Flat to file to locale desktop

Gopesh Sharma, November 21, 2006 - 5:54 pm UTC

Sir all the methods you have specified including a follow up link uses the UTL_FILE which dumps files on the server.
Is there any method to dump files on the client mechine from sql other than spool .

Another related question is how to dump the data of a clob in a file which is created on the client mechine or even for that matter on the sql* screen.
I could not even see how I can use DBMS_LOB.READ when the buffer is going to be a varchar2.Or should one use loop statement and keep on increasing the value of offset.


Tom Kyte
November 22, 2006 - 4:19 pm UTC

you need to have a client program running on your client pc to create a file there


otherwise it would be called a VIRUS


SQL> set long NNNNN

where NNNN is longer than your longest long/clob 

UTL_PACKAGE to read log file and load it into excel sheet

Ron, December 01, 2006 - 7:15 am UTC

Hi Tom,
Is there a query in which we can read the data from a log file and store it into excel sheet.
The data stored from log file is selective and store the data in excel sheet at sepecifc rows and column.
The log file contains the process id as well the start date and end date we need to extract those and store it into excel sheet.
Waiting for the response ASAP. Plz Help
Thanks in advance

Tom Kyte
December 01, 2006 - 7:43 am UTC

if your goal is to
a) read a file
b) create a spreadsheet

I have no idea why you would do it via the database. Doesn't even seem to make sense.

You'd want to query an "excel" person - so they can point you to the XML format that excel can use - and generate a spreadsheet for them.

Reading the Log file

Ron, December 02, 2006 - 11:54 pm UTC

Hi,
Prsently i need to read the log file using UTL file and get the selected information from the logfile and store it in some table.
So please helpme with the query to resolve this issue since it is priority. Waiting for ur response ASAP.


Thanks,
Ron

Tom Kyte
December 03, 2006 - 7:41 am UTC

So, you change your requirements - no more spreadsheet.

I suggest - you do not need nor want to use utl_file, you want to use

a) an external table
</code> http://asktom.oracle.com/pls/ask/search?p_string=%22organization+external%22 <code>

b) or sqlldr (if you cannot use an external table)


that way, you are "code free". Else, if you really really really want to write lots of code (that you probably don't need to), just search this site for utl_file, there are lots of examples - it is one of the "easier" packages to understand, you open a file (fopen), you read lines, you close file - just like processing a file in most all programming languages.

Read a file and storing it into csv format

Ron, December 04, 2006 - 7:00 am UTC

Plz do find the text file from which i have to select only processname, startdate and enddate ..

Ron_WEEKLY started at 2006-11-25-02:26:28
Ron_WEEKLY contains 258 execution steps.



==============================================================
WEEKLY-ExecStep 1 - Process Name : RUNSQL_mbn325_1

RUNSQL_mbn325_1: started at 2006-11-25-02:26:28
--------------------------------------------------------------
COMMAND:sqlplus -S sc/sc@prd1 @mbn325_1.sql
mbn325_1: ended with return code 0.
RUNSQL_mbn325_1: ended at 2006-11-25-02:26:29 with return code 0.
------------------------------------------------------------



==============================================================
WEEKLY-ExecStep 2 - Process Name : RUNSQL_mbn325_2

RUNSQL_mbn325_2: started at 2006-11-25-02:26:29
--------------------------------------------------------------
COMMAND:sqlplus -S sc/sc@prd1 @mbn325_2.sql
mbn325_2: ended with return code 0.
RUNSQL_mbn325_2: ended at 2006-11-25-02:26:30 with return code 0.
------------------------------------------------------------



==============================================================
WEEKLY-ExecStep 3 - Process Name : RUNSQL_mbn325_3

RUNSQL_mbn325_3: started at 2006-11-25-02:26:30
--------------------------------------------------------------
COMMAND:sqlplus -S sc/sc@prd1 @mbn325_3.sql
mbn325_3: ended with return code 0.
RUNSQL_mbn325_3: ended at 2006-11-25-02:26:31 with return code 0.
------------------------------------------------------------


Now i want to read the file and select the three date the processname, startdate and enddate and store it into database table and then later store it to excel sheet.
Plz do reply ASAP

Tom Kyte
December 04, 2006 - 7:30 am UTC

WRITE CODE, you are a programmer correct?

first - psuedo code it, walk through your psuedo code, verify you have the logic pretty close to correct.

Then, take psuedo code and write real code from that.


This is "programming 101" here - the logic, pretty simple. Write the code.

how do i split the values in different columns from a single column

Ron, December 06, 2006 - 12:56 am UTC

Hey I had done the reading of the query and storing it into the database, presently i m facing a lot of problem in ...

FLD1
--------------------------------------------------------------------------------
==============================================================
WEEKLY-ExecStep 6 - Process Name : ZIPEXPFIL_mbe010
ZIPEXPFIL_mbe010: started at 2006-11-25-02:26:40
ZIPEXPFIL_mbe010: ended at 2006-11-25-02:26:40 with return code 0.
==============================================================
WEEKLY-ExecStep 7 - Process Name : CHKEXPFIL_mbe010b
CHKEXPFIL_mbe010b: started at 2006-11-25-02:26:40
CHKEXPFIL_mbe010b: ended at 2006-11-25-02:26:40 with return code 0.
==============================================================
WEEKLY-ExecStep 8 - Process Name : RUNEXPSQL_mbe010b
RUNEXPSQL_mbe010b: started at 2006-11-25-02:26:40
RUNEXPSQL_mbe010b: ended at 2006-11-25-02:26:49 with return code 0.

FLD1
--------------------------------------------------------------------------------
==============================================================
WEEKLY-ExecStep 9 - Process Name : ZIPEXPFIL_mbe010b
ZIPEXPFIL_mbe010b: started at 2006-11-25-02:26:49
ZIPEXPFIL_mbe010b: ended at 2006-11-25-02:26:49 with return code 0.
==============================================================
WEEKLY-ExecStep 10 - Process Name : RUNSQL_mbn110
RUNSQL_mbn110: started at 2006-11-25-02:26:49
RUNSQL_mbn110: ended at 2006-11-25-02:26:50 with return code 0.
==============================================================
WEEKLY-ExecStep 11 - Process Name : RUNSQL_mbn224
RUNSQL_mbn224: started at 2006-11-25-02:26:50

FLD1
--------------------------------------------------------------------------------
RUNSQL_mbn224: ended at 2006-11-25-02:26:50 with return code 0.
==============================================================
WEEKLY-ExecStep 12 - Process Name : RUNSQL_mbn060
RUNSQL_mbn060: started at 2006-11-25-02:26:50
RUNSQL_mbn060: ended at 2006-11-25-02:27:21 with return code 0.
==============================================================
WEEKLY-ExecStep 13 - Process Name : RUNSQL_mbn102
RUNSQL_mbn102: started at 2006-11-25-02:27:21
RUNSQL_mbn102: ended at 2006-11-25-02:27:22 with return code 0.
==============================================================
WEEKLY-ExecStep 14 - Process Name : RUNSQL_mbn390

FLD1 is the column name .. u can see there are three rows together now i need to split the rows and make three columns.. I am not able to do this since i was trying for long so i need ur help in this.



Tom Kyte
December 07, 2006 - 7:59 am UTC

"ur" isn't working here anymore, sorry.

no create
no inserts
NO LOOK

Reading filenames from a Directory

Sourabh, December 15, 2006 - 9:49 am UTC

Hi Tom,

This may not be the right thread for raising this question but i was not able to find an appropriate thread.

Is there any mechanism in Oracle by which we can read filenames that are located in a specific directory.

I visited the sub-programs of UTL_FILE Package but was not able to find the same.

Thanks in Advance.

Tom Kyte
December 15, 2006 - 11:41 am UTC

no, this is one thing that is sort of "missing"

You would need to use a java stored procedure to accomplish this.

</code> http://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:439619916584 <code>

Byte Mode Options

Tim, June 02, 2007 - 1:25 pm UTC

You included an example of a new 10g feature of the UTL_FILE.FOPEN function for byte mode processing. You suggested that it would avoid a chr(10) character. Would you please provide a little more of an explanation as to the purpose of the byte mode options ('rb', 'wb', 'ab') versus conventional read, write, and append? That is, what might these options be used for and how do they work? Do they apply exclusively to raw operations?

Thanks.
Tom Kyte
June 03, 2007 - 5:47 pm UTC

binary versus text - like "raw" binary, end of line characters are not "special" in binary, raw mode.

they are in text mode

like the difference between a binary and a text ftp from windows to unix. A text ftp will corrupt a file (unless it is just text) whereas a binary will not because the end of lines are not end of lines, just regular old bytes.

UTL_FILE & ASM

Rijesh, July 16, 2007 - 2:05 am UTC

Hi Tom,

I need to write the result of a query into a text file with some column delimiter using the UTL_FILE package.

I know that it is easy to create the file in any file system just by creating an oracle directory object and passing it as parameter to the UTL_FILE.FOPEN()

Is there a way to create that file with in an ASM directory using UTL_FILE?

If so, can you pls let me know the syntax?

Thanks.
Tom Kyte
July 17, 2007 - 11:05 am UTC

ASM is not for normal files like this. It doesn't currently make sense to ask to do this, you wouldn't be able to do anything with this text file.

You use normal file systems for writing normal files to, ASM is for database files only (redo, control, data, temp, dumps and the like)

RE: UTL_FILE & ASM

Rijesh, July 18, 2007 - 4:24 am UTC

Thanks Tom.

Copy Files

A reader, August 06, 2007 - 9:51 am UTC

Tom,

Could you please suggest a utility/script that copies a file from shared drive to a database server?

Requirement is like this:
Our client will upload data files to a folder in shared drive and using a script we have to copy that file to the database server, from there the load procedure will validate the file and load the data into database.

Thanks a lot and hoping to hear a solution from you!

Tom Kyte
August 06, 2007 - 12:07 pm UTC

hah, sure.


I'll get right on that one...

what does this have to do with a database?


Here is an easy solution: make the share be a database accessible disk, the client will be uploading right to your database server.

A reader, August 06, 2007 - 3:20 pm UTC

Tom,

Thank you for the response. But our DBA might not agree to make database access sharable, as the end user will get access to all files.
Tom Kyte
August 07, 2007 - 10:17 am UTC

no they wouldn't - they would get access to the directory you share.

TEXT FIEL WITHOUT NEW LINE OR CARRIAGE RETRUN

Debasish Ghosh, September 11, 2007 - 6:21 am UTC

I have multiple record, I want to write that record in a text file througH TEXT_IO.PUT_LINE or UTL_FILE.PUT_LINE but the problem is at the end of each line it give a new line or carriage retrun character, but my need is to eleminate those control character from that text file.

sample.
in table prnt
id txt
----------- ------------------------
1 abcdefghijklmnopqr 12345
2 xyz dfa dddfdd dd 0000
3
4
so on
...
..... like 1000 of rows is there in the table
through a procedure I want to put that out put in a text file which file have to send for data uploading but there requirement is do not put any control character in that file. but I am unaable to eleminate control file frol that output text file. please let me know in advance how to eleminate that control character when i write in a file from plsql procedure.




Tom Kyte
September 15, 2007 - 3:22 pm UTC

do not use put_line then, use put.

there will be a 32k limit on the max size of a line however.

If you need to have more then 32k of continuous text without a line break, you will not be able to use plsql to do it.

Tab delimited

Praveen Sadagopan, April 03, 2008 - 11:25 am UTC

Tom,

This works great, but..
How do you actually send a "tab" as a delimiter input. Tabs are all considered as a space. I tried it and it would put only a space.

Thanks,
Praveen
Tom Kyte
April 03, 2008 - 9:16 pm UTC

chr(9) is a tab

bhagyasree nallagangu, May 02, 2008 - 2:22 pm UTC

Hi Tom,
I was trying to use the function Data_CVS and the procedure.... Actually my procedure pass a value to a variable and this variable is to be used in the query which is sent into the data_CVS... i tried to modify the function u have written but i end up in loops... can u say me how i can do this...

Thanking you for trying to help me...

-NBS
Tom Kyte
May 02, 2008 - 3:07 pm UTC

huh?

the error message

A reader, June 16, 2008 - 3:53 pm UTC

CREATE OR REPLACE PROCEDURE analytables

is

f utl_file.file_type;


begin
f := utl_file.fopen ('/data/pdr/source/WorkAllocationLogs/PDRDEV', 'testing.dat', 'w' );
utl_file.fclose( f );
f := utl_file.fopen( '/data/pdr/source/WorkAllocationLogs/PDRDEV', 'testing.dat', 'w', 32000 );
utl_file.put_line( f, rpad( '*', 1024 ) );

UTL_FILE.PUTF(f, 'Start working ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
dbms_stats.gather_table_stats(user,' CONTRIBUTION ', ESTIMATE_PERCENT=>50);
utl_file.fclose( f );
exception
when others
dbms_output.put_line('yyyyyyyyyyyyyyy ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
raise;
end;

I got the following errors:

BEGIN analytables; END;

*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "RCMPDEV.ANALYTABLES", line 23
ORA-06512: at line 1

I double check:

1) '/data/pdr/source/WorkAllocationLogs/PDRDEV' is included in the UTL_FILE_DIR;
2) this user have the permission to create a file in this directory
3) there is no test.dat file in that directory.

Can you please tell me what happens?

Thank you very much! Yuna


Tom Kyte
June 16, 2008 - 4:00 pm UTC

erase this code:

exception
when others
dbms_output.put_line('yyyyyyyyyyyyyyy ' || to_char(sysdate,'dd-mon-yyyy
hh24:mi:ss'));
raise;


and run it again, so you can at least tell WHAT LINE THE ERROR WAS ON.

i hate when others, most every one uses it improperly, like this.

run again as following

A reader, June 16, 2008 - 4:57 pm UTC

Thank you Tom for the quick response.


SQL ACPIC-S373 >CREATE OR REPLACE PROCEDURE analytables
2
3 is
4
5 f utl_file.file_type;
6
7
8 begin
9 f := utl_file.fopen ('/data/pdr/source/WorkAllocationLogs/PDRDEV', 'testing.dat', 'w' );
10 utl_file.fclose( f );
11 f := utl_file.fopen( '/data/pdr/source/WorkAllocationLogs/PDRDEV', 'testing.dat', 'w', 32000 );
12 utl_file.put_line( f, rpad( '*', 1024 ) );
13
14 UTL_FILE.PUTF(f, 'Start working ' || to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
15 dbms_stats.gather_table_stats(user,' CONTRIBUTION ', ESTIMATE_PERCENT=>50);
16 utl_file.fclose( f );
17 end;
18 /

Procedure created.

SQL ACPIC-S373 >exec analytables;
BEGIN analytables; END;

*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 98
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at "RCMPDEV.ANALYTABLES", line 9
ORA-06512: at line 1


Thanks a lot! Yuna
Tom Kyte
June 17, 2008 - 9:39 am UTC

f := utl_file.fopen ('/data/pdr/source/WorkAllocationLogs/PDRDEV',
'testing.dat', 'w' );


you do not use the OS directory name there, you use the directory name you created with create directory


create directory MY_DIR as '/data/pdr/source/WorkAllocationLogs/PDRDEV';

...
f := utl_file.fopen ('MY_DIR','testing.dat', 'w' );
.....


use upper case for DIRECTORY_NAME as I did

A reader, June 17, 2008 - 3:15 am UTC


Execute created files

Marcel, June 23, 2008 - 8:23 am UTC

Hi Tom,

we all know that we can create files with the utl file package.

I also have procedure's to create such files, but know I am interested to execute a batch file from the procedure.

Can you give me an advice, please ?

Thanks
Marcel
Tom Kyte
June 23, 2008 - 8:39 am UTC

java stored procedures can 'run processes'

dbms_scheduler can be used to schedule an external program as well.


search for

host commands

on this site

writing iso8859 files from utf8 database

A Reader, September 09, 2008 - 5:47 am UTC

Hi Tom,

what if we need to create iso8859 files, but our database characterset is AL32UTF8? Is there a way using UTL_FILE?


utl_file does not produce ASCII

Uffe, September 12, 2008 - 11:18 am UTC

Hi,
I need to produce CSV files in ASCII, which should make your article here highly relevant, but while copying your code works for creating CSV's, they are NOT (always) ASCII.

I work on both Oracle 9 and 10. On 9, we use characterset WE8ISO8859P15. On 10, we use AL32UTF8.

I invoke your function with this call:

i := dump_csv(p_query => 'select ''æ'', ''ø'', ''å'' from dual',
p_dir => '/opt/REPORTING',
p_filename => 'tmp_test.txt');

æ, ø and å are danish characters. The ASCII hex values for these are E6, F8 and E5, and this is what I get from Oracle9.
However, Oracle10 gives me C3 A6, C3 B8 and C3 AF, which are UTF-8 representations, and I *need* ASCII.

Any hints please?
Tom Kyte
September 16, 2008 - 1:48 pm UTC

utl_file wants to create data in the characterset of your database.


have you tried using the convert function to convert your utf data into simple ascii

conversion

Uffe, September 17, 2008 - 9:14 am UTC

Hey,

I was finally able to get the ASCII output I needed with the convert function, thanks. For others with same issue, the following two lines were changed:

l_output := utl_file.fopen( p_dir, p_filename, 'w' );

Changed to:

l_output := utl_file.fopen( p_dir, p_filename, 'wb' );



utl_file.put( l_output, l_separator || l_columnValue );

Changed to:

utl_file.put_raw( l_output, utl_raw.convert( utl_raw.cast_to_raw( l_separator || l_columnValue ), 'AMERICAN_AMERICA.WE8ISO8859P15', 'AMERICAN_AMERICA.AL32UTF8' ) );

thank you uffe

matthias, September 17, 2008 - 10:30 am UTC

thank you uffe!

I also didn't notice the utl_file.put_raw

perhaps the docs should be more precise in when to use put, put_nchar and put_raw.

What the docs also dont tell clearly, is that utl_file.put_xyz takes virtually no time whereas flush or close really go to the disk. So you better avoid flushing every time...

utl_file should really be used with caution. Today we had several sessions all spinning at 100% cpu and suddenly consuming 150 Mb PGA each. Reason was these sessions tried to utl_file.put in the same file at the same time and I suppose somehow the filesystem, raid or whatever didn't return control to oracle and kept spinning.... Before that, the dbms_lock which served to secure file access had been removed due to performance issues....

Lesson learned is, you can have 8 cores at 1% cpu and a system performing *very* slow just because your own trace instrumentation calls utl_file.flush all the time.





Listing files in a DIR

MK, October 03, 2008 - 1:57 pm UTC

Hi Tom,
Was wondering if there was a way to list the files in a DIRECTORY and manipulate them in PL/SQL. I basically want to process a whole bunch of .sql files that are stored in this directory on the filesystem. I am able to create an Oracle DIRECTORY to this dir on the file system but I am not sure how I can write a PL/SQL program to list all the files and then maybe even execute these .sql files.
I was thinking about using SQl*Plus only to realize that they have no loop constructs in there which makes it very difficult to get to these scripts.


Tom Kyte
October 06, 2008 - 2:35 pm UTC

not in plsql, you would need a java stored procedure to read a directory listing.

http://asktom.oracle.com/pls/ask/search?p_string=dir_list

and remember, the files must exist on the SERVER - not on your client.

cvs output

Baqir Hussain, November 08, 2008 - 2:54 pm UTC

I am not able to compile following code using dump_csv package due to case statement in it. When I put double quote, it gets execute but does not replace (decode) these values. Please help.
Thanks

declare
l_rows number;
begin
l_rows := dump_csv('select
d.divisionabbr as "Div",
tkw.ldate,
v.vehiclenumber as "Coach",
tkw.Blocknum "Train",
mlg.LineGroupAbbr "Line",
tkw.employeeid "Cap",
tkw.workname as "Run",
tkw.fromtime,
tkw.totime,
case db.splitnum
when 0 then ''
when 1 then 'PI'
else 'PO'
end as "Coach Change",
case v.priority
when 1 then 'Phase I'
when 2 then 'Phase II'
else ''
end as "APC Phase"
FROM tkwork tkw, dailyblocks db, vehicles v, masterdivisions d, masterlinegroup mlg
WHERE
db.blocknum = tkw.blocknum
and tkw.signid = db.signid
and tkw.divisionid = db.divisionid
and tkw.ldate = db.ldate
and db.fromtime <= tkw.fromtime
and db.vehicleid = v.vehicleid
and db.divisionid = d.divisionid
and mlg.linegroupid = tkw.linegroupid
and v.priority in (1,2)
and db.ldate = to_char(sysdate -1,''YYYYMMDD'')
order by 2,1,4,3,7',
',',
'/home/oracle/db_scripts/',
'test.dat');
end;
41 /
when 1 then 'PI'
*
ERROR at line 16:
ORA-06550: line 16, column 18:
PLS-00103: Encountered the symbol "PI" when expecting one of the following:
) , * & | = - + < / > at in is mod remainder not rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like LIKE2_
LIKE4_ LIKEC_ as between from using || multiset member
SUBMULTISET_

Tom Kyte
November 11, 2008 - 2:55 pm UTC

to put a ' in a string, you need to either


ops$tkyte%ORA10GR2> select 'how''s this' from dual;

'HOW''STHI
----------
how's this


do it twice or, in 10g and above

ops$tkyte%ORA10GR2> select q'|how's this|' from dual;

Q'|HOW'STH
----------
how's this



use q'||'

cvs attachment

Baqir Hussain, November 26, 2008 - 7:08 pm UTC

It worked. Thanks
The output on the linux looks in order -- line by line
FLN,20081125,6208,984,009X,2175,503,60060,71400,,Phase I
FLN,20081125,6204,985,009X,1721,496,54180,70980,,Phase I
FLN,20081125,6219,1163,001X,3320,448,23040,33660,,Phase II
FLN,20081125,6226,1167,001X,1915,468,24600,31800,,Phase II

When I attached the same file using the following package
CREATE OR REPLACE PROCEDURE send_email_attach AS
fHandle utl_file.file_type;
vTextOut varchar2(32766);
text varchar2(32766);

BEGIN

fHandle := UTL_FILE.FOPEN('/home/oracle/db_scripts/','trapeze_cvs.dat','r');

IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File read open');
ELSE
DBMS_OUTPUT.PUT_LINE('File read not open');
END IF;

loop
begin
UTL_FILE.GET_LINE(fHandle,vTextOut);
text:=text||vTextOut;
dbms_output.put_line(length(text));
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
end;
END LOOP;

dbms_output.put_line(length(text));

UTL_FILE.FCLOSE(fHandle);

UTL_MAIL.SEND_ATTACH_VARCHAR2(sender => 'baqir.hussain@sfmta.com', recipients => 'baqir.hussain@sfmta.com', subject => 'cvs output', message => 'Hello || crlf || crlf', mime_type => 'text/plain; charset=us-ascii' ,priority => 1, attachment => text, ATT_INLINE => FALSE, att_mime_type => 'text/html', att_filename => 'trapeze_cvs.dat');
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Fehler');
raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
END;
/
The output does not come in order as shown above.

FLN,20081125,6231,901,009,1234,405,16860,50100,,Phase IIFLN,20081125,6231,901,009,2434,532,50100,76740,,Phase IIFLN,20081125,6222,902,009,1727,408,17760,53220,,Phase IIFLN,20081125,6222,902,009,2662,509,53220,72240,,Phase IIFLN,20081125,6201,913,009,1941,475,25080,33720,,Phase IFLN,20081125,6201,913,009,3301,501,33720,48900,,Phase IFLN,20081125,6201,913,009,2683,529,48900,74940,,Phase IFLN,20081125,6228,915,009,1022,493,27000,51300,,Phase IIFLN,20081125,6228,915,009,2241,537,51300,74220,,Phase IIFLN,20081125,6233,924,009,1685,464,58620,63960,,Phase IIFLN,20081125,6203,952,009X,2158,480,25440,56520,,Phase IFLN,20081125,6203,952,009X,1266,542,56520,92280,,Phase

Please help me how to achieve it.

Have a happy thanksgiving
Tom Kyte
November 28, 2008 - 5:15 pm UTC

why????


   EXCEPTION
     WHEN OTHERS THEN
       dbms_output.put_line('Fehler');
    raise_application_error(-20001,'The following error has occured: ' || 
sqlerrm);
   END;




why ???? why ???? why ???? just to hide the error line number to make things harder to find and fix?



I have no idea what you might be doing here. The file


'/home/oracle/db_scripts/','trapeze_cvs.dat'

has the data shown at the bottom, not the top, of your review - of that - I'm sure.

You were looking at the "wrong" file at some point.


So do this:

SQL> !cat /home/oracle/db_scripts/trapeze_cvs.dat
SQL> run your stored procedure

make sure you are logged ONTO THE SERVER ITSELF of course, since utl_file only reads the servers file system

CR breaks when attach file

Baqir Hussain, December 01, 2008 - 2:28 pm UTC

1. It's supposed to be like it. Thanks
WHEN OTHERS THEN
     --  dbms_output.put_line('Fehler');
    raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
   END;
   /
2. dump_cvs creates output file with proper CR as follows.  I would like that the same format should be read while opening file thriugh exchage:
SQL> !cat /home/oracle/db_scripts/trapeze_cvs.dat
FLN,20081125,6231,901,009,1234,405,16860,50100,,Phase II
FLN,20081125,6231,901,009,2434,532,50100,76740,,Phase II
FLN,20081125,6222,902,009,1727,408,17760,53220,,Phase II
FLN,20081125,6222,902,009,2662,509,53220,72240,,Phase II
FLN,20081125,6201,913,009,1941,475,25080,33720,,Phase I
FLN,20081125,6201,913,009,3301,501,33720,48900,,Phase I
FLN,20081125,6201,913,009,2683,529,48900,74940,,Phase I
FLN,20081125,6228,915,009,1022,493,27000,51300,,Phase II
FLN,20081125,6228,915,009,2241,537,51300,74220,,Phase II
FLN,20081125,6233,924,009,1685,464,58620,63960,,Phase II
FLN,20081125,6203,952,009X,2158,480,25440,56520,,Phase I
FLN,20081125,6203,952,009X,1266,542,56520,92280,,Phase I
FLN,20081125,6216,954,009X,2045,488,26220,31620,,Phase I
FLN,20081125,6229,955,009X,237,494,27480,32820,,Phase II
FLN,20081125,6402,974,009X,1016,485,57180,68520,,Phase I
FLN,20081125,6219,981,009X,3572,546,58020,91380,,Phase II
FLN,20081125,6208,984,009X,2175,503,60060,71400,,Phase I
FLN,20081125,6204,985,009X,1721,496,54180,70980,,Phase I
FLN,20081125,6219,1163,001X,3320,448,23040,33660,,Phase II
FLN,20081125,6226,1167,001X,1915,468,24600,31800,,Phase II
FLN,20081125,6206,1168,001X,671,472,24780,32400,,Phase I
FLN,20081125,6208,1169,001X,1576,477,25200,32820,,Phase I
FLN,20081125,6220,1170,001X,1191,478,25320,32520,,Phase II
FLN,20081125,6213,1181,001X,1447,467,53400,66180,,Phase I
FLN,20081125,6212,1182,001X,780,483,57780,68280,,Phase I
FLN,20081125,6224,1471,014XL,3025,438,22320,36720,,Phase II
FLN,20081125,6224,1471,014XL,2167,447,52980,64080,,Phase II
FLN,20081125,6224,1471,014XL,2771,507,36720,52980,,Phase II

3. send_email_attach procedure attaches this file 'trapeze_cvs.dat' and sends it to exchnage.

4. When I open this attachment through exchange, it breaks the CR and the output looks like as foolows:
FLN,20081125,6231,901,009,1234,405,16860,50100,,Phase IIFLN,20081125,6231,901,009,2434,532,50100,76740,,Phase IIFLN,20081125,6222,902,009,1727,408,17760,53220,,Phase IIFLN,20081125,6222,902,009,2662,509,53220,72240,,Phase IIFLN,20081125,6201,913,009,1941,475,25080,33720,,Phase IFLN,20081125,6201,913,009,3301,501,33720,48900,,Phase IFLN,20081125,6201,913,009,2683,529,48900,74940,,Phase IFLN,20081125,6228,915,009,1022,493,27000,51300,,Phase IIFLN,20081125,6228,915,009,2241,537,51300,74220,,Phase IIFLN,20081125,6233,924,009,1685,464,58620,63960,,Phase IIFLN,20081125,6203,952,009X,2158,480,25440,56520,,Phase IFLN,20081125,6203,952,009X,1266,542,56520,92280,,Phase IFLN,20081125,6216,954,009X,2045,488,26220,31620,,Phase IFLN,20081125,6229,955,009X,237,494,27480,32820,,Phase IIFLN,20081125,6402,974,009X,1016,485,57180,68520,,Phase IFLN,20081125,6219,981,009X,3572,546,58020,91380,,Phase IIFLN,20081125,6208,984,009X,2175,503,60060,71400,,Phase IFLN,20081125,6204,985,009X,1721,496,54180,70980,,Phase IFLN,20081125,6219,1163,001X,3320,448,23040,33660,,Phase IIFLN,20081125,6226,1167,001X,1915,468,24600,31800,,Phase IIFLN,20081125,6206,1168,001X,671,472,24780,32400,,Phase IFLN,20081125,6208,1169,001X,1576,477,25200,32820,,Phase IFLN,20081125,6220,1170,001X,1191,478,25320,32520,,Phase IIFLN,20081125,6213,1181,001X,1447,467,53400,66180,,Phase IFLN,20081125,6212,1182,001X,780,483,57780,68280,,Phase IFLN,20081125,6224,1471,014XL,3025,438,22320,36720,,Phase IIFLN,20081125,6224,1471,014XL,2167,447,52980,64080,,Phase IIFLN,20081125,6224,1471,014XL,2771,507,36720,52980,,Phase IIFLN,20081125,6218,1477,014XL,1794,456,49500,65580,,Phase IFLN,20081125,6218,1477,014XL,1685,464,24360,49500,,Phase IFLN,20081125,6226,1479,014XL,1941,475,51180,66780,PO,Phase IIFLN,20081125,6226,1479,014XL,1797,512,37800,51180,PO,Phase IIFLN,20081125,6214,3807,038,2087,417,19260,54600,,Phase IFLN,20081125,6214,3807,038,674,463,54600,66360,,Phase IFLN,20081125,6223,3810,038,3325,427,20880,31560,,Phase 
IIFLN,20081125,6223,3810,038,2052,498,31560,38940,,Phase IIFLN,20081125,6223,3810,038,1821,514,38940,74460,,Phase IIFLN,20081125,6210,3813,038,3325,427,45720,58740,,Phase IFLN,20081125,6210,3813,038,2862,435,19560,45720,,Phase IFLN,20081125,6210,3813,038,1933,502,58740,70320,,Phase IFLN,20081125,6211,3820,038,1292,445,43200,64980,,Phase IFLN,20081125,6211,3820,038,1455,492,26820,43200,,Phase IFLN,20081125,6207,3821,038,2343,457,23880,40260,PI,Phase IFLN,20081125,6207,3821,038,0,470,40260,60000,PI,Phase IFLN,20081125,6207,3821,038,1681,551,60000,90180,PI,Phase IFLN,20081125,6227,3823,038,1809,460,24120,36060,,Phase IIFLN,20081125,6227,3823,038,3052,506,36060,71760,,Phase IIFLN,20081125,6403,3828,038,2393,437,41940,48240,,Phase IFLN,20081125,6403,3828,038,2538,481,25560,41940,,Phase IFLN,20081125,6403,3828,038,1235,525,48240,73860,,Phase IFLN,20081125,6401,3829,038,780,483,25680,37620,,Phase IFLN,20081125,6401,3829,038,2824,511,37620,60000,,Phase IFLN,20081125,6401,3829,038,2616,550,60000,86220,,Phase IFLN,20081125,6209,3831,038,2353,432,42780,62580,,Phase IFLN,20081125,6209,3831,038,1936,476,62580,67500,,Phase IFLN,20081125,6209,3831,038,2620,491,26400,42780,,Phase IFLN,20081125,6207,3851,038,2463,479,47340,65940,,Phase IFLN,20081125,6216,3852,038,3090,527,48300,77040,,Phase IFLN,20081125,6229,3854,038,2407,423,48720,53580,,Phase IIFLN,20081125,6229,3854,038,3346,459,53580,61500,,Phase 

5. I would like that this attachment should be read as the same way on the Linux server without breaking CR (carriage return).

Thanks

Tom Kyte
December 02, 2008 - 5:51 am UTC

1. It's supposed to be like it. Thanks
WHEN OTHERS THEN
     --  dbms_output.put_line('Fehler');
    raise_application_error(-20001,'The following error has occured: ' || 
sqlerrm);
   END;
   /


WHY? WHY?
WHY? WHY?
WHY? WHY?
WHY? WHY?
WHY? WHY?
WHY? WHY?
WHY? WHY?
WHY? WHY?
WHY? WHY?


why do you do that??? Just to make it impossible to debug (you lost the line number of the error). Just to confuse the application by breaking the error message? Just to make it hard to figure out the real error by hiding the ora-xxxx number?

Lose that when others - it is a worst practice. If you do that, it makes it look like you don't know what you are doing.


2) 'exchange' is Microsofts email stuff I suppose. I further suppose you are creating the file on unix where a newline is properly just "\n". On windows, the newline is "\r\n"

So, you are sending a unix file to a windows client and the windows client is doing the wrong thing.

open the file using wordpad - not notepad.

Nothing to do with exchange, everything to do with notepad and windows. wordpad is happy with just \n

attach cvs

Baqir Hussain, December 04, 2008 - 3:38 pm UTC

Thanks for the reply:
This is procedure I got it from metalink
(Doc ID: Note:269375.1 ) and made some changes regarding directory path and file name.

CREATE OR REPLACE PROCEDURE send_email_attach AS

fHandle utl_file.file_type;
vTextOut varchar2(32000);
text varchar2(32000);

BEGIN

fHandle := UTL_FILE.FOPEN('c:\beispiele\utl_mail','attach.txt','r');

IF UTL_FILE.IS_OPEN(fHandle) THEN
DBMS_OUTPUT.PUT_LINE('File read open');
ELSE
DBMS_OUTPUT.PUT_LINE('File read not open');
END IF;

loop
begin
UTL_FILE.GET_LINE(fHandle,vTextOut);
text:=text||vTextOut;
-- dbms_output.put_line(length(text));
EXCEPTION
WHEN NO_DATA_FOUND THEN EXIT;
end;
END LOOP;

--dbms_output.put_line(length(text));

UTL_FILE.FCLOSE(fHandle);

UTL_MAIL.SEND_ATTACH_VARCHAR2(sender => 'xxx@oracle.com', recipients => 'xxx@oracle.com', subject => 'Testmail', message => 'Hello', attachment => text, ATT_INLINE => FALSE);
EXCEPTION
WHEN OTHERS THEN
-- dbms_output.put_line('Fehler');
raise_application_error(-20001,'The following error has occured: ' || sqlerrm);
END;
/
SHOW ERRORS


Thanks

Tom Kyte
December 09, 2008 - 11:46 am UTC

that is an example bit of demo code that can only be used in sqlplus really. Make it real code

lose that when others, I'll never understand why developers do that - our developers, you, your developers - whoever. I'll never ever get it - there is quite simply NO REASON for it and lots of reasons to NOT DO IT.

dir check

A reader, February 01, 2009 - 12:55 pm UTC

Tom:

is there a way where oracle or UTL_FILE can check whether a directory exists on the server and return an error if it does not ebfore you attempt to write the file.

UTL_FILE does not seem to return an error when we try to write a file and it does nto write if the directory does not exist.
Tom Kyte
February 02, 2009 - 11:06 am UTC

give small example. (I bet your code has a when others then null - or the equivalent thereof. You are swallowing the exceptions and you don't even know it....)

Regardless of whether you use utl_file_dir (init.ora parameter, do not use in 9i and up, use a directory object) or a directory object - if the directory does not exist, utl_file.fopen will definitely throw an exception (which you are probably hiding.... with a nasty when others :( )

ops$tkyte%ORA10GR2> show parameter utl_file

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
utl_file_dir                         string      /not/a/path/
ops$tkyte%ORA10GR2> declare
  2      l_output        utl_file.file_type;
  3  begin
  4      l_output := utl_file.fopen( '/not/a/path/', 'test.dat', 'w' );
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at line 4

<b>utl_file_dir fails, if the path is not valid...</b>

ops$tkyte%ORA10GR2> declare
  2      l_output        utl_file.file_type;
  3  begin
  4      l_output := utl_file.fopen( 'TEST', 'test.dat', 'w' );
  5  end;
  6  /
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at line 4

<b>it fails if the directory object is not created yet...</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> !mkdir /tmp/foobar

ops$tkyte%ORA10GR2> !chmod a+rwx /tmp/foobar

ops$tkyte%ORA10GR2> create or replace directory TEST as '/tmp/foobar/'
  2  /

Directory created.

<b>if we create the directory in the OS and the directory object in the database, it works:</b>


ops$tkyte%ORA10GR2> declare
  2      l_output        utl_file.file_type;
  3  begin
  4      l_output := utl_file.fopen( 'TEST', 'test.dat', 'w' );
  5          utl_file.fclose( l_output );
  6  end;
  7  /

PL/SQL procedure successfully completed.


<b>but if we later remove the directory...</b>
ops$tkyte%ORA10GR2> !rm -rf /tmp/foobar

ops$tkyte%ORA10GR2> declare
  2      l_output        utl_file.file_type;
  3  begin
  4      l_output := utl_file.fopen( 'TEST', 'test.dat', 'w' );
  5          utl_file.fclose( l_output );
  6  end;
  7  /
declare
*
ERROR at line 1:
ORA-29283: invalid file operation
ORA-06512: at "SYS.UTL_FILE", line 488
ORA-29283: invalid file operation
ORA-06512: at line 4

<b>it fails</b>




so, please do followup - I'll bet it is a when others....

Tom is great

vishnu, February 09, 2009 - 2:19 pm UTC

Excellent and detailed explanation and I got most of my doubts cleared..

Thanks
Vishnu Singireddy

EOF Identification in a Text file with lot of Blank (Empty) Lines

Rajeshwaran, Jeyabal, February 12, 2009 - 10:41 am UTC

Tom,

I am facing the below issue. Can you please help me in this ?
irads2@IRADSDB> DECLARE
2 FILE_TYP UTL_FILE.FILE_TYPE;
3 v_Char VARCHAR2(32767);
4 v_i NUMBER := 0;
5 BEGIN
6 FILE_TYP := UTL_FILE.fopen('IRADS_PROC_IN','data.txt','r',32767);
7 LOOP
8 UTL_FILE.GET_LINE(FILE_TYP,v_Char,32767);
9 EXIT WHEN v_Char IS NULL;
10 v_i := v_i + 1;
11 END LOOP;
12 UTL_FILE.fclose(FILE_TYP);
13 dbms_output.put_line (' Number of Records in File = '||v_i);
14 EXCEPTION
15 WHEN OTHERS THEN
16 IF UTL_FILE.is_open(FILE_TYP) THEN
17 UTL_FILE.fclose(FILE_TYP);
18 END IF;
19 RAISE_APPLICATION_ERROR(-20458,SQLERRM);
20 END;
21 /
Number of Records in File = 21
PL/SQL procedure successfully completed.
Here is my " data.txt " File contents.
============================================================
a
b
c
d
e
f
g
h
i
j
k
l
m
n
o
p
q
r
s
t
u

Some Sample text goes here.
================================================================
There will be a lot of blank lines like the above in my production System. (My Production database is Oracle 10g)
Question
========
1) How to count the number of lines in the File using UTL_FILE package in a PL/SQL program that has lot of blank lines in it.
2) Is there is any way in PL/SQL to determine the End of the File specifications. Like " EOF " in C programming language?

Tom Kyte
February 12, 2009 - 4:17 pm UTC

documentation says...

http://docs.oracle.com/docs/cd/B19306_01/appdev.102/b14258/u_file.htm#sthref11863

... If no text was read due to end of file, the NO_DATA_FOUND exception is raised. ....


ops$tkyte%ORA11GR1> !cat /tmp/data.txt
a


b

ops$tkyte%ORA11GR1> !wc -l /tmp/data.txt
4 /tmp/data.txt

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> DECLARE
  2  FILE_TYP UTL_FILE.FILE_TYPE;
  3  v_Char   VARCHAR2(32767);
  4  v_i      NUMBER := 0;
  5  BEGIN
  6   FILE_TYP := UTL_FILE.fopen('IRADS_PROC_IN','data.txt','r',32767);
  7   LOOP<b>
  8    begin
  9       UTL_FILE.GET_LINE(FILE_TYP,v_Char,32767);
 10    exception
 11       when no_data_found then exit;
 12    end;</b>
 13    v_i := v_i + 1;
 14   END LOOP;
 15    UTL_FILE.fclose(FILE_TYP);
 16    dbms_output.put_line (' Number of Records in File = '||v_i);
 17   EXCEPTION
 18   WHEN OTHERS THEN
 19    IF UTL_FILE.is_open(FILE_TYP) THEN
 20     UTL_FILE.fclose(FILE_TYP);
 21    END IF;
 22   RAISE_APPLICATION_ERROR(-20458,SQLERRM);
 23  END;
 24  /
Number of Records in File = 4

PL/SQL procedure successfully completed.

A reader, May 15, 2009 - 5:05 pm UTC

Dear Tom,
you are the life-saver.....
I am indeed need a procedure to load data to .csv file and I foud your sample code.

I am kind new to PL/SQL. and need help.
I got error for invalid directory path, too, but don't know how to ask question to DBA about the utl_file_dir init.ora setting... please give me a sample about how this suppose to be set. :(

many thanks!!
Tom Kyte
May 23, 2009 - 10:46 am UTC

In 9i and above you would use directories.

create or replace directory YOUR_DIR as '/whatever/path/you/want';


then use 'YOUR_DIR' as the directory name. You will almost certainly NEED dba assistance for this as well, as the CREATE ANY DIRECTORY privilege would be needed and it is not likely you have it (nor should you - they should set up and control what directories are accessible on the server)

Simply Superb

Saru, June 25, 2009 - 1:24 pm UTC

Tom


i have used this site as reference for many production issues. The information is very helpful and that too with examples.

Tom you rock.... Simply superb.

Invali Path

Vijay, February 17, 2010 - 7:10 am UTC

I have a proc to read the data from the file and load it into a table. Proc was compiled but when executing I am getting Invalid Path. I have the Directory and file which I mentioned in the proc.
Tom Kyte
February 17, 2010 - 9:34 am UTC

then you specified something wrong when you specified it all...

double check your work.

make sure you understand the directory and file must exist on the DATABASE SERVER, not on your pc.

and show your work, "prove" to us that you specified things correctly.

and when you prove it, prove it with AS LITTLE CODE AS POSSIBLE - meaning, we don't need your other 1,000 lines of code that try to process the file you cannot open, we just need your snippet of code that tries and fails to open the file.

utl_file package body does not available

Mohamed, March 04, 2010 - 7:39 am UTC

In our testing database have a job as xxx. Continuous the job has been broken. i have check the database alert log and found the below error.
Please help to resolve this error..

*** 2010-03-03 06:50:36.281
ORA-12012: error on auto execute of job 188
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "LOISTBL.PASSIVE_DISB_PROCESS", line 1521
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at "LOISTBL.PASSIVE_DISB_PROCESS", line 958
ORA-04067: not executed, package body "SYS.UTL_FILE" does not exist
ORA-06508: PL/SQL: could not find program unit being called
ORA-06512: at line 1
Tom Kyte
March 04, 2010 - 9:59 am UTC

talk to your dba, they will know what to do to fix this. The dba is responsible for ensuring the integrity of the default supplied packages, for whatever reason, they have removed the body of UTL_FILE and will need to reinstall it.

Yassin, August 25, 2010 - 4:38 pm UTC

Hi Tom,

Can I use UTL_FILE package to convert binary files to ascii files.

Thank you

Yassin
Tom Kyte
August 26, 2010 - 1:12 pm UTC

tell me what a binary file converted to ascii would look like first :)

I wouldn't begin to know what to do.

Yassin Othman, October 12, 2010 - 1:25 am UTC

Hi Tom,

I hadn't gotten a notification by the system for your reply, sorry for this delay.

Dear, I wok in a telecom company and I would to know how to convert raw binary files of call data record (CDRs) to ASCII using pl/sql code.

Thank you in advance,

Yassin
Tom Kyte
October 12, 2010 - 7:28 am UTC

what is the difference between "binary" and ASCII. ASCII files are raw binary files - files full of bytes with values typically between 0 and 127 (true ASCII) or 0 and 255 (extended ASCII character sets)

so, you must have a file that has a binary file format of some sort that has non-ascii data in it (just saying a file is "raw binary stuff" is meaningless).

You would have to sort of DESCRIBE in painstaking detail precisely, exactly, totally what is in the file.

utl_file

manjunath, November 10, 2010 - 10:57 pm UTC

it is very useful for reading the code snippets and also excuting the code it is very nice for us .simple logic the code and etc
---------
i have one dobut about utl_file to load the data into oracle tables the procedure created at the excution time getting the error like invalid operation/invalid path..
i crated directory also on that directory i mention the file name


Tom Kyte
November 11, 2010 - 2:33 pm UTC

the directory must point to a file system on the DATABASE server (not your pc)

the directory must be accessible to the Oracle account

same for the file.


that solves 99.999999999999999999% of the problems - show us that the directory exists on the server, that oracle has access and that the file is there.

Identify session which keeps file locked

Merk, January 17, 2013 - 10:16 am UTC

Hi Tom

I have a situation on a windows server. I am not able to delete a file because it is locked by a session inside the database. Presumably by use of utl_file.
At OS-level I can only see that the file is locked by the global database process. How can I identify which session inside the database is keeping the file locked?

We are at Oracle 11g and Windows Server 2003.

Thank you in advance,

Merk
Tom Kyte
January 17, 2013 - 10:32 am UTC

I don't know of any way to do that

RE: Identify session which keeps file locked

Guy Fullalove, January 17, 2013 - 10:56 am UTC

Assuming you are talking about a one-off sitiuation, I would:

1) Use Process Explorer (form SysInternals) to identify the thread that was holding onto the file.

2) Run the following SQL to identify the session that is associated with that thread:

select substr(nvl(s.program, d.name), 1, 20) as process  ,
       p.spid                                as os_thread,
       substr(nvl2(s.sid, s.sid ||
       ','                      ||
       s.serial#, null), 1, 10)              as sid_serial,
       substr(s.username, 1, 15)             as db_user   ,
       substr(s.osuser, 1, 40)               as os_user   ,
       substr(s.module, 1, 20)               as module    ,
       substr(s.action, 1, 10)               as action    
  from      v$process    p
  left join v$session    s on p.addr = s.paddr
  left join v$dispatcher d on p.addr = d.paddr
 where p.spid = :os_thread;


I can't think of an easy way to make this into just one script, but perhaps there is someone out there who does?
Tom Kyte
January 17, 2013 - 12:15 pm UTC

only if process explorer can generate stdout output (no gui, all command line)

then you could use this technique:

http://www.oracle.com/technetwork/issue-archive/2012/12-nov/o62asktom-1867739.html


to integrate the process explorer data with the v$ info



thanks for giving the approach here though! I don't do windows :)

Process Explorer - Example?

Mark Williams, January 17, 2013 - 12:55 pm UTC

I'd like to see the steps or an example of how Process Explorer can be used to find which thread is "holding onto the file". A Windows file handle kernel object is associated with a process not a thread. Or am I missing something?

Regards,

Mark

RE: Identify session which keeps file locked

Merk, January 18, 2013 - 5:54 am UTC

That is my conclusion also.

I am trying to use Process Explorer but I am not able to link the lock on the file with the session thread.

Thank you for your reply, though Guy.

Best Regards,

Merk

not exporting data having comma <,> as text

Vijay, March 06, 2013 - 9:10 pm UTC

Hi Tom,

I am using your utility to export data into csv delimited by chr(9). The problem i am facing is that it is exporting text which has comma <,> into new line when i open the csv.
Any idea how can we control this?
Tom Kyte
March 07, 2013 - 8:58 am UTC

my code doesn't do that.

therefore, it must be whatever you are opening it with.

describe, from start to finish, completely, what you are doing exactly.

Ascii file with many table

Franz, July 17, 2013 - 2:54 am UTC

Hi tom , i wank ask you about ascii files with many table . How to take some table ?
Tom Kyte
July 17, 2013 - 6:04 pm UTC

sorry, i could not parse this at all. I don't know what you mean

keshavrr@gmail.com, June 28, 2015 - 2:09 pm UTC

Hi Tom,

I was wondering if there is any way to use Join statements in the dbms_sql.parse command. Basically i want to fetch columns from different tables and want to display the output with column names.
I've seen many examples of utl.file and dbms_sql.parse command but none with the retrieval query using more than 1 tables. Is that even possible?
I've tried making one query..However its not working though...please find below and guide me on this..

create or replace
PACKAGE BODY Supplier_Unpaid_inv_new_pkg
AS

PROCEDURE Supplier_Unpaid_inv_new_prc (Supplier_GSL IN VARCHAR2)
is
---v_file_type utl_file.file_type := NULL;
V_DATA_FILE UTL_FILE.FILE_TYPE;
v_file_name VARCHAR2(10000);
V_DIRECTORY VARCHAR2(500);
l_columnValue varchar2(4000);
l_theCursor integer default dbms_sql.open_cursor;
l_colCnt number := 0;
l_status integer;
L_SEPARATOR VARCHAR2(1);
L_DESCTBL DBMS_SQL.DESC_TAB;

BEGIN

v_file_name := 'Supplier_Unpaid_invn_pkg.xls';
V_DIRECTORY := 'SSS_GWY_IN';
V_DATA_FILE := UTL_FILE.FOPEN(V_DIRECTORY, V_FILE_NAME, 'W','32760');
dbms_sql.parse( l_theCursor, 'SELECT a.vendor_name,
b.vendor_site_code,
c.org_id,
c.invoice_num,
c.payment_method_code,
c.invoice_amount,
c.invoice_date,
c.payment_status_flag
from ' || apps.ap_suppliers A JOIN
APPS.AP_SUPPLIER_SITES_ALL B ON A.VENDOR_ID=B.VENDOR_ID JOIN
APPS.AP_INVOICES_ALL C ON B.VENDOR_SITE_ID=C.VENDOR_SITE_ID
WHERE C.PAYMENT_STATUS_FLAG='N'
AND a.SEGMENT1 = SUPPLIER_GSL, DBMS_SQL.NATIVE);
dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );
--

FOR i in 1 .. l_colCnt LOOP
utl_file.put( v_data_file, l_separator || '"' || l_descTbl(i).col_name || '"' );
dbms_sql.define_column( l_theCursor, i, l_columnValue, 4000 );
l_separator := ',';
END LOOP;
utl_file.new_line( v_data_file );

l_status := dbms_sql.execute(l_theCursor);

while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) LOOP
l_separator := '';
FOR i in 1 .. l_colCnt LOOP
dbms_sql.column_value( l_theCursor, i, l_columnValue );
utl_file.put( v_data_file, l_separator || l_columnValue );
L_SEPARATOR := ',';

END LOOP;

utl_file.new_line( v_data_file );
END LOOP;

dbms_sql.close_cursor(l_theCursor);
UTL_FILE.FCLOSE(V_DATA_FILE);


EXCEPTION

WHEN OTHERS THEN
dbms_output.put_line('In Exception');
raise;

END Supplier_Unpaid_inv_new_prc;
--
END SUPPLIER_UNPAID_INV_NEW_PKG;

Appreciate your help as always..:)

What about converting ASCII files to flat files or to Oracle Tables? Is that possible by PL/SQL?

Zeab, September 19, 2017 - 12:21 am UTC

Thank you for your answers and I learned how to convert flat files to Oracle tables and flat files to ASCII files. What about converting ASCII files to flat files and/or Oracle tables? Does Oracle have a package to do that or can I code that?
Thank you!
Connor McDonald
September 19, 2017 - 1:49 am UTC

"What about converting ASCII files to flat file"

I dont know what you mean? An ascii file *is* a flat file.

"What about converting ASCII files to flat files and/or Oracle tables?"

We have SQL Loader

https://docs.oracle.com/database/122/SUTIL/oracle-sql-loader.htm#SUTIL3311

and also external tables

https://docs.oracle.com/database/122/SUTIL/oracle-external-tables-concepts.htm#SUTIL011

Plenty of examples of each on the AskTOM site.

More to Explore

DBMS_SQL

More on PL/SQL routine DBMS_SQL here