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;


4 Responses to “DBMS_XSLPROCESSOR.CLOB2FILE Example Script to Write a CLOB data to file”

Unknown said...
March 13, 2014 at 10:01 PM This comment has been removed by the author.

Unknown said...
August 17, 2014 at 11:27 PM This comment has been removed by the author.

Unknown said...
December 29, 2018 at 8:02 PM

Thanks and Regards. Oracle Apps R12 Training Videos at affordable cost. please check oracleappstechnical.com


wisewitch said...
May 13, 2020 at 3:56 AM

Good material for quick reference


Post a Comment

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.