Friday, January 17, 2014
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
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
Subscribe to:
Posts (Atom)
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.