This discussion is archived
2 Replies Latest reply: Aug 25, 2011 1:24 AM by N@*841964* RSS

Unable to generate XML file

N@*841964* Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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* Newbie
    Currently Being Moderated
    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

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points