Friday, January 17, 2014

DBMS_XSLPROCESSOR.CLOB2FILE Example Script to Write a CLOB data to file















In our current project,  we have a requirement for writing a data stored in CLOB column to a file. We found a standard ORACLE API DBMS_XSLPROCESSOR.CLOB2FILE

In this post, we have tried to explain the same API with an example. We hope it helps.

API Specification:

DBMS_XSLPROCESSOR.CLOB2FILE(
      cl          IN  CLOB,
      flocation   IN  VARCHAR2,
      fname       IN  VARCHAR2,
      CSID        IN  NUMBER:=0);

Script:

SET SERVEROUTPUT ON:
DECLARE 
   -- CLOB data
   lv_clob  CLOB   DEFAULT 'This is a example testing by SHAREORACLEAPPS'; 
   -- Database directory for a physical location
   lv_utl_file_directory   VARCHAR2 (100) DEFAULT 'TEST_DB_DIRECTORY'; 
   -- Output Filename                            
   lv_file_name        VARCHAR2 (100) DEFAULT 'TESTFILE.txt'; 
BEGIN 
   DBMS_XSLPROCESSOR.clob2file (lv_clob,
                                lv_utl_file_directory,
                                lv_file_name
                               );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error :' || SQLERRM);
END;


Friday, January 17, 2014 by Team search · 3

Friday, January 10, 2014

How to write a XML file in Oracle with specific character set using DBMS_XMLDOM.WRITETOFILE














In our project, we had a requirement to export an AP Invoice data as an XML file in a specified CHARACTER SET. We gone through different options and finally got a standard ORACLE API. “DBMS_XMLDOM.WRITETOFILE”. Here we have given a brief overview of it. 

Specification:

DBMS_XMLDOM.WRITETOFILE
(   doc          IN   DOMDOCUMENT,
   fileName      IN   VARCHAR2,
   charset       IN   VARCHAR2
);


Sample Script:

SET SERVEROUTPUT ON;

DECLARE
   lv_doc            DBMS_XMLDOM.domdocument;
   lv_xdata          XMLTYPE;
   lv_characterset   VARCHAR2 (50)           DEFAULT 'ISO-8859-1';
   lv_filename       VARCHAR2 (50)           DEFAULT 'SHAREORACLEAPPS.xml';
   lv_directory      VARCHAR2 (50)           DEFAULT 'TEST_DIRECTORY'; -- db directory
BEGIN
   SELECT XMLELEMENT ("AP_INVOICE_DATA",
                      XMLELEMENT ("Invoice_number", invoice_num),
                      XMLELEMENT ("Invoice_date", invoice_date),
                      XMLELEMENT ("Invoice_amount", invoice_amount)
                     )
     INTO lv_xdata
     FROM ap_invoices_all aia
    WHERE aia.invoice_num LIKE '%TEST_1';

   -- prepare the document
   lv_doc := DBMS_XMLDOM.newdomdocument (lv_xdata);
  
   -- write the xml file to a db directory in a specified chracter set
   DBMS_XMLDOM.writetofile (lv_doc,
                            lv_directory || '/' || lv_filename,
                            lv_characterset
                           );
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Others Exception : ' || SQLERRM);
END;

Sample XML:




Friday, January 10, 2014 by Team search · 0

Disclaimer

The ideas, thoughts and concepts expressed here are my own. They, in no way reflect those of my employer or any other organization/client that I am associated. The articles presented doesn't imply to any particular organization or client and are meant only for knowledge Sharing purpose. The articles can't be reproduced or copied without the Owner's knowledge or permission.