 |
| how to call webservice from oracle 10g database base on xml and json fromat? |
:) Hi there i wanna share a little my experience about Oracle 10g calling web service. . .
It was made my headache. There are a lot variable that i have to learn and from what i have to start, there are so many thing here.
After all it was. i have a basic concept How to call webservice, How to gran the data, and use it on your own database table.
First i have to tell you about oracle support on web service experience.
Oracle 10g
does not support web service base on
json format.
:( but wait a minute,
Your Oracle 10g still has read your web service even it base on json format with a little
trick.
:):):) by using an external web base program like xampp, lampp and on your oracle still has read json format.
Normally oracle 10g does not support json but
it can read xml format very good.
How to oracle 10g can read json format from web service is we have to use our own local web service and read the cloud web service that you wanna go.
After that, your local web service will receive json format fram cloud web service and you have convert json to xml format and done. :)
There are so many web service that you can choose, in my case i use
slim web service.
I will have you the schema to make you easy to learn how it work in my machine :
1. Oracle 10g-->Local Web Service-->(calling Cloud Web Service)
2.(Cloud Web Service sends json Format)-->Local Web Service(receive json and convert it to xml fromat)-->Oracle 10g extract xml to data.
There are two basic idea how to oracle 10g read json format.
Then how to set it???
1.
Install Slim web service.
(i will not explain how to install slim coz you can googling easily)
2.
-----------------------------------------------------------------------------index.php
$app->post('/GetByNoKartu', 'GetByNoKartu');
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
-----------------------------------------------------------------------------
function GetByNoKartu($request, $response, $args) {
$ConsId = $request->getParsedBody()['consid'];
$SecretKey = $request->getParsedBody()['secretkey'];
$Url = $request->getParsedBody()['url'];
$NoKartu = $request->getParsedBody()['nokartu'];
// Computes the timestamp
date_default_timezone_set('UTC');
$tStamp = strval(time()-strtotime('1970-01-01 00:00:00'));
$signature = hash_hmac('sha256', $ConsId."&".$tStamp, $SecretKey, true);
$encodedSignature = base64_encode($signature);
$curl = curl_init();
$arrheader = array(
'X-cons-id: '.$ConsId,
'X-timestamp: '.$tStamp,
'X-signature: '.$encodedSignature
);
$Turl = $Url;
$curl = curl_init();
curl_setopt($curl,CURLOPT_URL,$Turl);
curl_setopt($curl,CURLOPT_RETURNTRANSFER,true);
curl_setopt($curl, CURLOPT_HTTPHEADER, $arrheader);
$result = curl_exec($curl);
curl_close($curl);
//echo $result;
// $response->write($result);
//return $response;
// Require Array2XML class which takes a PHP array and changes it to XML
require_once('array2xml.php');
// Gets JSON file
// Decodes JSON into a PHP array
$php_array = json_decode($result, true);
// adding Content Type
header("Content-type: text/xml");
// Converts PHP Array to XML with the root element being 'root-element-here'
$xml = Array2XML::createXML('myroot', $php_array);
echo $xml->saveXML();
}
-------------------------------------------------------------------------------------------------------------------------------
-------------------------------------------------------------------------------------------------------------------------------
Procedure on oracle to read local webservice and send request to cloud web service.
I share 2 procedure to read xml base on one record and multiple records
-------------------------------------------------------------------------------------------------------------------------------
3.
-------------------------------------------------------------------------------------------------------------------------------
one record
-------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE procedure RS.brproc_cari_ktsp(vnokartu in varchar2,vckpk in varchar2,vtglsep in varchar2)
as
--var base url
vbase_consid varchar2(100);
vbase_secretkey varchar2(100);
vbase_url varchar2(4000);
vbase_url_jadi varchar2(4000);
vbase_url_local varchar2(4000);
vbase_nokartu varchar2(100);
--var utl http
req utl_http.req;
resp utl_http.resp;
value1 clob;
Lvalue1 number;
vresult clob;
v_url varchar2(4000);
v_post varchar2(4000);
--var coloumns
CKcode varchar2(100);
CKmessage varchar2(1000);
CKdinsos varchar2(100);
CKiuran varchar2(100);
CKnoSKTM varchar2(100);
CKprolanisPRB varchar2(100);
CKkdJenisPeserta varchar2(100);
CKnmJenisPeserta varchar2(100);
CKkdKelas varchar2(100);
CKnmKelas varchar2(100);
CKnama varchar2(100);
CKnik varchar2(100);
CKnoKartu varchar2(100);
CKnoMr varchar2(100);
CKpisa varchar2(100);
CKkdCabang varchar2(100);
CKkdProvider varchar2(100);
CKnmCabang varchar2(100);
CKnmProvider varchar2(100);
CKsex varchar2(100);
CKketerangan varchar2(100);
CKkode varchar2(100);
CKtglCetakKartu varchar2(100);
CKtglLahir varchar2(100);
CKtglTAT varchar2(100);
CKtglTMT varchar2(100);
CKumurSaatPelayanan varchar2(100);
CKumurSekarang varchar2(100);
CKnoTelepon varchar2(100);
BEGIN
--find base url
select base_url,base_url_local,base_consid,base_secretkey into vbase_url,vbase_url_local,vbase_consid,vbase_secretkey from BRMST_BASEBRIDSEPS;
vbase_url_jadi:=vbase_url||'Peserta/nokartu/'||vnokartu||'/tglSEP/'||vtglsep;
--post var value
v_post:='consid='||vbase_consid||'&'||'secretkey='||vbase_secretkey||'&'||'url='||vbase_url_jadi;
v_url:=vbase_url_local||'GetByNoKartu';
--v_url:='http://localhost/bridgingbpjs/contohpeserta.xml';
req := utl_http.begin_request(v_url,'POST');
UTL_HTTP.SET_HEADER(req,'User-Agent','Mozilla/4.0');
UTL_HTTP.SET_HEADER (req,'Content-Type','application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER (req,'Content-Length',length(v_post));
UTL_HTTP.WRITE_TEXT (req,v_post);
resp := utl_http.get_response(req);
vresult := EMPTY_CLOB;
--get length clob
select dbms_lob.getlength(value1) into Lvalue1 from dual;
LOOP
UTL_HTTP.READ_TEXT(resp, value1, Lvalue1);
vresult := vresult || value1;
END LOOP;
utl_http.end_response(resp);
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(resp);
--extrack xml's value
--extracting multiple node from xml
FOR i IN
(
select CK_CODE
,CK_MESSAGE
,CK_NMKELAS
,CK_KDKELAS
,CK_DINSOS
,CK_NOSKTM
,CK_PROLANISPRB
,CK_NMJENISPESERTA
,CK_KDJENISPESERTA
,CK_NOMR
,CK_NOTELEPON
,CK_NAMA
,CK_NIK
,CK_NOKARTU
,CK_PISA
,CK_KDPROVIDER
,CK_NMPROVIDER
,CK_SEX
,CK_KETERANGAN
,CK_KODE
,CK_TGLCETAKKARTU
,CK_TGLLAHIR
,CK_TGLTAT
,CK_TGLTMT
,CK_UMURSAATPELAYANAN
,CK_UMURSEKARANG
FROM XMLTABLE('/myroot'
PASSING
xmltype(vresult)
COLUMNS
--describe columns and path to them:
--describe columns and path to them:
CK_CODE varchar2(200) PATH'./metaData/code',
CK_MESSAGE varchar2(200) PATH'./metaData/message',
CK_NMKELAS varchar2(200) PATH'./response/peserta/hakKelas/keterangan',
CK_KDKELAS varchar2(200) PATH'./response/peserta/hakKelas/kode',
CK_DINSOS varchar2(200) PATH'./response/peserta/informasi/dinsos',
CK_NOSKTM varchar2(200) PATH'./response/peserta/informasi/noSKTM',
CK_PROLANISPRB varchar2(200) PATH'./response/peserta/informasi/prolanisPRB',
CK_NMJENISPESERTA varchar2(200) PATH'./response/peserta/jenisPeserta/keterangan',
CK_KDJENISPESERTA varchar2(200) PATH'./response/peserta/jenisPeserta/kode',
CK_NOMR varchar2(200) PATH'./response/peserta/mr/noMR',
CK_NOTELEPON varchar2(200) PATH'./response/peserta/mr/noTelepon',
CK_NAMA varchar2(200) PATH'./response/peserta/nama',
CK_NIK varchar2(200) PATH'./response/peserta/nik',
CK_NOKARTU varchar2(200) PATH'./response/peserta/noKartu',
CK_PISA varchar2(200) PATH'./response/peserta/pisa',
CK_KDPROVIDER varchar2(200) PATH'./response/peserta/provUmum/kdProvider',
CK_NMPROVIDER varchar2(200) PATH'./response/peserta/provUmum/nmProvider',
CK_SEX varchar2(200) PATH'./response/peserta/sex',
CK_KETERANGAN varchar2(200) PATH'./response/peserta/statusPeserta/keterangan',
CK_KODE varchar2(200) PATH'./response/peserta/statusPeserta/kode',
CK_TGLCETAKKARTU varchar2(200) PATH'./response/peserta/tglCetakKartu',
CK_TGLLAHIR varchar2(200) PATH'./response/peserta/tglLahir',
CK_TGLTAT varchar2(200) PATH'./response/peserta/tglTAT',
CK_TGLTMT varchar2(200) PATH'./response/peserta/tglTMT',
CK_UMURSAATPELAYANAN varchar2(200) PATH'./response/peserta/umur/umurSaatPelayanan',
CK_UMURSEKARANG varchar2(200) PATH'./response/peserta/umur/umurSekarang'
) xmlt )
LOOP
--insert xml value into table
insert into BRMST_CARI_KTSPS
(CK_pk,
CK_code,
CK_message,
CK_dinsos,
CK_iuran,
CK_noSKTM,
CK_prolanisPRB,
CK_kdJenisPeserta,
CK_nmJenisPeserta,
CK_kdKelas,
CK_nmKelas,
CK_nama,
CK_nik,
CK_noKartu,
CK_noMr,
CK_pisa,
CK_kdCabang,
CK_kdProvider,
CK_nmCabang,
CK_nmProvider,
CK_sex,
CK_keterangan,
CK_kode,
CK_tglCetakKartu,
CK_tglLahir,
CK_tglTAT,
CK_tglTMT,
CK_umurSaatPelayanan,
CK_umurSekarang,
CK_noTelepon)
values
(vckpk,
i.CK_code,
i.CK_message,
i.CK_dinsos,
null,
i.CK_noSKTM,
i.CK_prolanisPRB,
i.CK_kdJenisPeserta,
i.CK_nmJenisPeserta,
i.CK_kdKelas,
i.CK_nmKelas,
i.CK_nama,
i.CK_nik,
i.CK_noKartu,
i.CK_noMr,
i.CK_pisa,
null,
i.CK_kdProvider,
null,
i.CK_nmProvider,
i.CK_sex,
i.CK_keterangan,
i.CK_kode,
i.CK_tglCetakKartu,
i.CK_tglLahir,
i.CK_tglTAT,
i.CK_tglTMT,
i.CK_umurSaatPelayanan,
i.CK_umurSekarang,
i.CK_noTelepon);
END LOOP;
COMMIT;
END brproc_cari_ktsp;
/
-------------------------------------------------------------------------------------------------------------------------------
multiple record
-------------------------------------------------------------------------------------------------------------------------------
CREATE OR REPLACE procedure RS.brproc_poli(vcaripoli in varchar2)
as
--var base url
vbase_consid varchar2(100);
vbase_secretkey varchar2(100);
vbase_url varchar2(4000);
vbase_url_jadi varchar2(4000);
vbase_url_local varchar2(4000);
--var utl http
req utl_http.req;
resp utl_http.resp;
value1 clob;
Lvalue1 number;
vresult clob;
v_url varchar2(4000);
v_post varchar2(4000);
--var coloumns
CKkdPoli varchar2(100);
CKnmPoli varchar2(100);
BEGIN
--find base url
select base_url,base_url_local,base_consid,base_secretkey into vbase_url,vbase_url_local,vbase_consid,vbase_secretkey from BRMST_BASEBRIDSEPS;
vbase_url_jadi:=vbase_url||'referensi/poli/'||vcaripoli;
--post var value
v_post:='consid='||vbase_consid||'&'||'secretkey='||vbase_secretkey||'&'||'url='||vbase_url_jadi;
--v_url:='http://localhost/bridgingbpjs/contohpoli.xml';
v_url:=vbase_url_local||'GetPoli';
req := utl_http.begin_request(v_url,'POST');
UTL_HTTP.SET_HEADER(req,'User-Agent','Mozilla/4.0');
UTL_HTTP.SET_HEADER (req,'Content-Type','application/x-www-form-urlencoded');
UTL_HTTP.SET_HEADER (req,'Content-Length',length(v_post));
UTL_HTTP.WRITE_TEXT (req,v_post);
resp := utl_http.get_response(req);
vresult := EMPTY_CLOB;
--get length clob
select dbms_lob.getlength(value1) into Lvalue1 from dual;
LOOP
UTL_HTTP.READ_TEXT(resp, value1, Lvalue1);
vresult := vresult || value1;
END LOOP;
utl_http.end_response(resp);
EXCEPTION
WHEN utl_http.end_of_body THEN
utl_http.end_response(resp);
-- delete log table BRMST_POLIS
delete from BRMST_POLIS;
commit;
--extrack xml's value
--extracting multiple node from xml
FOR i IN
(
select kdPoli,nmPoli
FROM XMLTABLE('/myroot/response/poli'
PASSING
xmltype(vresult)
COLUMNS
--describe columns and path to them:
kdPoli varchar2(120) PATH './kode',
nmPoli varchar2(120) PATH './nama'
) xmlt
)
LOOP
--insert xml value into table
insert into BRMST_POLIS(kdpoli,nmPoli)values(i.kdPoli,i.nmPoli);
END LOOP;
COMMIT;
END brproc_poli;
/
-------------------------------------------------------
