UTL_HTTP.GET.RESPONSE

[venkat]

Hi,
This is venkat i just want to call web service Using PL SQL
But I Found All the samples in google which is returning only xml of one return type (UTL_HTTP.GET.RESPONSE)
but in my case calling web service which returning blob and two more out parameters
but using UTL_Http.Get.Response how come it is going to catch all out parameters
Could you Please Suggest me the best way to solve the problem

Thanks in Advance
Regards
Venkat

[Tim...]

Hi.

What you have to do depends *entirely* on how the web service is planning to ship this information...

Typically I would expect such a web service to return a single XML documents (either SOAP or REST style) containing a number of return parameters as separate tags. The contents of those tags can be of any type, but remember it will be transported as text if the content is of mixed types.

Once you've received the XML, you will have to parse it to pull out the multiple parameters. That's pretty simple if you have used XML before. You can see examples of that here.

http://www.oracle-base.com/articles/9i/ ... nts-9i.php

Any binary data is still handled in this way, but needs for be converted for the process. Typically, web services convert binary data (images, pdfs, word docs etc) into base64 encoded text. If you were encoding one yourself, you could use this to do it.

http://www.oracle-base.com/dba/script.p ... encode.sql

To convert the encoded text from your webservice back to binary, you would use this.

http://www.oracle-base.com/dba/script.p ... decode.sql

So in all cases, you would read the returned document and pull out the relevant parameter values. In the case of binary data (images), you would then have to convert this data from some form of encoded text back to a binary, to put into a BLOB.

Cheers

Tim...

[venkat]

Thank You Tim given a good example tim

[Tim...]

[venkat]

Hi Tim
Good After Noon
when i am uplading the clob it is giving the error but without clob it is executing fine
/*********************************/
Declare
l_envelope VARCHAR2(32767);
base64Binary Clob;
l_http_request UTL_HTTP.req;
l_http_response UTL_HTTP.resp;
l_response t_response;
BEGIN
l_envelope := '<?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>
<UploadTransaction xmlns="https://www.shafafiya.org/v2/">
<login>'||'string'||'</login>
<pwd>'||'string'||'</pwd>
<fileContent>'||base64Binary||'</fileContent>
<fileName>'||string||'</fileName>
</UploadTransaction>
</soap:Body>
</soap:Envelope>'
generate_envelope(p_request, l_envelope);
show_envelope(l_envelope, 'Request');
l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.1');
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml');
UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope));
UTL_HTTP.set_header(l_http_request, 'SOAPAction', p_action);
UTL_HTTP.write_text(l_http_request, l_envelope);
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response, l_envelope);
UTL_HTTP.end_response(l_http_response);
End;

Thanks In Advance

Warm And Regards

Venkat

[Tim...]

Hi.

What error?

Remember, you are trying to use the CLOB like it were a VARCHAR2. By that I mean you are building one giant string and sending it in one go. If the l_envelope gets bigger than 32k it is going to break. If your content can be bigger than 32k, you need to build up the whole document in a CLOB and push chunks of it out, a piece at a time using write_text.

Cheers

Tim...

[venkat]

Thank You Tim
Regards
venkat

[Tim...]

[venkat]

Hi,
Mr Tim
Good After Noon

This is my program


Declare
File_content_C Clob;
File_content_B Clob;
bl blob;
bf bfile;
dest_offset integer := 1;
src_offset integer := 1;
l_blob BLOB;
l_raw RAW(32767);
l_amt NUMBER := 7700;
l_offset NUMBER := 1;
l_temp VARCHAR2(32767);
l_envelope Clob ;
l_http_response UTL_HTTP.resp;
Begin

dbms_lob.createtemporary (bl, true);
bf := bfilename ('XXXX_SHARE', 'SSS_009_TEST_POS.XML');
dbms_lob.open (bf, dbms_lob.lob_readonly);
dbms_lob.loadblobfromfile (bl, bf, dbms_lob.getlength (bf), dest_offset, src_offset);
dbms_lob.close (bf);

--Select File_name into File_content_B From venkat_blob where FILE_ID = 1;

Select venkat_base64encode(bl) into File_content_C From dual;
/*this converting blob into clob base64binary which is less than 32767*/

l_envelope := '<?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>
<UploadTransaction xmlns="https://www.shafafiya.org/v2/">
<login>'||login_id||'</login>
<pwd>'||PSWD||'</pwd>
<fileContent>'||File_content_C||'</fileContent>
<fileName>'||'Test01'||'</fileName>
</UploadTransaction>
</soap:Body>
</soap:Envelope>';
Dbms_output.put_line(length(l_envelope));
l_http_request := UTL_HTTP.begin_request('www.shafafiya.org', 'POST','HTTP/1.1');
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset=utf-8');
UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope ));
UTL_HTTP.set_header(l_http_request, 'SOAPAction','https://www.shafafiya.org/v2/UploadTransaction');
UTL_HTTP.write_text(l_http_request, l_envelope );
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response, l_envelope);
UTL_HTTP.end_response(l_http_response);
Dbms_output.put_line((l_envelope));

END;

out put is : <head><title>Document Moved</title></head>
<body><h1>Object Moved</h1>This document may be found <a HREF="dictionary/portal/">here</a></body>

but i actually i need to get this one

<?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>
<UploadTransactionResponse xmlns="https://www.shafafiya.org/v2/">
<UploadTransactionResult>int</UploadTransactionResult>
<errorMessage>string</errorMessage>
<errorReport>base64Binary</errorReport>
</UploadTransactionResponse>
</soap:Body>
</soap:Envelope>


i do not know the problem where the problem exists


Thanks in Advance

Regards
Venkat

[Tim...]

Hi.

You are displaying the envelope of the response, not the request. It looks to me like you are wanting to see the envelope of the request, which you've built. If that is the case, put another DBMS_OUTPUT where you display the length of the envelope to see what it looks like before it is sent.

If you look at this variable after it is sent, you are actually looking at the response envelope. This is the XML produced by the web service and sent back to you.

Cheers

Tim...

[venkat]

Thank you
Mr Tim
can you look at this please!!!


l_http_request := UTL_HTTP.begin_request('www.shafafiya.org', 'POST','HTTP/1.1');
UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml;charset=utf-8');
UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope ));
UTL_HTTP.set_header(l_http_request, 'SOAPAction','https://www.shafafiya.org/v2/UploadTransaction');
UTL_HTTP.write_text(l_http_request, l_envelope );
l_http_response := UTL_HTTP.get_response(l_http_request);
UTL_HTTP.read_text(l_http_response, l_envelope);

/*here if comment this "UTL_HTTP.read_text(l_http_response, l_envelope)" Then its not working and giving error like

ORA-06502: PL/SQL: numeric or value error: invalid LOB locator specified: ORA-22275
ORA-06512: at "SYS.DBMS_LOB", line 833
ORA-06512: at line 53


*/

BEGIN
LOOP
UTL_HTTP.read_text(l_http_response, l_text, 32766);
DBMS_LOB.writeappend (l_clob, LENGTH(l_text), l_text);
END LOOP;
EXCEPTION
WHEN UTL_HTTP.end_of_body THEN
UTL_HTTP.end_response(l_http_response);
END;

INSERT INTO xxnmc_ws_resp
VALUes (2, l_clob);
commit;
--create table xxnmc_ws_resp(id number,ret_clob clob)
-- Dbms_output.put_line((l_envelope));


--UTL_HTTP.end_response(l_http_response);

END;

when i saw in the table nothing is there

[Tim...]

Hi.

You've commented out the line that reads the response from the web service, so of course nothing is getting into your table. It is the response from the web service that is providing the data to put into the table.

Issues:

1) You are building the envelope in a CLOB, but the you are writing it out in a single call. This will only write a maximum of 32K, so making the envelope a clob will make no difference, except that it will now raise a different error when you attempt to call write_text. If you are going to use a CLOB, you *must* read the clob in chunks and write it out to the web service in multiple chunks using write_text. This is not the cause of this error, but it will cause you problems later if your envelopes get bigger than 32K.

2) You are using read_text to read the response, which expect the variable provided to be a VARCHAR2. You are using a CLOB, hence the issue.

Cheers

Tim...

[venkat]

Hi,
Mr Tim
Good Evening
I would like to know if we consume web service from data base server is there any security concerns
and is there any way to access client file using pl sql
Thanks in Advance
Regards
venkat

[Tim...]

Hi.

The security risk associated with accessing web services from the database is that your database must be able to connect to the internet. Many companies do not allow direct access to the internet from their databases, even with firewalls.

PL/SQL can only access files on the database server file system, or files over the network, like with HTTP. It can't access file systems on other machines natively.

Cheers

Tim...

[venkat]

Hi,
Mr Tim
Good Morning

can We access the jar file from pl sql where jar files are residing on application server

Thanks in Advance

Regards
Venkat