Oracle PL SQL real world example - Generate XML Files
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.
0 Comments