UTL_FILE package 3

[suggs]

I have just installed oracle server on my machine and am trying to use the utl_file package. I have created a database called cldtest. When I try to execute a simple procedure to just open a file and write to it I get the error note:
SQL> exec select_data('claudedemo')
BEGIN select_data('claudedemo'); END;

*
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 "CLAUDEDEMO.SELECT_DATA", line 6
ORA-06512: at line 1

My code is :
CREATE OR REPLACE PROCEDURE select_data(v_owner IN VARCHAR2) IS

fHandle UTL_FILE.FILE_TYPE;

BEGIN
fHandle:=UTL_FILE.FOPEN('o:\users\claude_vinaccia\temp','archivedata.txt','w');
UTL_FILE.PUT_LINE(fHandle,'File Begin');

UTL_FILE.PUT(fHandle,'schema:' || v_owner || ':' || 'table:' || 'tablename');


UTL_FILE.PUT(fHandle, 'columndata');

UTL_FILE.PUT(fHandle, ',');


UTL_FILE.PUT_LINE(fHandle, ';');


UTL_FILE.PUT_LINE(fHandle,'End File');
UTL_FILE.FCLOSE(fHandle);

END select_data;
/

I have set up auser called claudedemo and and granted execute on utl_file, and I have put utl_file_dir = * in the initCLDTEST.ora file.

Has anyone any ideas.

 

[sem]

Is O your local drive?

 

[suggs]

No it is a network drive

 

[sem]

As far as I know, UTL_FILE does not work with mapped drives on NT while running under SYSTEM account, for this NT account normally has no access to those drives. You may refine the Oracle service settings (Log On As). You may also use \\machine\share\path notation.

 

[karluk]

You should include an exception handler in your procedure to give you better diagnostics. That should help you diagnose your error more quickly. Sem may very well be right about the actual problem, but at this point it's all guesswork.

Here is an exception handler that we use with procedures that call utl_file. You can give it a try, if you like.

EXCEPTION
WHEN UTL_FILE.INVALID_PATH THEN
RAISE_APPLICATION_ERROR(-20100,'Invalid Path');

WHEN UTL_FILE.INVALID_MODE THEN
RAISE_APPLICATION_ERROR(-20101,'Invalid Mode');

WHEN UTL_FILE.INVALID_FILEHANDLE THEN
RAISE_APPLICATION_ERROR(-20102,'Invalid Filehandle');

WHEN UTL_FILE.INVALID_OPERATION THEN
RAISE_APPLICATION_ERROR(-20103,'Invalid Operation');

WHEN UTL_FILE.READ_ERROR THEN
RAISE_APPLICATION_ERROR(-20104,'Read Error');

WHEN UTL_FILE.WRITE_ERROR THEN
RAISE_APPLICATION_ERROR(-20105,'Write Error');

WHEN UTL_FILE.INTERNAL_ERROR THEN
RAISE_APPLICATION_ERROR(-20106,'Internal Error');

WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR(-20107,'No Data Found');

WHEN VALUE_ERROR THEN
RAISE_APPLICATION_ERROR(-20108,'Value Error');

WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20109,'Unknown error: '||sqlcode);

 

[hacketta]

This probably isn't the problem but before you use UTL_FILE you must set the UTL_FILE_DIR init.ora parameter to define which directories the package can write to