Oracle PL SQL real world example - Generate XML Files

 

Oracle PL SQL real world example - Generate XML Files

In this blog we will try to generate XML files. Let's understand the real business worl requirements first.

The abc company working as a collection agent for another company named xyz

The abc company use oracle forms to collect the payment via calling a SOAP service provided by xyz company. On every success call they save data in below table which later send back to xyz company in form of XML files. The abc company also use this data for other purpose like calculate their income or fee for this service for each bill.

Now we create below table to save bill information along with billing No., amount, date , currency and most important Institute code. Why institute code because same service can be provide other companies and based on institute code we identify or segregate the billing information.

Execute below code to create table and then insert few records for same date and same institute code.

Table for Bill information 

create table   BILL_TRAN_HIST(p_tran_date date, p_inst_code varchar2)

(

  tran_id             number,

  branch              varchar2(20),

  tran_date           date,

  org_code           varchar2(10),

  tran_amt            number,

  ccy                 varchar2(3),

);

After inserting data try to fetch the data and see the result. As of now we added data for one institute code and same date.

Let's create procedure to generate XML files. In this procedure we use simple SQL cursor using concatenation operator to generate full XML string. We also use two parameters institute code and date of billing. As this process run every day after close of business (COB). Then we use utl_file utility which is built-in in oracle PL SQL to generate files.

These files will generate on a folder from where it can be pickup manually or via ftp then send to xyz company 

One more thing as these files generate into database folders we need to create a database directory. You can use create database directory command via DBA user.

Please search on Google how to do it in oracle to get some good knowledge related to this. 

This SB directory use in this process named as db_dir, if you create with different name change it in procedure. If you already have any directory then change the value for related variable v_dir.

Procedure to generate XML files

create or replace procedure (p_tran_date date, p_inst_code varchar2) is 

cursor c1 is 

           select '<?xml version="1.0" encoding="utf-8"?>' || chr(10) ||

           '<ns0:mt_fbl_sdd_file_rsp xmlns:ns0="urn:ooms:dev/xi">' || chr(10) ||

           '<record>' || chr(10) ||

           xmlelement("tran_id", tran_id) || chr(10) ||

           xmlelement("date", to_char(tran_date, 'yyyymmdd')) || chr(10) ||

           xmlelement("amount", tran_amt) || chr(10) ||

           xmlelement("transaction_state", 'y')) || chr(10) ||

           '</record>' || chr(10) ||

           '</ns0:mt_fbl_sdd_file_rsp>' xml_value,

       from bill_tran_hist 

       where inst_code = p_inst_code

       and tran_date = p_tran_date

;

  v_cnt number := 0;

  fh utl_file.file_type;

  v_dir      varchar2(500) := 'db_dir';

begin

  

FOR cc IN c1 LOOP

      fh := utl_file.fopen(v_dir, 'xml-'|| TO_CHAR(sysdate, 'DDMMYYYY') || '.xml', 'w',32767);

      UTL_FILE.PUT(Fh,cc.xml_value);

      UTL_FILE.FCLOSE(Fh);

      v_cnt := v_cnt + 1;

end loop;

dbms_output.put_line(v_cnt || ' XML files generated'); --you can add log into any log table

exception 

  when others then

    utl_file.fclose(fh);

    dbms_output.put_line('error : ' ||sqlerrm); --you can add log into any log table

    

end;

After creating the procedure you can run it manually from SQL to check the results and automate via DBA scheduler to run every day after COB time.

We can use simple cursor with columns then use club it using a varchar variable then out it in file but the SQL give more power and ease of use 

Hopefully you get some good ideas and understanding how to use PL SQL in real life business problem.

Stay tune for more.