Skip to Main Content

Analytics Software

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Search Prompt

884921Sep 14 2011 — edited Sep 21 2011
Hi Gurus,

i got new requirement in prompt level when i enter any letter i need to get related values starting with that letter in drop down.for example in IRCTC site when we enter starting three letters of station name then in drop down we are getting related station names. my requirement is also samething.please help me to come out from this. i am using 10.3.2 version.

Comments

Billy Verreynne

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.

Ahmed Haroon

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.

odie_63

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.

Ahmed Haroon

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

odie_63

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

Ahmed Haroon

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.

Ahmed Haroon

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>

Ahmed Haroon

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.

odie_63

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).

Ahmed Haroon

@"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.

Ahmed Haroon

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

odie_63

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.

Ahmed Haroon

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

odie_63

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.

Ahmed Haroon

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

regards

odie_63

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.

report error:
Oops, something went wrong!  We've notified our team to take a look. Please try again later. (Reference 300000.25.174.39748).
Locked Post
New comments cannot be posted to this locked post.