3 Replies Latest reply: Jan 23, 2012 10:52 AM by 897410 RSS

    XML invalid file operation DBMS_XSLPROCESSOR

    897410
      Hello,

      I made a procedure to create some xml file and then save all this files in a folder.

      1. So first I created my directory:

      create or replace directory test_dir as 'c:\oracle\test';

      2. XML

      CREATE OR REPLACE PROCEDURE Getloc3
      IS
      xmldoc CLOB;

      BEGIN

      FOR r IN (
      SELECT XMLSerialize(DOCUMENT
      XMLElement(
      ...
      )
      ) as xmldoc
      FROM table_name
      WHERE ...
      )
      LOOP

      DBMS_XSLPROCESSOR.clob2file(r.xmldoc, 'TEST_DIR', 'index'||TO_CHAR(SYSDATE, 'YYYYMMDD-HH24MISS')||'.xml');

      END LOOP;


      END;
      /

      When I create the procedure I dont have errors. The problem is when I execute, that I have this errors:

      ORA-29283: invalid file operation
      ORA-06512: at "SYS.UTL_FILE", line 475
      ORA-29283: invalid file operation
      ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 317
      ORA-06512: at "VPS.GETLOC3", line 57
      ORA-06512: at line 2

      Any Idea?

      Thanks
        • 1. Re: XML invalid file operation DBMS_XSLPROCESSOR
          odie_63
          Consider the following example and tell us what you're doing differently.

          The query returns 4 rows :
          SQL> select deptno
            2       , xmlserialize(document
            3           xmlelement("Department",
            4             xmlforest(deptno as "id", dname as "name", loc as "location")
            5           )
            6           as clob indent
            7         ) as dept_xml
            8  from scott.dept
            9  ;
           
          DEPTNO DEPT_XML
          ------ --------------------------------------------------------------------------------
              10 <Department>
                   <id>10</id>
                   <name>ACCOUNTING</name>
                   <location>NEW YORK</lo
           
              20 <Department>
                   <id>20</id>
                   <name>RESEARCH</name>
                   <location>DALLAS</locati
           
              30 <Department>
                   <id>30</id>
                   <name>SALES</name>
                   <location>CHICAGO</location
           
              40 <Department>
                   <id>40</id>
                   <name>OPERATIONS</name>
                   <location>BOSTON</loca
           
          Then to write each one to a separate file in the XML_DIR directory :
          BEGIN
            
            FOR r IN (
              select deptno 
                   , xmlserialize(document
                       xmlelement("Department",
                         xmlforest(deptno as "id", dname as "name", loc as "location")
                       )
                       as clob indent
                     ) as dept_xml
              from scott.dept
            )
            LOOP
              DBMS_XSLPROCESSOR.clob2file(r.dept_xml, 'XML_DIR', 'DEPT'||to_char(r.deptno)||'_'||to_char(sysdate,'YYYYMMDD"T"HH24MISS')||'.xml');
            END LOOP;
          
          END;
          /
          Output :
          DEPT10_20120122T122601.xml
          DEPT20_20120122T122601.xml
          DEPT30_20120122T122601.xml
          DEPT40_20120122T122601.xml

          Remember that the directory must point to somewhere on the database server, or another location the db server has access to.
          The Oracle user also has to be granted OS read/write privileges on the physical directory.
          • 2. Re: XML invalid file operation DBMS_XSLPROCESSOR
            897410
            Hello,

            With this query I fix the problem about overwrite files, but I have always the error message:

            ORA-29283: invalid file operation
            ORA-06512: at "SYS.UTL_FILE", line 475
            ORA-29283: invalid file operation
            ORA-06512: at "XDB.DBMS_XSLPROCESSOR", line 317
            ORA-06512: at "VPS.GETLOC3", line 57
            ORA-06512: at line 2

            It is strange because I have the error only in one database, and I grant the privilegies and I created a folder that oracle can access.

            Do you have any idea?

            Thx
            • 3. Re: XML invalid file operation DBMS_XSLPROCESSOR
              897410
              I tried to give this permissons and it did not work:

              GRANT READ,WRITE ON DIRECTORY test_DIR TO user;