Skip to Main Content
  • Questions
  • how to fix getting unreadble characters in utl_http response using oracle database?

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, omar.

Asked: June 13, 2020 - 8:34 am UTC

Answered by: Chris Saxon - Last updated: June 19, 2020 - 1:47 pm UTC

Category: PL/SQL - Version: Release 19.0.0.0.0 - Production Version 19.3.0.0.0

Viewed 100+ times

You Asked

I'm using oracle 19c database character set AR8MSWIN1256 ON WINDOWS I have used UTL_HTTP to get a soap response but I am getting invalid characters also i have switched to AMERICAN_AMERICA.AL32UTF8 on another database but with no luck im getting the following
<?xml version="1.0" encoding="UTF-8"?>
    <soapenv:Envelope xmlns:soapenc="http://schemas.xmlsoap.org/soap/encoding/" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"><soapenv:Body><xrsi:h2h-das-reply xmlns:xrsi="http://www.westernunion.com/schema/xrsi"><MTML><REPLY HOST="AUHOST" CLIENT="WUCLIENT"
    TYPE=""><DATA_CONTEXT><HEADER><ACCOUNT_NUM>AJX129993</ACCOUNT_NUM><DATA_MORE>N</DATA_MORE><DATA_NUM_RECS>56</DATA_NUM_RECS><NAME>GetStateList</NAME><FSID>WGHHJOI390T</FSID><COUNTER_ID>JOI39ARP001D</COUNTER_ID><TERM_ID>WAQt</TERM_ID></HEADER><RECORDSET><GETSTATELIST><STATE_CODE>AL</SaR?EA9????????AB{q?kJt4
    ??/Bh??Y???p????^?H?4%??p????d?mY;J?g?????w?<??@?????7?kQ???$???h+?zpS????{~*????o8/9?????p#?IK?8e?S?8?a????????+?k@???H???#???_??}U???W)????I??s^    .?"?`????M??????? ?ZsS?>?c??????[?0[?G[??4???s/%}?vi*9?$~????0?????l?d???????LFF"???????_A?F-Qs%??

here is my plsql code:
declare
    url varchar2(2000):='https://wugateway2pi.westernunion.net';
    body varchar2(32000):='
    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xrsi="http://www.westernunion.com/schema/xrsi" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
    <soapenv:Header/><soapenv:Body><xrsi:h2h-das-request><channel><type>H2H</type><name>abc</name>
    <version>141</version></channel><foreign_remote_system><identifier>xyz</identifier>
    <reference_no>1213</reference_no><counter_id>xyz</counter_id>
    </foreign_remote_system><name>GetStateList</name><filters><queryfilter1>en</queryfilter1>
    <queryfilter2>US</queryfilter2></filters></xrsi:h2h-das-request></soapenv:Body></soapenv:Envelope>';
    req UTL_HTTP.REQ;
    resp UTL_HTTP.RESP;
    myclob clob;
    buff varchar2(32767);  
    begin
    UTL_HTTP.set_wallet('file:D:\wallet',null);
    utl_http.SET_BODY_CHARSET('utf-8');
    UTL_HTTP.SET_TRANSFER_TIMEOUT(120);
    req:= UTL_HTTP.BEGIN_REQUEST(url, 'POST');
    UTL_HTTP.SET_HEADER (req,'Content-Type','text/xml;chartset=utf-8');
    UTL_HTTP.SET_HEADER (req,'Content-Length',lengthb(body));
    UTL_HTTP.WRITE_TEXT (req,body);
    resp:=UTL_HTTP.GET_RESPONSE(req);
    -----------
    DBMS_LOB.createtemporary(myclob, FALSE);
    BEGIN
    LOOP
    UTL_HTTP.READ_TEXT(resp, buff, 1000);
    dbms_output.put_line(buff);
    DBMS_LOB.WRITEAPPEND(myclob, length(buff), buff);
    END LOOP;
    UTL_HTTP.END_RESPONSE(resp);
    EXCEPTION
    WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(resp);
    END;
    dbms_output.put_line(myclob);
    DBMS_LOB.freetemporary(myclob);
    end;
    /

also i have tried the following
l_text:=convert(l_text,'AR8MSWIN1256','AL32UTF8');
utl_http.set_header(http_req, 'Content-Type', 'text/xml; charset=utf-8');
utl_http.set_header(http_req, 'Content-Type', 'application/xml; charset=utf-8');
utl_http.set_header(http_req, 'Content-Type', 'application/soap+xml;charset=UTF-8');
utl_http.set_header(http_req, 'Content-Type', 'text/xml');

but with no luck at all!

how can i fix this issue?

is it possible to be a matter of decryption erorrs becuase this site uses a mutual authentication
and using a private key ?

and we said...

First up, check that the API is working correctly.

What happens when you call the service using a third-party tool outside the database (e.g. Postman)? If you get the same result, then there's a problem with their responses.

I can't spot any obvious issues with your code. If the API works with other tools, add further logging to capture what's happening at each part of the program to pinpoint where exactly processing fails.

and you rated our response

  (3 ratings)

Reviews

mor info

June 17, 2020 - 6:10 am UTC

Reviewer: Omar sabatin

when I execute the same code in java or Soapui I get a valid response
so a created a java source and oracle procedure (wrapper) but I got errors
I think these errors about permissions I set all permissions I could set
but no success
the difference between the java code that worked and the oracle procedure is that in oracle I created a wallet by importing the certificates and using openssl
and in java I used a Keystore JKS sent by the web service provider
I think the problem lies here

Chris Saxon

Followup  

June 17, 2020 - 1:42 pm UTC

So... what errors did you get?

my wrapped procedure

June 17, 2020 - 2:45 pm UTC

Reviewer: Omar sabatin

here is the java source
CREATE OR REPLACE and resolve JAVA SOURCE NAMED "Western" AS
import java.io.*;
import java.sql.*;
import java.net.URL;
import java.security.KeyStore;
import java.security.KeyStore.SecretKeyEntry;
import javax.crypto.SecretKey;
import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLSession;
public class Western{
public static void west(String[] body,String[] result, String[] response){
String s=body[0];
String lenn = Integer.toString(s.length());
result[0]="ok";
System.setProperty("java.net.preferIPv4Stack" , "true");
System.setProperty("javax.net.debug", "all");
try {
String keyPath = "C:\\me\\pi-client-keystore.jks";
String keyPass = "BAS$!@S87";
String keyType = "JKS";
System.setProperty("javax.net.ssl.keyStore", keyPath);
System.setProperty("javax.net.ssl.keyStorePassword", keyPass);
System.setProperty("javax.net.ssl.keyStoreType", keyType);
URL url = new URL(" https://wugateway2pi.westernunion.net" );
HttpsURLConnection con = (HttpsURLConnection) url.openConnection();
con.setRequestMethod("POST");
con.setDoOutput( true );
con.setRequestProperty( "Content-Type", "text/xml" );
con.setHostnameVerifier(new HostnameVerifier()
{
public boolean verify(String hostname, SSLSession session)
{
return true;
}
});
con.connect();
OutputStreamWriter writer = new OutputStreamWriter(con.getOutputStream());
writer.write(s);
writer.flush();
writer.close();
BufferedReader in = new BufferedReader(new InputStreamReader(con.getInputStream()));
String inputLine;
StringBuffer buff = new StringBuffer();
while ((inputLine = in.readLine()) != null) {
buff.append(inputLine);
}
response[0]=buff.toString();
con.disconnect();
}
catch(Exception ex)
{
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
ex.printStackTrace(pw);
result[0]=sw.toString();
}
} }
/
the oracle procedure:
CREATE OR REPLACE procedure west (body IN out VARCHAR2,result in OUT VARCHAR2,resp in OUT VARCHAR2)
AS LANGUAGE JAVA
NAME 'Western.west(java.lang.String[],java.lang.String[],java.lang.String[])';
the error I get:
java.net.SocketException: connect failed, 10022
An invalid argument was
supplied.

at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at
java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79
)
at
java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
a
t
java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:2
06)
at
java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at
java.net.PlainSocketImpl.connect(PlainSocketImpl.java:255)
at
java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at
java.net.Socket.connect(Socket.java:589)
at
sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:666)
at
sun.security.ssl.BaseSSLSocketImpl.connect(BaseSSLSocketImpl.java:173)
at
sun.net.NetworkClient.doConnect(NetworkClient.java:180)
at
sun.net.www.http.HttpClient.openServer(HttpClient.java:463)
at
sun.net.www.http.HttpClient.openServer(HttpClient.java:558)
at
sun.net.www.protocol.https.HttpsClient.<init>(HttpsClient.java:264)
at
sun.net.www.protocol.https.HttpsClient.New(HttpsClient.java:367)
at
sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.getNewHttpClient(A
bstractDelegateHttpsURLConnection.java:191)
at
sun.net.www.protocol.http.HttpURLConnection.plainConnect0(HttpURLConnection.java
:1156)
at
sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:
1050)
at
sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.connect(AbstractDe
legateHttpsURLConnection.java:177)
at
sun.net.www.protocol.https.HttpsURLConnectionImpl.connect(HttpsURLConnectionImpl
.java:162)
at Western.west(Western:36)

This is the java code and i execute it from plsql

June 18, 2020 - 7:46 pm UTC

Reviewer: Omar sabatin from Usa

CREATE OR REPLACE and resolve JAVA SOURCE NAMED "Western" AS
import java.io.*;
import java.sql.*;
import java.net.URL;
import java.security.KeyStore;
import java.security.KeyStore.SecretKeyEntry;
import javax.crypto.SecretKey;
import javax.net.ssl.HostnameVerifier;
import javax.net.ssl.HttpsURLConnection;
import javax.net.ssl.SSLSession;
public class Western{
public static void west(String[] body,String[] result, String[] response){
String s=body[0];
String lenn = Integer.toString(s.length()); 
result[0]="ok";
System.setProperty("java.net.preferIPv4Stack" , "true");
System.setProperty("javax.net.debug", "all");
try {
String keyPath = "C:\\me\\pi-client-keystore.jks";
String keyPass = "BAS$!@S87";
String keyType = "JKS";
System.setProperty("javax.net.ssl.keyStore", keyPath);
System.setProperty("javax.net.ssl.keyStorePassword", keyPass);
System.setProperty("javax.net.ssl.keyStoreType", keyType);
URL url = new URL(" https://wugateway2pi.westernunion.net" ); 
HttpsURLConnection con = (HttpsURLConnection) url.openConnection();
con.setRequestMethod("POST");
con.setDoOutput( true );
con.setRequestProperty( "Content-Type", "text/xml" );
con.setHostnameVerifier(new HostnameVerifier()
{
public boolean verify(String hostname, SSLSession session)
{
return true;
}
});
con.connect();
OutputStreamWriter writer = new OutputStreamWriter(con.getOutputStream());
writer.write(s);
writer.flush();
writer.close();
BufferedReader in = new BufferedReader(new InputStreamReader(con.getInputStream()));
String inputLine;
StringBuffer buff = new StringBuffer();
while ((inputLine = in.readLine()) != null) {
buff.append(inputLine);
}
response[0]=buff.toString();
con.disconnect();
}
catch(Exception ex)
{
StringWriter sw = new StringWriter();
PrintWriter pw = new PrintWriter(sw);
ex.printStackTrace(pw);
result[0]=sw.toString();
}
} }
/
the oracle procedure:
CREATE OR REPLACE procedure west (body IN out VARCHAR2,result in OUT VARCHAR2,resp in OUT VARCHAR2)
AS LANGUAGE JAVA
NAME 'Western.west(java.lang.String[],java.lang.String[],java.lang.String[])';
the error I get:
java.net.SocketException: connect failed, 10022
An invalid argument was
supplied.

at java.net.DualStackPlainSocketImpl.connect0(Native Method)
at
java.net.DualStackPlainSocketImpl.socketConnect(DualStackPlainSocketImpl.java:79
)
at
java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:350)
a
t
java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:2
06)
at
java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:188)
at
java.net.PlainSocketImpl.connect(PlainSocketImpl.java:255)
at
java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at
java.net.Socket.connect(Socket.java:589)
at
sun.security.ssl.SSLSocketImpl.connect(SSLSocketImpl.java:666)
at
sun.security.ssl.BaseSSLSocketImpl.connect(BaseSSLSocketImpl.java:173)
at
sun.net.NetworkClient.doConnect(NetworkClient.java:180)
at
sun.net.www.http.HttpClient.openServer(HttpClient.java:463)
at
sun.net.www.http.HttpClient.openServer(HttpClient.java:558)
at
sun.net.www.protocol.https.HttpsClient.<init>(HttpsClient.java:264)
at
sun.net.www.protocol.https.HttpsClient.New(HttpsClient.java:367)
at
sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.getNewHttpClient(A
bstractDelegateHttpsURLConnection.java:191)
at
sun.net.www.protocol.http.HttpURLConnection.plainConnect0(HttpURLConnection.java
:1156)
at
sun.net.www.protocol.http.HttpURLConnection.plainConnect(HttpURLConnection.java:
1050)
at
sun.net.www.protocol.https.AbstractDelegateHttpsURLConnection.connect(AbstractDe
legateHttpsURLConnection.java:177)
at
sun.net.www.protocol.https.HttpsURLConnectionImpl.connect(HttpsURLConnectionImpl
.java:162)
at Western.west(Western:36)


Chris Saxon

Followup  

June 19, 2020 - 1:47 pm UTC

Well as the error says:

java.net.SocketException: connect failed, 10022
An invalid argument was
supplied.


So there's some problem with the connection arguments (for con.connect()).

My Java skills are weak, so I'm not sure exactly what the issue is.

More to Explore

PL/SQL

Check out more PL/SQL tutorials on our LiveSQL tool.