Sunday, May 24, 2015
In our current project, we have a requirement for reading a data stored in physical file to a CLOB column. We found a standard ORACLE API DBMS_XSLPROCESSOR.READ2CLOB
In this post, we have tried to explain the same API with an example. We hope it helps.
DBMS_XSLPROCESSOR.READ2CLOB(flocation IN VARCHAR2,fname IN VARCHAR2,csid IN NUMBER:=0)RETURN CLOB;
SET SERVEROUTPUT ON:DECLARE-- CLOB datalv_clob CLOB DEFAULT NULL;-- Database directory for a physical locationlv_utl_file_directory VARCHAR2 (100) DEFAULT 'TEST_DB_DIRECTORY';--Input Filenamelv_file_name VARCHAR2 (100) DEFAULT 'shareoracleapps.txt';BEGINlv_clob :=DBMS_XSLPROCESSOR.read2clob (flocation => lv_utl_file_directory,fname => lv_file_name);DBMS_OUTPUT.put_line (lv_clob);EXCEPTIONWHEN OTHERSTHENDBMS_OUTPUT.put_line ('Error :' || SQLERRM);END;
Step1: Create a DB Directory pointing the physical location of file
Step2: Place the file to be copied to CLOB in the desired location
Step3: Run the sample script to verify the content of the fileHappy Sharing Enjoy Learning
Do you think this Article is useful?
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.