Hi,This is venkat i just want to call web service Using PL SQLBut 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 parametersbut 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

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.

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.

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.

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.

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.

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.

Hi,Mr TimGood EveningI 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 AdvanceRegardsvenkat

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.