Loading

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 · 1

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

Sunday, December 29, 2013

API to validate a IBAN in Oracle Apps R12 - CE_BANK_AND_ACCOUNT_VALIDATION















In this post, I tried to give a sample execution script for the private API CE_BANK_AND_ACCOUNT_VALIDATION.VALIDATE_IBAN

It can be used to validate whether the given bank account number is an IBAN number or not. It was very helpful for us in the current project. Hence, thought to share. 

SCRIPT:


SET SERVEROUTPUT ON:

DECLARE
   lv_iban_out        VARCHAR2 (200);
   lv_return_status   VARCHAR2 (200);
   p_account_no       VARCHAR2 (35)  DEFAULT 'FI1234567890123';
BEGIN
   ce_bank_and_account_validation.validate_iban
                            (p_iban               => p_account_no,
                             p_iban_out           => lv_iban_out,
                             x_return_status      => lv_return_status
                             );

   IF lv_return_status = 'S'
   THEN
      DBMS_OUTPUT.put_line ('Bank Account Type : IBAN');
   ELSE
      DBMS_OUTPUT.put_line ('Bank Account is not a IBAN number');
   END IF;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line ('Error : ' || SQLERRM);
END;




Sunday, December 29, 2013 by Team search · 0