2 Replies Latest reply: Aug 25, 2011 3:24 AM by N@*841964* RSS

    Unable to generate XML file

    N@*841964*
      Hi Gurus,

      I am generating one sample xml file in one of the designated location on the server. The xmldir directory is provided with all the necessary privileges. But when I am executing the pl/sql script for generating the xml file, it is throwing 'ORA-29285: file write error'.

      Any permissions do I need to set,while googling I found there are certain permissions need to be set. But I've given full access on the directories. Please let me know what exactly is missing.

      Thanks for your help in advance.


      Regards
      Nagendra
        • 1. Re: Unable to generate XML file
          AlexAnd
          I am generating one sample xml file in one of the designated location on the server.
          How?
          The xmldir directory is provided with all the necessary privileges.
          Which?
          But when I am executing the pl/sql script for generating the xml file, it is throwing 'ORA-29285: file write error'.
          Show please

          In my case the following is work
          select * from v$version
          
          BANNER                                                           
          ---------------------------------------------------------------- 
          Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 
          PL/SQL Release 10.2.0.1.0 - Production                           
          CORE     10.2.0.1.0     Production                                         
          TNS for 32-bit Windows: Version 10.2.0.1.0 - Production          
          NLSRTL Version 10.2.0.1.0 - Production                           
          
          conn sys@ora as sysdba
          
          create or replace directory T as 'c:\tmp\t';
          
          grant read, write on directory T to SCOTT;
          
          conn scott@ora
          
          declare
              v_filename varchar2(30);
              f_xml_file utl_file.file_type;
          
              v_record_data varchar2(4000) := null;
              v_empno varchar2(50) := null;
              v_ename varchar2(50) := null;
              v_job varchar2(50) := null;   
          
              cursor cur is
                select empno, ename, job from emp;
          
            begin   
              v_FILENAME := 'to_xml_file.xml';
          
              f_XML_FILE := UTL_FILE.fopen('T', v_FILENAME, 'W');
          
              v_RECORD_DATA := '<?xml version="1.0" encoding="UTF-8"?>';
              UTL_FILE.put_line(f_XML_FILE, v_RECORD_DATA);
          
              UTL_FILE.put_line(f_XML_FILE, '<DATA>');
              
              open cur;
              loop
                fetch cur into v_empno, v_ename, v_job;     
                EXIT WHEN cur%NOTFOUND;
          
                UTL_FILE.put_line(f_XML_FILE, ' <DETAILS>');
                utl_file.put_line(f_xml_file, ' <EMPNOTAG>' || v_empno || '</EMPNOTAG>');
                utl_file.put_line(f_xml_file,
                                  ' <ENAMETAG>' || v_ename || '</ENAMETAG>');
                utl_file.put_line(f_xml_file,
                                  ' <JOBATAG>' || v_job || '</JOBATAG>');      
                UTL_FILE.put_line(f_XML_FILE, ' </DETAILS>');
          
              end loop;
              close cur;
              
              UTL_FILE.put_line(f_XML_FILE, '</DATA>');
              UTL_FILE.FCLOSE(f_XML_FILE);
          
            EXCEPTION
              WHEN UTL_FILE.INTERNAL_ERROR THEN
                raise_application_error(-20500,
                                        'Cannot open file :' || v_FILENAME ||
                                        ', internal error; code:' || sqlcode ||
                                        ',message:' || sqlerrm);
              WHEN UTL_FILE.INVALID_OPERATION THEN
                raise_application_error(-20501,
                                        'Cannot open file :' || v_FILENAME ||
                                        ', invalid operation; code:' || sqlcode ||
                                        ',message:' || sqlerrm);
              WHEN UTL_FILE.INVALID_PATH THEN
                raise_application_error(-20502,
                                        'Cannot open file :' || v_FILENAME ||
                                        ', invalid path; code:' || sqlcode ||
                                        ',message:' || sqlerrm);
              WHEN UTL_FILE.WRITE_ERROR THEN
                raise_application_error(-20503,
                                        'Cannot write to file :' || v_FILENAME ||
                                        ', write error; code:' || sqlcode ||
                                        ',message:' || sqlerrm);
            end;
          in C:\tmp\t exsist to_xml_file.xml with
          <?xml version="1.0" encoding="UTF-8"?>
          <DATA>
           <DETAILS>
           <EMPNOTAG>9999</EMPNOTAG>
           <ENAMETAG>Tim</ENAMETAG>
           <JOBATAG></JOBATAG>
           </DETAILS>
           <DETAILS>
           <EMPNOTAG>7369</EMPNOTAG>
           <ENAMETAG>SMITH</ENAMETAG>
           <JOBATAG>CLERK</JOBATAG>
          ...
          • 2. Re: Unable to generate XML file
            N@*841964*
            Hi Gurus,

            Thanks for your support, as there was space issue on the server due to which I was not able to generate XML file. Now the issue is resolved.


            Regards
            Nagendera