Home » SQL & PL/SQL » SQL & PL/SQL » plsql routines expose as native web services in 11g (11.1.0.6.0)
plsql routines expose as native web services in 11g [message #311708] Sat, 05 April 2008 19:59 Go to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am having a problem exposing my pl/sql routines as web services. I am hoping one of us here has seen this before and knows what it is. I figure either:

Quote:
1) I am being stupid because I can't execute a web service from sqlplus like this, in which case I need some other easy way to test it.
2) I skipped a privilege thing somewhere.

Here is a summary:

Quote:
1) 11g installed (11.1.0.6.0)
2) web services turned on
3) acl set up so any user can do any thing
4) roles granted so any user can do any thing

Here is what happens:

Quote:
1) I can read outside web pages from sqlplus no problem
2) I can create a function in sqlplus no problem
3) function works correctly in sqlplus no problem
4) I can examine the wsdl from sqlplus no problem
...
5) but I can't call the function as a web service and get results via sqlplus. it looks like it executes and returns a result, just the result is some error message.

** sqlplus version is 10.2.0.3.0 (do not think this matters for this problem)

Here is my code:

select utl_http.request('http://www.oracle.com/') text from dual;
select utl_http.request('http://www.google.com/') text from dual;

create or replace function hello_world return varchar2
as
begin
   return ('Hello Mike');
end;
/
show errors

select hello_world from dual;

grant all on hello_world to public;

select utl_http.request('http://angus:8080/orawsv/KEVIN/HELLO_WORLD?wsdl') text from dual;

select utl_http.request('http://angus:8080/orawsv/KEVIN/HELLO_WORLD') text from dual;


Here is a run of the code with the error at the bottom. For brevity, I have shortened the HTML returned from all calls except the one that reports an error:

SQL> select utl_http.request('http://www.oracle.com/') text from dual;

TEXT
--------------------------------------------------------------------
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head>
<title>Oracle 11g, Siebel, PeopleSoft | Oracle, The World's Largest Enterprise Software Company</title>
<meta name="title" content="Enterprise Applications | Database | Fusion Middleware | Applications Unlimited | Business | 


SQL> select utl_http.request('http://www.google.com/') text from dual;

TEXT
--------------------------------------------------------------------
<html><head><meta http-equiv="content-type" content="text/html; charset=ISO-8859-1"><title>Google</title><style>body,td,a
over,.pgtabselected,.pgtabside{text-align:center;text-decoration:none;color:#00c;display:block;height:27px;float:left;ove
tion:-144px 0}.pgtabselected{width:144px}.pgtabside{width:3px;background-position:-404px 0}.ptr{cursor:pointer;cursor:han


SQL> create or replace function hello_world return varchar2
  2  as
  3  begin
  4     return ('Hello Mike');
  5  end;
  6  /

Function created.

SQL> show errors
No errors.


SQL> select hello_world from dual;

HELLO_WORLD
--------------------------------------------------------------------
Hello Mike


SQL> grant all on hello_world to public;

Grant succeeded.


SQL> select utl_http.request('http://angus:8080/orawsv/KEVIN/HELLO_WORLD?wsdl') text from dual;

TEXT
---------------------------------------------------------------------
<definitions name="HELLO_WORLD"
    targetNamespace="http://xmlns.oracle.com/orawsv/KEVIN/HELLO_WORLD"
    xmlns="http://schemas.xmlsoap.org/wsdl/"
    xmlns:tns="http://xmlns.oracle.com/orawsv/KEVIN/HELLO_WORLD"


SQL> select utl_http.request('http://angus:8080/orawsv/KEVIN/HELLO_WORLD') text from dual;

TEXT
--------------------------------------------------------------------
<?xml version="1.0" ?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
 <soap:Body>
   <soap:Fault>
     <soap:faultcode>
       <soap:Value>soap:Sender</soap:Value>
     </soap:faultcode>
     <soap:faultstring>Error processing input</soap:faultstring>
     <soap:detail>
      <OracleErrors xmlns="http://xmlns.oracle.com/orawsv/faults">
       <OracleError>
        <ErrorNumber>ORA-31011</ErrorNumber>
        <Message><![CDATA[XML parsing failed]]></Message>
      </OracleError>
      </OracleErrors>
     </soap:detail>
   </soap:Fault>
 </soap:Body>
</soap:Envelope>


As you can see, the last two steps above, show that I can see the wsdl definition, but can't actually invoke the function.

I was expecting the last call to return a XML soap response with the "Hello Mike" reply, but instead I get this error response.

For the life of me I have no clue what I missed. I am hoping someone is going to say "dummy, you can't test from sqlplus like that, here is a test script you can use instead".

Barbara, Ross, Michel, (some new friend I have not met yet who is going bail me out on this one), you guys got any ideas?

Kevin

[Updated on: Sat, 05 April 2008 20:01]

Report message to a moderator

Re: plsql routines expose as native web services in 11g [message #311712 is a reply to message #311708] Sat, 05 April 2008 20:44 Go to previous messageGo to next message
BlackSwan
Messages: 25036
Registered: January 2009
Location: SoCal
Senior Member
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:53473595940508

Below is from ASKTOM above:
Hi Tom,

I seem to remember that there was some suggestion that Oracle 10g would be able to publish plsql
webservices straight from the database without needing an application server. Has this plan
disappeared, been put back into 11g or have I just got a faulty memory?





Followup September 17, 2006 - 1pm US/Eastern:

currently UTL_DBWS lets us "consume" web services

but we still need the app server to publish them


RE: PLSQL webservices without Oracle AS September 17, 2006 - 8pm US/Eastern Bookmark | Bottom | TopReviewer: Chris Poole from Sunny Sydney in Spring
Hi Tom,

But what about DBMS_EPG? Does that not allow you to publish a URL using mod_plsql without the
external Apache server or another AS? It's in 10gR2... If it doesn't do that, what does it do?

Ta,

Chris



Followup September 18, 2006 - 1am US/Eastern:

you can use the embedded plsql gateway to run mod_plsql in the database - yes.


but that does not make it a "web service" yet. There is the soap envelope - parsing of inputs and
so on that must be considered.

that gives us the transport, but not the entire implementation.

HTH & YMMV
Re: plsql routines expose as native web services in 11g [message #311716 is a reply to message #311708] Sat, 05 April 2008 22:28 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks anacedent

Yes, I read this six months ago. Not really sure what Tom was getting at towards the end about "not the whole transport" thing. This may in fact be where I am falling down.

However, based on what I am reading, and how far I have gotten so far with 11g, I was expecting procedure/function invocations to return soap. Maybe this is my first mistake. Still, if they do not return soap, then what are they returning and how does one get a look at it?

I read the XDB installing guide and about 50 other papers/webpages to try and figure this out. My impressions are that the embedded plsql gateway inside the 11g database makes it real easy to do this, and everything up to the last step was.

Thanks again, ... someone else got an idea? Maybe someone can show me an example of how to call the function, and describe what it is the call should be returning (soap?,xml?,html?).

Kevin
Re: plsql routines expose as native web services in 11g [message #311718 is a reply to message #311708] Sat, 05 April 2008 23:44 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
GOOD NEWS I THINK.

It seems I am able to execute the webservice from APEX. I followed some instructions of the internet on how to create a webservice reference in APEX, and create a form region based on it.

I was getting an authentication error in APEX when trying to call my function as a webservice. Once I defined the webservice in APEX as requiring basic authentication, it worked. There were three things I would note:

1) the APEX process for defining and using a webservice reference seems a little buggy. It wanted to add an INPUT parameter for my function call even though there is no such parameter for the function specification. You would think it should know this after examining the wsdl for the webservice.

2) there are two levels of authentication when using APEX. When I went to run the APEX app, I had to log into to APEX. Then when I hit the SUBMIT button in the app to run the url call, I had to authenticate with the URL.

3) I am guessing that if my problem with apex was an authentication one, then maybe my problem from sqlplus was also an authentication issue, and yet when I add a username/password to the http call, it still fails.

So, this leads me to my next question:

How does one invoke a webservice for SQLPLUS so that we can have an easy way to test these things? In my sample code from the original post at the top of this thread, I showed how to access a web page from sqlplus, but clearly my understanding of the basics of all this technology are wanting because I guess reading a web page is not the same as consuming a web service.

Someone elighten me please.

Kevin
Re: plsql routines expose as native web services in 11g [message #334014 is a reply to message #311718] Tue, 15 July 2008 03:41 Go to previous messageGo to next message
mec1
Messages: 1
Registered: July 2008
Junior Member
Hi Kevin, I don't know whether I can enlighten you, but...

I was facing the same problem. According to metalink (Note:263498.1) there might be a problem in the dbms_xmlparser. If you access a procedure/function, you may add:

execute immediate('alter session set events ''31156 trace name context forever, level 2''');

For a client you can use Oracle's JDeveloper - creates some simple interfaces to the web-service.

Regards
MEC
Re: plsql routines expose as native web services in 11g [message #334193 is a reply to message #311708] Tue, 15 July 2008 15:08 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
thanks very much. Kevin
Previous Topic: Collection return Null
Next Topic: It's stoped while a table was creating.
Goto Forum:
  


Current Time: Sun Dec 04 14:43:40 CST 2016

Total time taken to generate the page: 0.07043 seconds