How to open a file from PL/SQL

[froudeledu]

Problem: Can not specify path to read a file from stored procedure.

Potential cause: Improper configuration

Description:

I have created a procedure that opens a tab-delimited file, reads it, and inputs it into the database. In the procedure I specify path and name as follows:
p_FileDir := 'the_path';
p_FileName := 'file_name';

*the_path and file_name are actual paths and names.

The procedure compiles fine, but when I go to execute it I get this error:
SQL> execute loaddata
BEGIN loaddata; END;

*
ERROR at line 1:
ORA-20054: LoadData: Invalid Path
ORA-06512: at "FRO.LOADDATA", line 165
ORA-06512: at line 1

I am running Oracle on Sum Solaris.

Can someone please help?

Leo.

[balajiyes]

hi,
chj the path u hv given. see wheather the path is mapped in init.ora OR select from v$parameter.
other thing is SUN SOLARIS is case sencitive.

@worst case paste the code
rgds,

[dknight]

If you are using the UTL_FILE, type

SQL> show parameters utl

to see if the settings are correct. Otherwise, you need to change this parameter in init.ora.

Good luck.

[Shestakov]

If you going to use UTL_FILR package then you MUST set server parameter UTL_FILE_DIR = ... (for example UTL_FILE_DIR = *) in init{SID}.ora.

Check it.