PL/SQL: can't execute a simple file reading procedure

[SashaBuilder3]

Hi everybody!


I've just started learning Oracle and PL/SQL and a lot of basic things are unclear to me.


I wrote a simple sample procedure to read a file:

PROCEDURE mp2 IS
/*file reading*/
str1 varchar2(100);
dir varchar2(80);
fnm varchar2(10);

fin UTL_FILE.FILE_TYPE;

BEGIN
dir:='C:\D\OR\LOAD';
fnm:='a.txt';

fin:=UTL_FILE.FOPEN(dir,fnm,'r');

UTL_FILE.GET_LINE(fin,str1);
DBMS_OUTPUT.put_line(str1);

UTL_FILE.FCLOSE(fin);

END;


When I am trying to execute the procedure I am getting the following error messages:

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 "AM.MP2", line 13
ORA-06512: at line 2


That's where I am stuck.

Can anyone help?


Thanks,

Alexandre

 

[Turkbear]

Hi,
Please supply the version of Oracle and the OS for best results.

Have you specified that directory in your INIT<SID>.ORA file
as one that Oracle is allowed to access?

 

[SashaBuilder3]

Hi Turkbear,

Thanks for your response. Here is what my Procedure Builder's About box says:

Oracle Procedure Builder V6.0.7.2.1 Build #228 - Production
ORACLE PL/SQL V8.0.6.0.0 - Production
Oracle8i Personal Edition Release 8.1.5.0.0 - Production
With the Java option
PL/SQL Release 8.1.5.0.0 - Production


Does it give you a clue?


I suspected something related to security.

No, I did't specify this in the INIT.ORA.

Should it be: UTL_FILE_DIR='C:\D\OR\LOAD' ?

Or better to put: UTL_FILE_DIR=* ?

Should I somehow specify myself as a user in this assighnment? I don't load as system/manager, instead I created a new user and a corresponding tablespase.

Sorry if my oracle parlace sounds weird.


Thanks!

Alexandre

 

[Turkbear]

Hi, It depends..If it is a 'private' system ( that is only
you access it and it is not networked ) that may work but it opens up your file system to anyone who can connect to your Oracle database and knows anything about standard packages...

Read the docs on the UTL_FILE package for more, and probably better, info..

Or search for UTL_FILE at

http://tahiti.oracle.com

 

[SashaBuilder3]

Thanks Turkbear,

I added this line into init.ora and now it works fine. It IS a private system so there is no need in strict security.

And thank you for the link, I see a lot of useful things there!


Alexandre

 

[skunkmaster]

Another good idea would be to add an exception section to get a better error message, something like:-

EXCEPTION
WHEN UTL_FILE.INVALID_PATH then
UTL_FILE.FCLOSE(fin);
dbms_output.put_line('Invalid path for the file');
WHEN UTL_FILE.INVALID_MODE then
UTL_FILE.FCLOSE(fin);
dbms_output.put_line('Invalid mode for the file');
WHEN UTL_FILE.INVALID_FILEHANDLE then
UTL_FILE.FCLOSE(fin);
dbms_output.put_line('Invalid file handle ');
WHEN UTL_FILE.READ_ERROR then
UTL_FILE.FCLOSE(fin);
dbms_output.put_line('Read error for the file');
WHEN OTHERS then
UTL_FILE.FCLOSE(fin);
dbms_output.put_line('Error in utl_file procedure');

 

[SashaBuilder3]

skunkmaster,


Thank you very much, I am new to all this and any advice is very welcome.


Alexandre

 

[RJSHA1]

Hi SashaBuilder3,
The section that skunkmaster has included is VERY useful and you should include it in all packages that use UTL file because as you probably have discovered the error messages about UTL are not very clear.


Good Luck


Bob......

 

[SashaBuilder3]

Thanks a lot, Bob!

Alexandre