Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-22285 -> non-existent directory or file for FILEOPEN operation

773821May 3 2011 — edited May 4 2011
Hello

I want to insert the xmldata from a xmlfile to the a table, with this scirpt:

#!/bin/sh
#
#---------------------------------------------------------------------
set -x


for i in `find . -name "dspevent*.xml"| awk -F"/" '{print $NF}'`
do
echo $i
fname_old=$i
fname_neu=`ls $i | awk '{print "OK_"$0}'`
cat $i | sed 's/aps://g' >$fname_neu
echo $fname_neu
rm $i
#insert_xml $fname_neu

INSERT_XML=`sqlplus -s username/passwort@database <<EOF
INSERT INTO event_xml (name,
dtsysl,
hrsysl,
dtcptl,
nusesi,
dsp_step,
dsp_state,
bat_class,
bat_program,
bat_seqi,
bat_state)
SELECT *
FROM XMLTABLE (
'Operations/Event'
PASSING xmltype (BFILENAME ('EVENT_DIR', '$fname_neu'),
NLS_CHARSET_ID ('CHAR_CS'))
COLUMNS name VARCHAR (40) PATH 'name',
dtsysl DATE PATH 'dtsysl',
hrsysl NUMBER PATH 'hrsysl',
dtcptl DATE PATH 'dtcptl',
nusesi NUMBER PATH 'nusesi',
dsp_step VARCHAR (40) PATH 'DSP/step',
dsp_state VARCHAR (40) PATH 'DSP/state',
bat_class VARCHAR (40) PATH 'BAT/class',
bat_program VARCHAR (40) PATH 'BAT/program',
bat_seqi NUMBER PATH 'BAT/seqi',
bat_state VARCHAR (40) PATH 'BAT/state');
EOF
`
done


But the problem is, that I have always this error message:

ORA-22285 -> non-existent directory or file for FILEOPEN operation

I'd create a direcotrie and over utl_file.fopen I can write a file to this directorie, but this works over the path, not over the directory name. the utl_file_dir parameter was also set to the correct path. Whats wrong or how can I debug this problem?
thanks.

roger
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 1 2011
Added on May 3 2011
16 comments
3,011 views