Insert into table with xmltype column from a xml file

Question

Following is my query to insert xml file

INSERT INTO sampletagtable VALUES ( 1 , XMLType(bfilename('xmldir3', 'book.xml') , nls_charset_id('AL32UTF8') ));

Before that I have created the xmldir3 by the following query,

CREATE OR REPLACE DIRECTORY xmldir3 AS '/opt/user/nishanth/xmldir';

Here /opt/user/nishanth is a directory in my linux os.

book.xml is inside that specified directory.

I am getting the following error,

SQL Error: ORA-22285: non-existent directory or file for FILEOPEN operation
ORA-06512: at "SYS.XMLTYPE", line 296
ORA-06512: at line 1
22285. 00000 -  "non-existent directory or file for %s operation"
*Cause:    Attempted to access a directory that does not exist, or attempted
           to access a file in a directory that does not exist.
*Action:   Ensure that a system object corresponding to the specified
           directory exists in the database dictionary, or
           make sure the name is correct.

Answer 1

You created the directory as xmldir3, which is an unquoted identifier so it'll be upper-case in the data dictionary. But then you refer to it in lower-case. You need to use:

bfilename('XMLDIR3', 'book.xml')

You can check the actual directory name by querying the all_directories view:

SQL> CREATE OR REPLACE DIRECTORY xmldir3 AS '/opt/user/nishanth/xmldir';

Directory created.

SQL> SELECT directory_name, directory_path FROM all_directories;

DIRECTORY_NAME                 DIRECTORY_PATH
------------------------------ ----------------------------------------
XMLDIR3                        /opt/user/nishanth/xmldir
...