12 Replies Latest reply: Aug 26, 2014 9:24 AM by 847526 RSS

    how to write xml file(Blob Data Type) content along with other columns to file system

    847526

      Team,
      we are currently working on oracle 11 g .Lets assume that we consider the SCOTT.EMP table for our table creation .
      Along all the existing columns for SCOTT.EMP table lets assume that this table has one extra column by name emp_xml which is of BLOB Data TYpe and it holds xml data.
      the size of the blob data for each record in this col is greater than 32 KB(NOrmally around 1 GB),NOw i would like to write the content of this  column along
      with empno,enmae,hiredate to an external file. Assume that if we need only the BLOB column i.e. emp_xml then the below code works

       

      begin 

      for c in (select emp_xml from your_table)

      loop  

      dbms_xslprocessor.clob2file(emp_xml.getclobval(), 'YOUR_LOCATION', 'YOUR_DYNAMIC_FILENAME' )
      end loop;
      end;

       

      but i want empno,enmae,hiredate along with emp_xml blob written to external file . ANy assistance in this case most welcome.

      Regards

        • 1. Re: how to write xml file(Blob Data Type) content along with other columns to file system
          ranit B

          BLOB - Binary Large Object

           

          Seems like you are storing XML data into a BLOB datatype. Why?

           

          XMLTYPE datatype is specifically for storing XML data.

           

          For each record in <your_table>, are you generating a separate file?

          • 2. Re: how to write xml file(Blob Data Type) content along with other columns to file system
            847526

            THanks for quick response.

             

            no, we need to write the contents of all the records into a single file.

             

            emp_xml is a blob column as Java Developers are inserting the xml content into the emp_xml column which is of BLob data type.

            Please suggest the process to be followed in case my column emp_xml is xmltype .(suggestion for xmltype or Blob datatype for emp_xml column is welcome )

             

            Regards

            • 3. Re: how to write xml file(Blob Data Type) content along with other columns to file system
              ranit B

              Another observation - Why are you generating the output file by LOOPing the table content?

               

              Can you try creating a CLOB variable in the BEGIN-END block, merge all the data into it, and then insert into the file at once?

              • 4. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                847526

                Any further recommendation or piece of code could be helpful as i could not get you completely.

                 

                Regards

                • 5. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                  ranit B

                  *Not tested*

                   

                  Please try if something like this works:

                  declare

                    v_c clob;

                  begin

                      

                    /* preparing a CSV */

                    select col1||','||col2||','||col3||',"'||emp_xml||'"'

                    into v_c

                    from your_table;

                   

                    dbms_xslprocessor.clob2file(v_c,'YOUR_LOCATION','YOUR_DYNAMIC_FILENAME');

                   

                  end;

                  • 6. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                    Karthick_Arp
                    SQL> create table dept_xml
                      2  as
                      3  select deptno
                      4       , dname
                      5       , dbms_xmlgen.getxmltype('select * from emp where deptno = ' || deptno) emp_detail
                      6    from dept;
                    
                    Table created.
                    
                    SQL> desc dept_xml
                     Name                                      Null?    Type
                     ----------------------------------------- -------- ----------------------------
                     DEPTNO                                             NUMBER
                     DNAME                                              VARCHAR2(10)
                     EMP_DETAIL                                         SYS.XMLTYPE
                    
                    SQL> select * from dept_xml;
                    
                        DEPTNO DNAME      EMP_DETAIL
                    ---------- ---------- ------------------------------------------------------------
                            10 ACCOUNTING <ROWSET>
                                           <ROW>
                                            <EMPNO>7782</EMPNO>
                                            <ENAME>CLARK</ENAME>
                                            <JOB>MANAGER</JOB>
                                            <MGR>7839</MGR>
                                            <HIREDATE>09-JUN-2013 00:00:00</HIREDATE>
                                            <SAL>12450</SAL>
                                            <COM>0</COM>
                                            <DEPTNO>10</DEPTNO>
                                           </ROW>
                                           <ROW>
                                            <EMPNO>7839</EMPNO>
                                            <ENAME>KING</ENAME>
                                            <JOB>PRESIDENT</JOB>
                                            <HIREDATE>17-NOV-2013 00:00:00</HIREDATE>
                                            <SAL>5000</SAL>
                                            <COM>0</COM>
                                            <DEPTNO>10</DEPTNO>
                                           </ROW>
                                          </ROWSET>
                            20 RESEARCH   <ROWSET>
                                           <ROW>
                                            <EMPNO>7369</EMPNO>
                                            <ENAME>SMITH</ENAME>
                                            <JOB>CLERK</JOB>
                                            <MGR>7902</MGR>
                                            <HIREDATE>02-APR-2013 00:00:00</HIREDATE>
                                            <SAL>12975</SAL>
                                            <COM>0</COM>
                                            <DEPTNO>20</DEPTNO>
                                           </ROW>
                                           <ROW>
                                            <EMPNO>7566</EMPNO>
                                            <ENAME>JONES</ENAME>
                                            <JOB>MANAGER</JOB>
                                            <MGR>7839</MGR>
                                            <HIREDATE>02-APR-2013 00:00:00</HIREDATE>
                                            <SAL>12975</SAL>
                                            <COM>0</COM>
                                            <DEPTNO>20</DEPTNO>
                                           </ROW>
                                           <ROW>
                                            <EMPNO>7788</EMPNO>
                                            <ENAME>SCOTT</ENAME>
                                            <JOB>ANALYST</JOB>
                                            <MGR>7566</MGR>
                                            <HIREDATE>19-APR-1987 00:00:00</HIREDATE>
                                            <SAL>13000</SAL>
                                            <COM>0</COM>
                                            <DEPTNO>20</DEPTNO>
                                           </ROW>
                                           <ROW>
                                            <EMPNO>7876</EMPNO>
                                            <ENAME>ADAMS</ENAME>
                                            <JOB>CLERK</JOB>
                                            <MGR>7788</MGR>
                                            <HIREDATE>23-MAY-1987 00:00:00</HIREDATE>
                                            <SAL>11101</SAL>
                                            <COM>0</COM>
                                            <DEPTNO>20</DEPTNO>
                                           </ROW>
                                          </ROWSET>
                            30 SALES      <ROWSET>
                                           <ROW>
                                            <EMPNO>7499</EMPNO>
                                            <ENAME>ALLEN</ENAME>
                                            <JOB>SALESMAN</JOB>
                                            <MGR>7698</MGR>
                                            <HIREDATE>20-FEB-2013 00:00:00</HIREDATE>
                                            <SAL>11600</SAL>
                                            <COM>300</COM>
                                            <DEPTNO>30</DEPTNO>
                                           </ROW>
                                           <ROW>
                                            <EMPNO>7521</EMPNO>
                                            <ENAME>WARD</ENAME>
                                            <JOB>SALESMAN</JOB>
                                            <MGR>7698</MGR>
                                            <HIREDATE>22-FEB-2013 00:00:00</HIREDATE>
                                            <SAL>11250</SAL>
                                            <COM>500</COM>
                                            <DEPTNO>30</DEPTNO>
                                           </ROW>
                                           <ROW>
                                            <EMPNO>7654</EMPNO>
                                            <ENAME>MARTIN</ENAME>
                                            <JOB>SALESMAN</JOB>
                                            <MGR>7698</MGR>
                                            <HIREDATE>28-SEP-2013 00:00:00</HIREDATE>
                                            <SAL>11250</SAL>
                                            <COM>1400</COM>
                                            <DEPTNO>30</DEPTNO>
                                           </ROW>
                                           <ROW>
                                            <EMPNO>7698</EMPNO>
                                            <ENAME>BLAKE</ENAME>
                                            <JOB>MANAGER</JOB>
                                            <MGR>7839</MGR>
                                            <HIREDATE>01-MAY-2013 00:00:00</HIREDATE>
                                            <SAL>12850</SAL>
                                            <COM>0</COM>
                                            <DEPTNO>30</DEPTNO>
                                           </ROW>
                                           <ROW>
                                            <EMPNO>7844</EMPNO>
                                            <ENAME>TURNER</ENAME>
                                            <JOB>SALESMAN</JOB>
                                            <MGR>7698</MGR>
                                            <HIREDATE>08-SEP-2013 00:00:00</HIREDATE>
                                            <SAL>11500</SAL>
                                            <COM>0</COM>
                                            <DEPTNO>30</DEPTNO>
                                           </ROW>
                                          </ROWSET>
                    

                    You can write a PL/SQL block like this.

                     

                    declare
                      l_file   utl_file.file_type;
                      l_size   integer;
                      l_chunk  varchar2(1024);
                      l_offset integer;
                      l_part   integer;
                    begin
                      l_file :=  utl_file.fopen('KARDIR', 'dept_xml.dat', 'w', 2000);
                    
                      for i in (
                                  select deptno
                                       , dname
                                       , to_clob(emp_detail) emp_detail
                                    from dept_xml
                               )
                      loop
                          utl_file.put_line(l_file, to_char(i.deptno) || ',' || i.dname);
                          l_size   := dbms_lob.getlength(i.emp_detail);
                          l_offset := 1;
                          l_part   := 0;
                          loop
                              l_chunk  := dbms_lob.substr(i.emp_detail, 1024, l_offset);
                              utl_file.put(l_file, l_chunk);
                              exit when l_offset + 1024 > l_size;
                    
                              l_part   := l_part + 1;
                              l_offset := l_offset + (1024 * l_part);
                          end loop;
                      end loop;
                    
                      utl_file.fclose(l_file);
                    end;
                    /
                    

                     

                    In Unix...

                     

                    karthick% cat dept_xml.dat
                    10,ACCOUNTING
                    <ROWSET>
                     <ROW>
                      <EMPNO>7782</EMPNO>
                      <ENAME>CLARK</ENAME>
                      <JOB>MANAGER</JOB>
                      <MGR>7839</MGR>
                      <HIREDATE>09-JUN-2013 00:00:00</HIREDATE>
                      <SAL>12450</SAL>
                      <COM>0</COM>
                      <DEPTNO>10</DEPTNO>
                     </ROW>
                     <ROW>
                      <EMPNO>7839</EMPNO>
                      <ENAME>KING</ENAME>
                      <JOB>PRESIDENT</JOB>
                      <HIREDATE>17-NOV-2013 00:00:00</HIREDATE>
                      <SAL>5000</SAL>
                      <COM>0</COM>
                      <DEPTNO>10</DEPTNO>
                     </ROW>
                    </ROWSET>
                    20,RESEARCH
                    <ROWSET>
                     <ROW>
                      <EMPNO>7369</EMPNO>
                      <ENAME>SMITH</ENAME>
                      <JOB>CLERK</JOB>
                      <MGR>7902</MGR>
                      <HIREDATE>02-APR-2013 00:00:00</HIREDATE>
                      <SAL>12975</SAL>
                      <COM>0</COM>
                      <DEPTNO>20</DEPTNO>
                     </ROW>
                     <ROW>
                      <EMPNO>7566</EMPNO>
                      <ENAME>JONES</ENAME>
                      <JOB>MANAGER</JOB>
                      <MGR>7839</MGR>
                      <HIREDATE>02-APR-2013 00:00:00</HIREDATE>
                      <SAL>12975</SAL>
                      <COM>0</COM>
                      <DEPTNO>20</DEPTNO>
                     </ROW>
                     <ROW>
                      <EMPNO>7788</EMPNO>
                      <ENAME>SCOTT</ENAME>
                      <JOB>ANALYST</JOB>
                      <MGR>7566</MGR>
                      <HIREDATE>19-APR-1987 00:00:00</HIREDATE>
                      <SAL>13000</SAL>
                      <COM>0</COM>
                      <DEPTNO>20</DEPTNO>
                     </ROW>
                     <ROW>
                      <EMPNO>7876</EMPNO>
                      <ENAME>ADAMS</ENAME>
                      <JOB>CLERK</JOB>
                      <MGR>7788</MGR>
                      <HIREDATE>23-MAY-1987 00:00:00</HIREDATE>
                      <SAL>11101</SAL>
                      <COM>0</COM>
                      <DEPTNO>20</DEPTNO>
                     </ROW>
                    </ROWSET>
                    30,SALES
                    <ROWSET>
                     <ROW>
                      <EMPNO>7499</EMPNO>
                      <ENAME>ALLEN</ENAME>
                      <JOB>SALESMAN</JOB>
                      <MGR>7698</MGR>
                      <HIREDATE>20-FEB-2013 00:00:00</HIREDATE>
                      <SAL>11600</SAL>
                      <COM>300</COM>
                      <DEPTNO>30</DEPTNO>
                     </ROW>
                     <ROW>
                      <EMPNO>7521</EMPNO>
                      <ENAME>WARD</ENAME>
                      <JOB>SALESMAN</JOB>
                      <MGR>7698</MGR>
                      <HIREDATE>22-FEB-2013 00:00:00</HIREDATE>
                      <SAL>11250</SAL>
                      <COM>500</COM>
                      <DEPTNO>30</DEPTNO>
                     </ROW>
                     <ROW>
                      <EMPNO>7654</EMPNO>
                      <ENAME>MARTIN</ENAME>
                      <JOB>SALESMAN</JOB>
                      <MGR>7698</MGR>
                      <HIREDATE>28-SEP-2013 00:00:00</HIREDATE>
                      <SAL>11250</SAL>
                      <COM>1400</COM>
                      <DEPTNO>30</DEPTNO>
                     </ROW>
                     <ROW>
                      <EMPNO>7698</EMPNO>
                      <ENAME>BLAKE</ENAME>
                      <JOB>MANAGER</JOB>
                      <MGR>7839</MGR>
                      <HIREDATE>01-MAY-2013 00:00:00</HIREDATE>
                      <SAL>12850</SAL>
                      <COM>0</COM>
                      <DEPTNO>30</DEPTNO>
                     </ROW>
                     <ROW>
                      <EMPNO>7844</EMPNO>
                      <ENAME>TURNER</ENAME>
                      <JOB>SALESMAN</JOB>
                      <MGR>7698</MGR>
                      <HIREDATE>08-SEP-2013 00:00:00</HIREDATE>
                      <SAL>11500</SAL>
                      <COM>0</COM>
                      <DEPTNO>30</DEPTNO>
                     </ROW>
                    </ROWSET>
                    karthick%
                    
                    • 7. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                      847526

                      thanks Karthik for immediate response .

                       

                      but what if the size of the xml is above 32 KB or lets assume its around 1 GB . and hope that the above code will not work.

                       

                      Any advise on this.

                       

                      REgards

                      • 8. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                        odie_63

                        but i want empno,enmae,hiredate along with emp_xml blob written to external file .

                         

                        And what would the output look like? You want to mix scalar data with XML so the format you expect is not really obvious.

                         

                        Can you also state once and for all what's the datatype of EMP_XML?

                        You first said it is BLOB, but then you gave an example implying it is XMLType?

                        • 9. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                          Karthick_Arp

                          847526 wrote:

                           

                          thanks Karthik for immediate response .

                           

                          but what if the size of the xml is above 32 KB or lets assume its around 1 GB . and hope that the above code will not work.

                           

                          Any advise on this.

                           

                          REgards

                           

                          Why? I don't see a reason for that to not work.

                          • 10. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                            847526

                            I was doubtful because in the block of code if we see this line:

                             

                             

                            l_file : utl_file.fopen('KARDIR','dept_xml.dat','W',2000)

                             

                            i think the file to which we write or the Utl_file cannot write anything which is above 32 KB(32767).

                             

                            Please suggest

                            • 11. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                              Karthick_Arp

                              max_linesize


                              Maximum number of characters for each line, including the newline character,
                              for this file (minimum value 1, maximum value 32767). If unspecified, Oracle
                              supplies a default value of 1024.

                               

                              That defines how much you can read or write on a single call. I loop through the clob and i am writing chunks of 1024. So there is no limit in how much it can write. Just test it, IT WILL WORK!!

                              • 12. Re: how to write xml file(Blob Data Type) content along with other columns to file system
                                847526

                                THanks to both  ranit B and Karthick for  quick response.