Current Position:Home > XML File in External Table - OS error permission denied.

XML File in External Table - OS error permission denied.

Update:10-11Source: network consolidation
Advertisement
Hi.
10g R2, Red Hat Linux
I'm using the article (see below, taken from http://www.dbazine.com/olc/olc-articles/scardina1 by Mark Scardina) to create an external table where I'd store my XML file.
So, I
1. Created a directory xmlfile_dir
2. Granted access to needed db user
3. Created the table
CREATE TABLE relayxml_xt (doc CLOB)
ORGANIZATION EXTERNAL
TYPE ORACLE_LOADER
DEFAULT DIRECTORY xmlfile_dir
ACCESS PARAMETERS
FIELDS (lobfn CHAR TERMINATED BY ',')
COLUMN TRANSFORMS (doc FROM lobfile (lobfn))
LOCATION ('xml.dat')
REJECT LIMIT UNLIMITED;
4. mv relay.xml /xmlfile_dir/xml.dat
When I run SELECT * FROM relayxml_xt I get this:
Error starting at line 1 in command:
select * from relayxml_xt
Error report:
SQL Error: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file RELAYXML_XT_28773.log
OS error Permission denied
ORA-06512: at "SYS.ORACLE_LOADER", line 19
29913. 00000 - "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.
What am I doing wrong?
Thanks,
Using External Tables
Introduced in Oracle9i, Oracle’s external table feature offers a solution to define a table in the database while leaving the data stored outside of the database. Prior to Oracle Database 10g, external tables can be used only as read-only tables. In other words, if you create an external table for XML files, these files can be queries and the table can be joined with other tables. However, no DML operations, such as INSERT, UPDATE, and DELETE, are allowed on the external tables.
Note: In Oracle Database 10g , by using the ORACLE_DATAPUMP driver instead of the default ORACLE_DRIVER, you can write to external tables. In Oracle Database 10g, you can define VARCHAR2 and CLOB columns in external tables to store XML documents. The following example shows how you can create an external table with a CLOB column to store the XML documents. First, you need to create a DIRECTORY to read the data files:
CREATE DIRECTORY data_file_dir AS 'D:\xmlbook\Examples\Chapter9\src\xml';
GRANT READ, WRITE ON DIRECTORY data_file_dir TO demo;
Then, you can use this DIRECTORY to define an external table:
CREATE TABLE customer_xt (doc CLOB)
ORGANIZATION EXTERNAL
TYPE ORACLE_LOADER
DEFAULT DIRECTORY data_file_dir
ACCESS PARAMETERS
FIELDS (lobfn CHAR TERMINATED BY ',')
COLUMN TRANSFORMS (doc FROM lobfile (lobfn))
LOCATION ('xml.dat')
REJECT LIMIT UNLIMITED;
The xml.dat file follows:
customer1.xml
customer2.xml
If you describe the table, you can see the following definition:
SQL> DESC customer_xt;
Name Null? Type
DOC CLOB
Then, you can query the XML document as follows:
SELECT XMLType(doc).extract('/Customer/EMAIL')
FROM customer_xt;
Though the query requires run-time XMLType creation and XPath evaluation, this approach is useful when applications just need a few queries on the XML data and don’t want to upload the XML data into database. In Oracle Database 10g, you cannot create external tables that contain pre-defined XMLType column types.
Message was edited by:
vi2167

The Best Answer

Advertisement
Your don't have the proper operating system privileges. Be sure that you (=oracle OS user / the OS Linux user that is starting the database) are allowed have read privs on the path and/or file.
for example...
chown -Rf /xxxxxxx/xxxx/etc
ls -l file.xml
file.xml    oracle:oinstall    rw-rw-rw
  • XML File in External Table - OS error permission denied. Update:10-11

    Hi. 10g R2, Red Hat Linux I'm using the article (see below, taken from http://www.dbazine.com/olc/olc-articles/scardina1 by Mark Scardina) to create an external table where I'd store my XML file. So, I 1. Created a directory xmlfile_dir 2. Granted ac

  • Loading xml file into external tables Update:10-11

    emp.xml is xml file namewhich is saved in C:\Documents and Settings\james\Desktop\emp.xml xml file <EMPLOYEES> <EMP> <EMPNO>7369</EMPNO> <ENAME>SMITH</ENAME> <JOB>CLERK</JOB> <HIREDATE>17-DEC-80</HI

  • How to load a XML file into a table Update:10-11

    Hi, I've been working on Oracle for many years but for the first time I was asked to load a XML file into a table. As an example, I've found this on the web, but it doesn't work Can someone tell me why? I hoped this example could help me. the file ac

  • Problem converting data in XML file to internal table data Update:10-11

    Hi all, I have a requirement. I need to convert an XML file to internal table data and based on that data do Goods Receipt in SAP. With the help of this blog /people/r.eijpe/blog/2005/11/10/xml-dom-processing-in-abap-part-i--convert-an-abap-table-int

  • Data Load from XML file to Oracle Table Update:10-11

    Hi, I am trying to load data from XML file to Oracle table using DBMS_XMLStore utility.I have performed the prerequisites like creating the directory from APPS user, grant read/write to directory, placing the data file on folder on apps tier, created

  • SSMS 2012: Import XML File to SQL Table - 'value' is not a recognized built-in function name!!?? Update:10-11

    Hi all, I have the following xml file (books1.xml): <bookstore> <book> <BookID>1</BookID> <title>Everyday Italian</title> <author>Giada De Laurentiis</author> <year>2005</year> <price>30.00

  • How to Download XML File to internal table Update:10-11

    Hi Friends, This is my urgent requirement. How to download XML File to Internal table. regards pauldharmaHai, Please check this Link http://www.sap-img.com/abap/upload-direct-excel.htm PARAMETERS: filename LIKE rlgrap-filename MEMORY ID M01,         

  • How to overwrite a log and bad file in external table in oracle 10g Update:11-30

    Hi, I have used external table in oracle 10g.whenever use select query in external table orace internally create one log file in specified directory, but this log file is growing.How can i overwrite the log file(old to replace with new).I need overwr

  • How to read any file using external tables. Update:11-30

    Hi folks, I have written an application that reads a series of csv files using external tables which works fine as long as I specify each file name in the directory i.e....... CREATE TABLE gb_test (file_name varchar2(10), rec_date date rec_name VARCH

  • XML file import in table Update:11-30

    Hello, I am working on oracle 10g on aix . I am trying to import a xml file in to table. but at the first column it is giveing error , any idea where is the issue .. ? sqlldr CONTROL=xml_insert.ctl, LOG=sample.log, BAD=baz.bad, DATA=newxml.xml USERID