Skip to Main Content

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Chella.

Asked: October 23, 2000 - 7:41 am UTC

Last updated: December 18, 2012 - 12:12 pm UTC

Version: 8i

Viewed 10K+ times! This question is

You Asked

I can run the following script in my local db but not on a db that's on the server;
-----------------------------------------------------------
declare
output utl_file.file_type;
begin
output := utl_file.fopen( 'c:\bsp\', 'epoctrans.slk', 'w');
utl_file.fclose( output );
output := utl_file.fopen( 'c:\bsp\', 'epoctrans.slk', 'w',32000);
owa_sylk.show(
p_file => output,
p_query => 'select * from scott.tblMailBoxData where CustAcNumber = :CustAcNumber',
p_parm_names =>
owa_sylk.owaSylkArray( 'CustAcNumber'),
p_parm_values =>
owa_sylk.owaSylkArray( '11318624' ),
p_show_grid => 'YES' );
utl_file.fclose( output );
end;
/

---------------------------------------
I get this error if I try to run on the server side db;

SQL> @c:\bsp\excltrans_e.sql
declare
*
ERROR at line 1:
ORA-06510: PL/SQL: unhandled user-defined exception
ORA-06512: at "SYS.UTL_FILE", line 101
ORA-06512: at "SYS.UTL_FILE", line 157
ORA-06512: at line 4

I have the GRANT EXECUTE permission to use the UTL_FILE on this server side database but what else could be wrong ? Thank you for your help in advance.

Chella

and Tom said...

You have not setup the init.ora parameter utl_file_dir to allow file IO to c:\bsp\ OR c:\bsp\ does not exist on that server.

UTL_FILE only works on file systems available to the server and only if the utl_file_dir init.ora parameter allows it.

if you use an exception block like this:

exception
when utl_file.invalid_path then
raise_application_error(-20001,
'INVALID_PATH: File location or filename was invalid.');
when utl_file.invalid_mode then
raise_application_error(-20002,
'INVALID_MODE: The open_mode parameter in FOPEN was
invalid.');
when utl_file.invalid_filehandle then
raise_application_error(-20002,
'INVALID_FILEHANDLE: The file handle was invalid.');
when utl_file.invalid_operation then
raise_application_error(-20003,
'INVALID_OPERATION: The file could not be opened or
operated on as requested.');
when utl_file.read_error then
raise_application_error(-20004,
'READ_ERROR: An operating system error occurred during
the read operation.');
when utl_file.write_error then
raise_application_error(-20005,
'WRITE_ERROR: An operating system error occurred
during the write operation.');
when utl_file.internal_error then
raise_application_error(-20006,
'INTERNAL_ERROR: An unspecified error in PL/SQL.');
end;


You'll get a more meaningful error message perhaps...


Rating

  (12 ratings)

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

Comments

utl_file_dir - ora 01034 error

rose, July 23, 2004 - 3:26 pm UTC

Hi Tom,
I am using Oracle8i (8.1.7) in Win2000.
I would like to use utl_file, so I altered init.ora file: e utl_file_dir = c:\test
Obs: I already created the path.

I stopped and initialized Oracle without problems.
The problem occurs when I am logging : ora-01034.
I done the test: when I not using utl_file_dir in init.ora, I can connect to DB without problems, but when I use this parameter (utl_file_dir), I can't connect to DB .

What is the problem?

Thanks



Tom Kyte
July 23, 2004 - 5:09 pm UTC

[tkyte@localhost tkyte]$ oerr ora 1034
01034, 00000, "ORACLE not available"
// *Cause: Oracle was not started up. Possible causes include the following:
// - The SGA requires more space than was allocated for it.
// - The operating-system variable pointing to the instance is
// improperly defined.
// *Action: Refer to accompanying messages for possible causes and correct
// the problem mentioned in the other messages.
// If Oracle has been initialized, then on some operating systems,
// verify that Oracle was linked correctly. See the platform
// specific Oracle documentation.

look in your alert log or try starting the database from the command line using sqlplus:

sqlplus /nolog
connect / as sysdba
shutdown abort
startup


and see what it says -- you most likely are using invalid syntax in the init.ora and that is preventing the database from starting.


utl_file_dir - ora 01034 error

rose, July 26, 2004 - 9:26 am UTC

Hi Tom,
I was wrong.
In init.ora, I was using "utl_dile_dir" instead "utl_file_dir".

Thank you very much!




Is it Possible

Suren, June 14, 2006 - 8:34 am UTC

Tom,

I saw in the first example heis directing the file to C:\ seems like local machine path, My question is it possible to get the output (.xls file)on the Local Disk using UTL_FILE if not please direct me to the link where you have OWA_SYLK package for Writing files to local disk.

Thanks,

Suren

Tom Kyte
June 14, 2006 - 12:29 pm UTC

UTL_FILE, which is running from plsql in the SERVER, can only write to file systems accessible to the SERVER.

UTL_FILE does not run in the client, UTL_FILE runs on the server.

If utl_file could actually write to your local file system - that would be a huge huge problem :)

why?

A reader, June 15, 2006 - 9:19 am UTC

Thanks for the Reply, But Oracle is really an Ocean one answer can give birth to numeros questions

1) Why is it a huge problem to run UTL_FILE in local disk?
2) OLE2 in forms why not in PL/SQL and whats the alternative if atall i want to write a File to Local Disk?

Take Care,

Suren


Tom Kyte
June 15, 2006 - 9:28 am UTC

1) think about it, you are asking for some machine in the network - way over there - to write to your disk. You *really* think this is a good idea? Many other people would put it into the category of "virus".

The disk to be written to must be available on the server doing the writing, that only makes sense. A client program (plsql is the client program in this case) can only write to the resources it has access to. plsql RUNS IN THE SERVER, not in your program.

Your program, running on your machine - it can write to the local file system (but not the servers! consider that, it goes two ways)

2) you have to have a program running on the local machine to write to the local disk. I don't know what "ole2" would have to do with it - given I don't really program windows only technologies myself.

Pl/SQL?

A reader, June 15, 2006 - 10:43 am UTC

Your explaination on UTL_FILE is understood good one,

Okey just a straight one how can i write Files to Local Disk using Oracle/Sql/PlSql?

I have to write a program in Oracle which Allows user to save Data on there Local Disk can this be achived in Oracle?

Thanks,

Suren

Tom Kyte
June 15, 2006 - 5:06 pm UTC

you write a client application in the language of your choice that CALLS either SQL or PLSQL and gets data back from them and opens a file on the local disk and writes it.


you could for example use a web browser - that runs a process in oracle and the web browser "saves to disk"

Just like if you click on my files tab in the tab bar and download a file from my database!

The client program = web browser.

SQL and PLSQL = thing that returned data to client program.

Thanks

A reader, June 15, 2006 - 11:57 pm UTC

Thanks for the reply ill try to write a program in Client that collects data from Server and writes it for me, shall keep you posted

Thanks

Suren

ULT FILE of Oracle 8i

manhtuan, January 25, 2011 - 2:16 am UTC

Hi Tom,

I using Oracle database 8i in windows server enterprise 2003 and get a problem when writting file with ULT_FILE package.

I get the sample code of create directory and write something to file:
---------------------------------
create or replace directory dir_temp as 'c:\temp';
/
declare
f utl_file.file_type;
begin
f := utl_file.fopen('DIR_TEMP', 'something.txt', 'w');
utl_file.put_line(f, 'line one: some text');
utl_file.put_line(f, 'line two: more text');
utl_file.fclose(f);
end;
/
-----------------------------------------
When I run this, I get the error:

10:10:34 PM ORA-06510: PL/SQL: unhandled user-defined exception
10:10:34 PM ORA-06512: at "SYS.UTL_FILE", line 101
10:10:34 PM ORA-06512: at "SYS.UTL_FILE", line 157
10:10:34 PM ORA-06512: at line 39
-----------------------------------------
In my init file,
parameter utl_file_dir=*
------------------------------------------
When I run "GRANT WRITE ON DIRECTORY dir_temp TO PUBLIC", I get the error:
10:17:57 PM ORA-22928: invalid privilege on directories

I sure that C:\TEMP folder is created in system
------------------------------------------
How can I fix this error ??

Thank you so much!
Tom Kyte
February 01, 2011 - 10:29 am UTC

In my init file,
parameter utl_file_dir=*


please fix that - you don't want *, really - truly - honestly - you do not. That would let anyone erase anything oracle has access to - that is truly dangerous.


You do not use 'directory' objects with utl_file in 8i - that started in 9i.

so, do not use DIR_TEMP, use c:\temp in your code.

ULT FILE of Oracle 8i

A reader, February 09, 2011 - 2:24 am UTC

Thanks for your help!


utl_file

pavani, June 04, 2012 - 5:16 am UTC

hi,

i am getting some encrypted output while reading file from unix path using utl_file concepts. please let me know how can i overcome this problem.
Tom Kyte
June 04, 2012 - 9:13 am UTC

don't read an encrypted file and it won't be a problem???

I have no idea what your problem is. This is as clear as mud.

use java strored procedure to write to local file system?

Ravi B, December 03, 2012 - 12:44 pm UTC

Hi Tom,

I understand that we cannot use pl/sql (UTL_FILE) to write to a local file system. But could we use java stored procedure to write to local file system?

Thanks.
Tom Kyte
December 18, 2012 - 12:12 pm UTC

you can only write to the file system where the process is running.

your java stored procedure writes to the same place plsql would write to.

think about it - would you not be upset of a java process running on machine A could reach out and write to your file system without you making your file system available to machine A?

Most people would call it a virus if it could.

we can use pl/sql (UTL_FILE) to write to a local file system

Sokrates, December 04, 2012 - 8:39 am UTC

both utl_file.fremove and utl_file.frename do indeed write in my understanding ?
Tom Kyte
December 14, 2012 - 1:33 pm UTC

they write to the directory - yes.

not sure what you mean entirely

what did Ravi B mean ?

Sokrates, December 16, 2012 - 11:32 am UTC

we can use utl_file to write
we can java stored procedure to write

both write to filesystem on the server
not much difference between those two, i think ?
Tom Kyte
December 18, 2012 - 12:11 pm UTC

oh, i see - i read that wrong. I've updated that.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.