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

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

Superb solution

Is it possible using native dynamic sql though?

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

is there any way to create directory with utl_file

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.

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!!

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

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
/*
** 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.

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

Hi Sir,

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

Thanks
Siva Kumar

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:)

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> 

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

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.,

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:

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:

> 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: this is crystal clear now. Thank you. -Mike

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:

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:

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:

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:

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

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

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:

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:

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:

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:

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:

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

 

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.
 

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?

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, 

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

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.

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.

 

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

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.

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??

thanks very much.
your solution works.

thanks.

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,

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

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.

 

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

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

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 ?  

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.
 

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...

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

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, would you happen to have a java equivalent of the dump csv procedure?

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

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

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

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...

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).

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:

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.

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

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

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

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..

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
)

"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?

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."

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

thanx!

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

Hi Tom,

Thanks,

I would change the * to a specified directory.

Thanks.

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 --

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, could you explain what forking means?
Thanks

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.

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?

Thanks, it is working.

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;

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)

Hi Tom,

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

Thanks.

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

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

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

Thanks

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,

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,

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,
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,

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

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..

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.

<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?

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.

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

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

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.

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.

I wonder why I am always overlooking dbms_utility...

Bye,
Flavio

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?

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?

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.

is exactly what I was looking for.

Thanks.

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?

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?

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. 

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

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,
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,

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

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

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

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 ?

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

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

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,

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,

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,

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,

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?

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,

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

Thanks Tom for the follow up. Clear now.

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

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

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

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:

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:
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:

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?

@ 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

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 

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

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

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>

 

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:

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
 

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>
 

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,

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

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

Very quick reply.

Thanks
Manish

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

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!

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.

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 

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

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

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

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.

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.

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.

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.

Thanks Tom.

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,

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.

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,

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

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

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

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

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

Hi Tom,

what if we need to create iso8859 files, but our database characterset is AL32UTF8? Is there a way using UTL_FILE?

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?

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!

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.

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.

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_

ops$tkyte%ORA10GR2> select 'how''s this' from dual;

'HOW''STHI
----------
how's this

ops$tkyte%ORA10GR2> select q'|how's this|' from dual;

Q'|HOW'STH
----------
how's this

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

   EXCEPTION
     WHEN OTHERS THEN
       dbms_output.put_line('Fehler');
    raise_application_error(-20001,'The following error has occured: ' || 
sqlerrm);
   END;