SQL & PL/SQL

Example of how to use PL/SQL to call a web service posted in .

Remember that ACL (access control list) entries are needed in 11g and later for allowing PL/SQL code TCP/IP access to the world outside the database.

If HTTPS is used, then an Oracle Wallet needs to be created (by the DBA) on the database server platform, and the wallet needs to contain the certificates of the web server.

Very important - modularise the code you write, and instrument the code you write.

thanks for your help and link, saw your post but one thing to ask, when you selected data with your select statement the column value returned contain XML coding also, how I can show data with Select statement which can be same as normal output of a Select statement.

Sorry, but I didn't worked yet with XML data.

regards.

Ahmed Haroon wrote:
when you selected data with your select statement the column value returned contain XML coding also, how I can show data with Select statement which can be same as normal output of a Select statement.

You may use XMLTABLE to extract data from XML in relational format.

thank you for reply.

there is also image in xml data, is there any special requirement to access it or just use a BLOB type column in table and store it in?

regards

Ahmed Haroon wrote:
there is also image in xml data, is there any special requirement to access it or just use a BLOB type column in table and store it in? 

I assume the image is encoded in base64?

You have to extract the corresponding node as CLOB, then convert the content from base64 to BLOB.

See the following threads :

https://community.oracle.com/message/13733179#13733179

https://community.oracle.com/message/14034020#14034020

I tried to change some lines of your provided code to get xml data through web service as below but getting error:

AMC.@.testdb > select GetCnicInfo ('4210116109017', null ) as Info from dual;

ERROR:

ORA-29269: HTTP server error 500 - Internal Server Error

ORA-06512: at "AMC.GETCNICINFO", line 87

Your modified function is :

create or replace function GetCnicInfo( cnic varchar2, area varchar2 ) return XmlType is
   --// URL to call 
   SOAP_URL constant varchar2(1000) := 'http://192.168.1.22/IBAS/IBAS.asmx'; 
         --'http://192.168.1.22/IBAS/IBAS.asmx';
   --// SOAP envelope template, containing $ substitution variables
   SOAP_ENVELOPE   constant varchar2(32767) :=
  '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" 
xmlns:web="http://192.168.1.22/IBAS/IBAS.asmx?op=GetClientInfoDirect">
     <soapenv:Header/>
     <soapenv:Body>
        <web:GetInfo>
           <web:CNIC>$cnic</web:CNIC>
           <web:Area>$area</web:AREA>
        </web:GetInfo>
     </soapenv:Body>
  </soapenv:Envelope>';
          --// we'll identify ourselves using an IE9/Windows7 generic browser signature
          C_USER_AGENT    constant varchar2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';
          --// these variables need to be set if web access
          --// is via a proxy server
          proxyServer varchar2(20) default null;
          proxyUser varchar2(20) default null;
          proxyPass varchar2(20) default null;
          --// our local variables
          soapEnvelope    varchar2(32767);
          proxyURL        varchar2(4000);
          request         utl_http.req;
          response        utl_http.resp;
          buffer          varchar2(32767);
          soapResponse    clob;
          xmlResponse     XmlType;
          eof             boolean;
  begin
          --// create the SOAP envelope
          soapEnvelope := replace( SOAP_ENVELOPE, '$CNIC', cnic );
          soapEnvelope := replace( soapEnvelope, '$AREA', area );
          --// our "browser" settings
          utl_http.set_response_error_check( true );
          utl_http.set_detailed_excp_support( true );
          utl_http.set_cookie_support( true );
          utl_http.set_transfer_timeout( 10 );
          utl_http.set_follow_redirect( 3 );
          utl_http.set_persistent_conn_support( true );
          --// configure for web proxy access if applicable
          if proxyServer is not null then
                  proxyURL := 'http://'||proxyServer;
                  if (proxyUser is not null) and (proxyPass is not null) then
                          proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
                  end if;
                   utl_http.set_proxy( proxyURL, null );
          end if;
          --// make the POST call to the web service
          request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );
          utl_http.set_header( request, 'User-Agent', C_USER_AGENT );
          utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );
          utl_http.set_header( request, 'Content-Length', length(soapEnvelope) );
          utl_http.set_header( request, 'SoapAction', 'http://192.168.1.22/IBAS/IBAS.asmx?op=GetClientInfoDirect' );
          utl_http.write_text( request, soapEnvelope );
          --// read the web service HTTP response
          response := utl_http.get_response( request );
          dbms_lob.CreateTemporary( soapResponse, true );
          eof := false;
          loop
                  exit when eof;
                  begin
                          utl_http.read_line( response, buffer, true );
                          if length(buffer) > 0 then
                                  dbms_lob.WriteAppend(
                                          soapResponse,
                                          length(buffer),
                                          buffer
                                  );
                          end if;
                  exception when utl_http.END_OF_BODY then
                          eof := true;
                  end;
          end loop;
          utl_http.end_response( response );
          --// as the SOAP responds with XML, we convert
          --// the response to XML
          xmlResponse := XmlType( soapResponse );
          dbms_lob.FreeTemporary( soapResponse );
          return( xmlResponse );
 exception when OTHERS then
          if soapResponse is not null then
                  dbms_lob.FreeTemporary( soapResponse );
          end if;
          raise;
  end;
/

we are using character set:

AMC.@.testdb > SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;
VALUE$
---------------------------------------------------------------------------------
WE8MSWIN1252

please guide, I am going to use these first time. also about User Agent.

regards.

modified further as below:

create or replace function GetCnicInfo( client_cnic varchar2, client_contactNo varchar2, client_area varchar2 ) return XmlType is
   --// URL to call 
   SOAP_URL constant varchar2(1000) := 'http://192.168.1.22/IBAS/IBAS.asmx'; 
         --'http://192.168.1.22/IBAS/IBAS.asmx';
   --// SOAP envelope template, containing $ substitution variables
   SOAP_ENVELOPE   constant varchar2(32767) :=
  '<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" 
xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetClientInfoDirect xmlns="http://mybnk.com/">
      <CNIC>$client_cnic</CNIC>
      <contactNo>$client_contactNo</contactNo>
      <area>$client_area</area>
    </GetClientInfoDirect>
  </soap:Body>
</soap:Envelope>';
          --// we'll identify ourselves using an IE9/Windows7 generic browser signature
          C_USER_AGENT    constant varchar2(4000) := 'Mozilla/5.0 (compatible; MSIE 9.0; Windows NT 6.1; Trident/5.0)';
          --// these variables need to be set if web access
          --// is via a proxy server
          proxyServer varchar2(20) default null;
          proxyUser varchar2(20) default null;
          proxyPass varchar2(20) default null;
          --// our local variables
          soapEnvelope    varchar2(32767);
          proxyURL        varchar2(4000);
          request         utl_http.req;
          response        utl_http.resp;
          buffer          varchar2(32767);
          soapResponse    clob;
          xmlResponse     XmlType;
          eof             boolean;
  begin
          --// create the SOAP envelope
          soapEnvelope := replace( SOAP_ENVELOPE, '$CNIC', client_cnic );
          soapEnvelope := replace( soapEnvelope, '$contactNo', client_contactNo );
          soapEnvelope := replace( soapEnvelope, '$AREA', client_area );
          --// our "browser" settings
          utl_http.set_response_error_check( true );
          utl_http.set_detailed_excp_support( true );
          utl_http.set_cookie_support( true );
          utl_http.set_transfer_timeout( 10 );
          utl_http.set_follow_redirect( 3 );
          utl_http.set_persistent_conn_support( true );
          --// configure for web proxy access if applicable
          if proxyServer is not null then
                  proxyURL := 'http://'||proxyServer;
                  if (proxyUser is not null) and (proxyPass is not null) then
                          proxyURL := Replace( proxyURL, 'http://',  'http://'||proxyUser||':'||proxyPass||'@' );
                  end if;
                   utl_http.set_proxy( proxyURL, null );
          end if;
          --// make the POST call to the web service
          request := utl_http.begin_request( SOAP_URL, 'POST', utl_http.HTTP_VERSION_1_1 );
          utl_http.set_header( request, 'User-Agent', C_USER_AGENT );
          utl_http.set_header( request, 'Content-Type', 'text/xml; charset=utf-8' );
          --utl_http.set_header( request, 'Content-Type', 'text/xml; charset=WE8MSWIN1252' );
          utl_http.set_header( request, 'Content-Length', length(soapEnvelope) );
          utl_http.set_header( request, 'SoapAction', 'http://mybnk.com/GetClientInfoDirect' );
          utl_http.write_text( request, soapEnvelope );
          --// read the web service HTTP response
          response := utl_http.get_response( request );
          dbms_lob.CreateTemporary( soapResponse, true );
          eof := false;
          loop
                  exit when eof;
                  begin
                          utl_http.read_line( response, buffer, true );
                          if length(buffer) > 0 then
                                  dbms_lob.WriteAppend(
                                          soapResponse,
                                          length(buffer),
                                          buffer
                                  );
                          end if;
                  exception when utl_http.END_OF_BODY then
                          eof := true;
                  end;
          end loop;
          utl_http.end_response( response );
          --// as the SOAP responds with XML, we convert
          --// the response to XML
          xmlResponse := XmlType( soapResponse );
          dbms_lob.FreeTemporary( soapResponse );
          return( xmlResponse );
 exception when OTHERS then
          if soapResponse is not null then
                  dbms_lob.FreeTemporary( soapResponse );
          end if;
          raise;
  end;

now getting this output on SQL-Plus window

AMC.@.testdb > select GetCnicInfo ('4210116109017', null, null) info from dual;
INFO
----------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/env
.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetClientInfo
ah.com/"><GetClientInfoDirectResult>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;CITIZEN_VERIFIC
rg/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;  &lt;RESPONSE_DATA&gt; 
&lt;CODE&gt;107&lt;/CODE&gt;      &lt;MESSAGE&gt;invalid citizen nummber&lt;/MESSAGE&gt;    &lt;/RESPON
ID /&gt;    &lt;CITIZEN_NUMBER&gt;$client_cnic&lt;/CITIZEN_NUMBER&gt;  &lt;/RESPONSE_DATA&gt;&lt;/CI
tInfoDirectResult></GetClientInfoDirectResponse></soap:Body></soap:Envelope>

this is what they have sent us to access data Soap Envelope

POST /IBAS/IBAS.asmx HTTP/1.1
Host: 192.168.1.22
Content-Type: text/xml; charset=utf-8
Content-Length: length
SOAPAction: "http://mybnk.com/GetClientInfoDirect"
<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
  <soap:Body>
    <GetClientInfoDirect xmlns="http://mybnk.com/">
      <CNIC>string</CNIC>
      <contactNo>string</contactNo>
      <area>string</area>
    </GetClientInfoDirect>
  </soap:Body>
</soap:Envelope>

at last i got data but it is not in readable format, one column contain Image

select GetCnicInfo ('4210116109017', null, 'sindh' ) as Info from dual
AMC.@.testdb > /
INFO
----------------------------------------------------------------------------------------------------
<?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/env
.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><GetClientInfo
ah.com/"><GetClientInfoDirectResult>&lt;?xml version="1.0" encoding="utf-16"?&gt;&lt;CITIZEN_VERIFIC
rg/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"&gt;  &lt;RESPONSE_DATA&gt; 
&lt;CODE&gt;100&lt;/CODE&gt;      &lt;MESSAGE&gt;successful&lt;/MESSAGE&gt;    &lt;/RESPONSE_STATUS&gt;
000000054679&lt;/SESSION_ID&gt;    &lt;CITIZEN_NUMBER&gt;4210116109017&lt;/CITIZEN_NUMBER&gt;    &lt;P
t;¿¿¿¿ ¿¿¿¿&lt;/NAME&gt;      &lt;FATHER_HUSBAND_NAME&gt;¿¿¿ ¿¿¿¿¿&lt;/FATHER_HUSBAND_NAME&gt;      &
¿¿¿¿a-166¿¿¿ ¿¿¿¿¿/¿¿¿¿¿¿ ¿¿¿¿¿¿c ¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿
¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿&lt;/PRESENT_ADDRESS&gt;      &lt;PERMANANT_ADDRESS&gt;¿¿¿¿¿¿ ¿¿¿¿¿¿a-166¿¿¿
¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿ ¿¿¿¿¿¿¿¿¿¿¿¿¿ ¿¿¿¿¿¿¿ ¿¿¿¿¿¿ ¿¿
SS&gt;      &lt;DATE_OF_BIRTH&gt;1980-12-04&lt;/DATE_OF_BIRTH&gt;      &lt;GENDER&gt;male&lt;/GENDER&
4AAQSkZJRgABAQECWAJYAAD/4QBuRXhpZgAATU0AKgAAAAgAAYdpAAQAAAABAAAAGgAAAAAAAZKGAAcAAAA6AAAALAAAAABVTklD
ACAAQQBjAGMAdQBTAG8AZgB0ACAAQwBvAHIAcAAu/9sAQwAIBgYHBgUIBwcHCQkICgwUDQwLCwwZEhMPFB0aHx4dGhwcICQuJyAi
/9sAQwEJCQkMCwwYDQ0YMiEcITIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIyMjIy/8AAEQgB
AQEBAAAAAAAAAAABAgMEBQYHCAkKC//EALUQAAIBAwMCBAMFBQQEAAABfQECAwAEEQUSITFBBhNRYQcicRQygZGhCCNCscEVUtHw
OkNERUZHSElKU1RVVldYWVpjZGVmZ2hpanN0dXZ3eHl6g4SFhoeIiYqSk5SVlpeYmZqio6Slpqeoqaqys7S1tre4ubrCw8TFxsfI
9PX29/j5+v/EAB8BAAMBAQEBAQEBAQEAAAAAAAABAgMEBQYHCAkKC//EALURAAIBAgQEAwQHBQQEAAECdwABAgMRBAUhMQYSQVEH
JDThJfEXGBkaJicoKSo1Njc4OTpDREVGR0hJSlNUVVZXWFlaY2RlZmdoaWpzdHV2d3h5eoKDhIWGh4iJipKTlJWWl5iZmqKjpKWm
1NXW19jZ2uLj5OXm5+jp6vLz9PX29/j5+v/aAAwDAQACEQMRAD8A9/ooooAKKKKACiiigAooooAKKKKACiiigAooooAKKKKACiii
iJpv9d+FPg6t/u0UVIdCvD94/Srb/wCqj/H+dFFWJbkcP3x9D/Kqdt/rz9aKKmW6H0NB/vr/ALop1x/yDT/vUUVXQOpFov8AyEI/
+ub/AHG/lRRTkNFqD7gq9df8edt9DRRV9iV1P//Z&lt;/PHOTOGRAPH&gt;      &lt;SIGNATURE /&gt;      &lt;EXPIRY_DATE&g
ATE&gt;    &lt;/PERSON_DATA&gt;  &lt;/RESPONSE_DATA&gt;&lt;/CITIZEN_VERIFICATION&gt;</GetClientInfoD
tResponse></soap:Body></soap:Envelope>

now want guidance to have data in readable format, a problem is there name, father name etc. are not in English these are in Urdu language, how i can translate these data and store image in table to show through oracle forms 10g.

regards.

Ahmed,

now want guidance to have data in readable format, a problem is there name, father name etc. are not in English these are in Urdu language, how i can translate these data and store image in table to show through oracle forms 10g.

I've already directed you to two threads that deal with extracting data from XML, including image data.

Have you reviewed those yet?

However, you have a more serious problem here.

Your database uses WE8MSWIN1252 character set, but you're receiving a response in UTF-8.

WINDOWS-1252 doesn't support Urdu characters (0600 - 06FF range in the Unicode standard).

@"odie_63"

thank you for the links and reply to guide us.

one thing please i failed to get WINDOWS-1252, is it WinXP or what is it? sorry, but i don't know about it.

will try to play around to extract data as required and you have given two links for, when succeed or failure will inform here as above for what I posted related to access / consume web service help by Billy Verreynne

regards.

i tried as below but always it returned no data found ( data for the given parameters returned which showed in reply # 8 above )

declare
  mCNIC  VARCHAR2(50) := '4210116109017';
  mAREA  VARCHAR2(50) := 'sindh';
  mNULL  VARCHAR2(10) := NULL;
  --
  l_xml    xmltype;
  vNAME    VARCHAR2(100);
  vFH_NAME VARCHAR2(100);
  vDOB     VARCHAR2(100);
  vGENDER  VARCHAR2(100);
begin
  -- 
  select GetCnicInfo ( mCNIC, mNULL, mAREA ) Into l_xml from dual;
  -- 
  select x.*
    into vNAME, vFH_NAME, vDOB, vGENDER
    from xmltable(
         '/CITIZEN_VERIFICATION/RESPONSE_DATA/PERSON_DATA'
         passing l_xml
         columns NAME                 varchar2(100)  path 'NAME'
               , FATHER_HUSBAND_NAME  varchar2(100)  path 'FATHER_HUSBAND_NAME'
               , DATE_OF_BIRTH        varchar2(100)  path 'DATE_OF_BIRTH'
               , GENDER               varchar2(100)  path 'GENDER'
       ) x ;
  --
  dbms_output.put_line(vNAME||', '||vFH_NAME||', '||vDOB||', '||vGENDER);
  --
end;

our .NET developer informed about columns we have in it as below:

<?xml version="1.0" encoding="utf-16"?>
<CITIZEN_VERIFICATION xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
  <RESPONSE_DATA>
    <RESPONSE_STATUS>
      <CODE>100</CODE>
      <MESSAGE>successful</MESSAGE>
    </RESPONSE_STATUS>
    <SESSION_ID>1806100000000054685</SESSION_ID>
    <CITIZEN_NUMBER>4210119499329</CITIZEN_NUMBER>
    <PERSON_DATA>
      <NAME>????? ???????</NAME>
      <FATHER_HUSBAND_NAME>???? ???????</FATHER_HUSBAND_NAME>
      <PRESENT_ADDRESS>????? ?????B-102?? ????? 16?? ????? ?????????? ???? ??? ??????? ?????? ????</PRESENT_ADDRESS>
      <PERMANANT_ADDRESS>????? ?????B-102?? ????? 16?? ????? ?????????? ???? ??? ??????? ?????? ????</PERMANANT_ADDRESS>
      <DATE_OF_BIRTH>1980-07-12</DATE_OF_BIRTH>
      <GENDER>male</GENDER>
      <PHOTOGRAPH>/9j/4AAQ//2Q==</PHOTOGRAPH>
      <SIGNATURE />
      <EXPIRY_DATE>2021-03-22</EXPIRY_DATE>
    </PERSON_DATA>
  </RESPONSE_DATA>
</CITIZEN_VERIFICATION>

where i made mistake, what i mis-understood your provided example, definitely there is something wrong from me.

please help as usual.

regards

Ahmed Haroon wrote:
one thing please i failed to get WINDOWS-1252, is it WinXP or what is it? sorry, but i don't know about it.

It's the character set.

WINDOWS-1252 = WE8MSWIN1252

The first one is IANA standard naming, whereas the second one is Oracle's naming convention.

thank you odie_63

please guide on my reply # 11 in which i modified your code to extract data from xmltype but failed.

for Characterset problem I have forwarded what you mentioned about,  now responsible persons here will take care of it

regards

where i made mistake, what i mis-understood your provided example, definitely there is something wrong from me.

Make sure what your l_xml variable contains.

Given your previous posts, I was under the impression that the GetCnicInfo() function returned something like this (complete soap envelope) :

<?xml version="1.0" encoding="utf-8"?>
<soap:Envelope xmlns:soap="..."

In this case you first need to extract the envelope payload (<CITIZEN_VERIFICATION>...</CITIZEN_VERIFICATION>) as a standalone XML document, then you'll be able to parse it using the query you showed.

how I can do it? will you please help how to write code for it?

regards

Ahmed Haroon wrote:
how I can do it? will you please help how to write code for it?

So, does the xmltype variable contain a SOAP Envelope, or just the CITIZEN_VERIFICATION document?

Can you post the full content to lift ambiguity?

I can give you a working example but the sample function result you posted earlier is truncated, so we may be missing some tags or namespace declarations.