invoke a webservice through pl/sql block

How to invoke a webservice through pl/sql block for which we know url,username and password.

And how to see the response?

Give some sample code...

Thanks in advance

I have used the following piece of code:

CREATE OR REPLACE FUNCTION READ_DATA_FROM_WS (url IN VARCHAR2, username IN VARCHAR2, password IN VARCHAR2) RETURN CLOB IS req UTL_HTTP.req; resp UTL_HTTP.resp; DATA VARCHAR2 (2000); data1 CLOB; def_timeout PLS_INTEGER; l_envelope VARCHAR2(32767); BEGIN req := utl_http.begin_request(url, 'POST','HTTP/1.0'); UTL_HTTP.set_authentication (req, username, password); utl_http.set_header(req, 'Content-Type', 'text/xml'); resp := utl_http.get_response(req); IF (resp.status_code = UTL_HTTP.http_ok) THEN UTL_HTTP.set_body_charset (resp, 'UTF-8'); BEGIN LOOP UTL_HTTP.read_text (resp, DATA); data1 := data1 || DATA; END LOOP; EXCEPTION WHEN UTL_HTTP.end_of_body THEN UTL_HTTP.end_response (resp); UTL_HTTP.set_transfer_timeout (def_timeout); WHEN OTHERS THEN NULL; END; UTL_HTTP.set_transfer_timeout (def_timeout); ELSE UTL_HTTP.end_response (resp); DBMS_OUTPUT.put_line ('HTTP response status code: ' || resp.status_code); END IF; RETURN (data1); END read_data_from_ws; /


I have used web services with pl/sql without problems! I'm using this one (+ my own improvements):

Be sure that you define name spaces correctly, and I think you should only use this for retrieving ASCII not binary data...

Here is some sample code. Left some pieces out but it gives you an idea. The function returns the capabilities XML for a WMS webservice.

function getcapabilities(p_url varchar2
,p_version varchar2) return xmltype is
pragma autonomous_transaction;

req utl_http.req;
resp utl_http.resp;
c varchar2(255);
l_clob clob;
dbms_lob.createtemporary(lob_loc => l_clob, cache => true, dur =>;
-- -----------------------------------
-- ----------------------------------- => l_clob, open_mode => dbms_lob.lob_readwrite);

utl_http.set_proxy(proxy => <proxy>, no_proxy_domains => <no_proxy>);

/* request that exceptions are raised for error Status Codes */
utl_http.set_response_error_check(enable => true);

/* allow testing for exceptions like Utl_Http.Http_Server_Error */
utl_http.set_detailed_excp_support(enable => true);

if instr(p_url, '?') > 0
req := utl_http.begin_request(p_url || '&REQUEST=GetCapabilities&SERVICE=WMS&VERSION=' ||
req := utl_http.begin_request(p_url || '?REQUEST=GetCapabilities&SERVICE=WMS&VERSION=' ||
end if;

utl_http.set_header(req, 'User-Agent', 'Mozilla/4.0');
resp := utl_http.get_response(req);

utl_http.read_text(r => resp, data => c);

/* function that adds a string to a clob */
add_to_clob(l_clob, c);

end loop;
when utl_http.end_of_body then
when others then


dbms_lob.close(lob_loc => l_clob);

/* this was for some Oracle bug */
execute immediate 'alter session set events =''31156 trace name context forever, level 2''';
return xmltype.createxml(l_clob);

Even if there is a way to do this it would be a very bad practice!

Also, there are so many problems here. What will this service return? How are you gonna parse the results to something that sql can understand? How are you going to handle errors coming back from the service?

Just return whatever it is you need to return to the application and have the app invoke the web service.

Category:sql Time:2010-10-25 Views:1

Related post

  • Place to store PL/SQL block 2011-09-09

    Is the Below question solution correct for where should the PL/SQL be stored. You want to create a PL/SQL block cof code that calculates discounts on customer orders. - This code will be invoked from several places,but only within the program unit OR

  • org.apache.axis2.AxisFault: Transport error: 401 Error: Unauthorized while invoking the webservice on Tomcat 2011-10-21

    apache.axis2.AxisFault: Transport error: 401 Error: Unauthorized exception while invoking the webservice deployed on Tomcat 6.0.13 please find the stack trace as given below: log4j:WARN No appenders could be found for logger (org.apache.axis2.descrip

  • Is it possible to output a SELECT state from a PL/SQL block? 2008-12-09

    How can I get a PL/SQL block to output the results of a SELECT statement the same way as if I had done a plain SELECT? For example how do a SELECT like: SELECT foo, bar FROM foobar; Hint : BEGIN SELECT foo, bar FROM foobar; END; doesn't work. -------

  • How to catch a unique constraint error in a PL/SQL block? 2009-01-13

    Say I have an Oracle PL/SQL block that inserts a record into a table and need to recover from a unique constraint error, like this: begin insert into some_table ('some', 'values'); exception when ... update some_table set value = 'values' where key =

  • With ADO, how do I call an Oracle PL/SQL block and specify input/output bind variables (parameters?) 2010-03-03

    I am trying to call a PL/SQL block with ADO and VBA, but I can't pass input and/or output bind variables (probably aka parameters). dim cn as ADODB.connection ' ... open connection ... dim plsql as string plsql = "declare" plsql = plsql & " num_i

  • SQLPlus - spooling to multiple files from PL/SQL blocks 2010-04-13

    I have a query that returns a lot of data into a CSV file. So much, in fact, that Excel can't open it - there are too many rows. Is there a way to control spool to spool to a new file everytime 65000 rows have been processed? Ideally, I'd like to hav

  • how to invoke a webservice from one container in another container in glassfish 2010-04-22

    I have webservices deployed on two containers in two separate servers A and B. A webMethod in 'Server A' needs to invoke a webmethod in 'Server B'. I have created a client stub for Sever B. Im trying to make 'Server A' use this client stub and talk t

  • Can I return values to PHP from an anonymous PL/SQL block? 2010-06-01

    I'm using PHP and OCI8 to execute anonymous Oracle PL/SQL blocks of code. Is there any way for me to bind a variable and get its output upon completion of the block, just as I can when I call stored procedures in a similar way? $SQL = "declare someth

  • Order of declaration in an anonymous pl/sql block 2010-06-09

    I have an anonymous pl/sql block with a procedure declared inside of it as well as a cursor. If I declare the procedure before the cursor it fails. Is there a requirement that cursors be declared prior to procedures? What other rules are there for or

  • ABAP: using SQL Server table hints in a Native SQL block 2010-11-04

    I have an ABAP class with various methods for reading from / writing to a remote Microsoft SQL Server 2005 instance. Everything works as expected. Now I've been advised to add a SQL Server table hint (READPAST) to a SELECT query, for safety reasons (

  • Execute anonymous pl/sql block and get resultset in java 2011-02-24

    I would like to execute the anonymous PL/SQL and need to get the resultset object. I got the code which can be done by using cursors inside the PL/SQL block. But the PL/SQL block itself will come from the database as text. So I can't edit that PL/SQL

  • Problem while Invoking a webservice? 2011-03-02

    I am invoking a webservice and got this error..Do anyone know what is the exact problem... System.ServiceModel.Security.MessageSecurityException: An unsecured or incorrectly secured fault was received from the other party. See the inner FaultExceptio

  • PL/SQL Blocks - seeing output simply ? [A Very simple question I'm sure !] 2011-08-30

    I'm sure what I want is very simple but I cannot figure out how. I want : To declare some variables and initialize them to certain values To excecute a number of selects (predicated by the above variable values) and see the results as if i had execut

  • Monitoring long-running PL/SQL block 2011-09-08

    I have a fairly time intensive PL/SQL block that builds fingerprints from molecular structures. I would like to print output to SQL*Plus console to provide feedback on how many structures have been processed. I can do this with dbms_output.put_line H

  • How do I retrieve data from Oracle as a query result using an anonymous PL/SQL block in Python? 2011-10-20

    All the examples of using PL/SQL I can find end up looking like this (this example taken from Wikipedia): FOR RecordIndex IN (SELECT person_code FROM people_table) LOOP DBMS_OUTPUT.PUT_LINE(RecordIndex.person_code); END LOOP; In other words, they all

  • How to sync a SharePoint List (via SP WebServices) with a SQL Table? 2011-10-28

    is there a possibility to sync a SharePoint list via standard SharePoint WebServices with a SQL Table? I would like to use the Microsoft Sync Framework. Some sample code would be very helpful. Thanks --------------Solutions------------- Not much code

  • declare variables in a pl/sql block 2011-12-12

    I am trying to follow this guide for creating pl/sql blocks and I am getting an ORA-00922:missing or invalid option on the SET orderNumberSEQ.... What am I doing wrong? declare orderNumberSEQ number(5); userid varchar(20); begin insert into bs_orders

  • How to write the following pl/sql block without using Cursor? 2011-12-27

    I had written a cursor in a pl/sql block. This block taking lot of time if it has more records. How to write this without a cursor or Is there any other alternative way that will reduce the time? Is there any alternative query to perform insert into

  • JAXWS Client to invoke JAXWS Webservice using X509 Server authentication 2012-01-04

    I created a JAXWS webservice from wsdl using wsdlc ANT task and deployed in Weblogic 10.3.0. And we have Sun one Java Server 7.0 where Verisign Server certificate is installed and configured to forward the webservice requests to weblogic. I created a

Copyright (C), All Rights Reserved.

processed in 0.648 (s). 13 q(s)