how to call webservice from oracle 10g database base on xml and json fromat?

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;
/
-------------------------------------------------------

DBlink from oracle 10g/XE to sql server

How to create database Link Oracle 10g / XE to MYSQL Server

:) Hi there, a few days ago, i had a problem that makes me to use oracle 10g dblink to sql server.
This is my first time to do this and i do not sleep around 4 days for finding how to do this properly.
When i tried the procedures that i searched on google. . .there are so many errors i faced. at last fortunately i managed connecting oracle to MYSQL server. . . :)

I,m Using

OS: WIN7
ODBC DSN : hsodbc
DB1 : oracle XE
DB2 :MYSQL Server
DB2 : user "a" pass "a"

Database link from oracle 10g to sql server :

1.Setting up a system DSN(Data Source Name)
2.Setting hsodbc(oracle XE/10g)/dg4odbc(oracle11g-12c)
3.Setting tnsname.ora / listener.ora
4.Create db link to MYSQL
done!

1.

Configure ODBC by setting up a system DSN(Data Source Name)
Typically you can find this at
Start->Settings->Control Panel->Administrative Tools->Data Sources(ODBC)

2.

Look for inithsodbc.ora in $ORACLE_HOME/hs/admin/
Make a copy of it and rename it to init<your odbc Data Source Name>.ora
Remember we had chosen "hsodbc" as our DSN.
Open the file and enter the DSN that you just created recently i.e "hsodbc".
This is how $ORACLE_HOME/hs/admin/inithsodbc.ora should look like:

# This is a sample agent init file that contains the HS parameters that are
# needed for an ODBC Agent. 

#
# HS init parameters
#
HS_FDS_CONNECT_INFO = hsodbc
HS_FDS_TRACE_LEVEL = 0


#
# Environment variables required for the non-Oracle system
#
#set <envvar>=<value>

 3.

Configure listener.ora
$ORACLE_HOME/network/admin/listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (SID_NAME = hsodbc)
      (ORACLE_HOME = C:\oraclexe\app\oracle\product\10.2.0\server)
      (PROGRAM = hsodbc)
    )
  )

Note: SID_NAME should be your DSN name. PROGRAM=hsodbc or dg4odbc tells Oracle to use heterogenous services.

4.

Configure tnsnames.ora
$ORACLE_HOME/network/admin/tnsnames.ora

HSODBC =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = Lab)(PORT = 1521))
    (CONNECT_DATA = (SID = hsodbc))
      (HS = OK)
    )

HOST = Oracle Host
PORT = Oracle PORT
SID = your  DSN name

Restart your listener to make sure the settings are in effect.

lsnrctl stop
lsnrctl start

You can now validate the connection to your SQL Server database by the normal Oracle tnsping utility.

tnsping hsodbc

TNS Ping Utility for 32-bit Windows: Version 10.1.0.2.0 - Production on 01-DEC-2004 13:19:54

Copyright (c) 1997, 2003, Oracle.  All rights reserved.

Used TNSNAMES adapter to resolve the alias
Attempting to contact 

OK (30 msec)

5.

Make sure that global_names parameter is set to false
SQL> sho parameter global_names

NAME               TYPE             VALUE
------------------ ---------------- -------
global_names       boolean          FALSE

Create a database link:

CREATE DATABASE LINK <<dblinkname>> CONNECT TO <<user MYSQLServer>> IDENTIFIED BY <<password MYSQLServer>> USING '<<connection string>>';

Example:
SQL> CREATE DATABASE LINK hsodbc CONNECT TO a IDENTIFIED BY a USING 'hsodbc';

Database link created.

We have completed our configuration.

Example:
SQL> desc emp@hsodbc

 Name          Null?      Type
 ------------- --------   --------------
 EMPID         NOT NULL   NUMBER(1)
 EMPNAME                  VARCHAR2(60)
 CREATED                  DATE

Note :

Check Whether HSODBC is Installed

The first common error is people do not check the OracleDB has installed hsodbc/dg4odbc on the DB or not.

The full text of the error reported to us was:

ORA-28545: error diagnosed by Net8 when connecting to an agent
Unable to retrieve text of NETWORK/NCR message 65535
You may get this error if:

Oracle fails to connect to DG4ODBC/HSODBC. DG4ODBC/HSODBC cannot retrieve the underlying error from the ODBC driver.

open command prompt / terminal go to $ORALCE_HOME/bin

cd $ORALCE_HOME/bin
hsodbc

 you will see something similar to the following if HSODBC is installed:

hsoodbc: ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV)

If you get HSODBC: empty, your version of Oracle does not include HSODBC.

if you find this error

ORA-00942 table of view does not exist

MYSQLServer is not case sensitive as long as you omit the quotes. The following names are all identical:
TEST2, test2, Test2, TeSt2

The following tables are different:
"TEST2", "test2", "Test2"

So, use double quote (") between your table name, because oracle will send upper case command by default.

:) i hope it will help your problem.

#http://www.dbforums.com/showthread.php?1662981-Must-double-quote-table-name

#http://www.databasejournal.com/features/oracle/article.php/3442661/Making-a-Connection-from-Oracle-to-SQL-Server.htm