Home » SQL & PL/SQL » SQL & PL/SQL » How to call Tibco webservice in PL/SQL (10g)
How to call Tibco webservice in PL/SQL [message #389916] Wed, 04 March 2009 02:12 Go to next message
bala_id
Messages: 27
Registered: July 2005
Junior Member
Hi Friends,

Can anybody guide me how to call tibco webservice from PL/SQL?

Thanks in advance.


Re: How to call Tibco webservice in PL/SQL [message #390032 is a reply to message #389916] Wed, 04 March 2009 12:03 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
UTL_TCP or UTL_HTTP
Re: How to call Tibco webservice in PL/SQL [message #390129 is a reply to message #390032] Thu, 05 March 2009 01:39 Go to previous messageGo to next message
bala_id
Messages: 27
Registered: July 2005
Junior Member
Hi,
Thanks for the reply. I tried by using UTL_HTTP. I am getting the following error.

ERROR at line 1:
ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00007: unexpected end-of-file encountered
ORA-06512: at "SYS.XMLTYPE", line 0
ORA-06512: at "DOHA_DB.SOAP_API", line 143
ORA-06512: at "DOHA_DB.PKG_MY_WEBSERVICE", line 29


My function is

CREATE OR REPLACE PACKAGE BODY pkg_my_webservice IS


       vg_funciton_fnc VARCHAR2(256) := 'GetDBStatusRequest';
       vg_ws_address   VARCHAR2(300) := 
'http://10.10.217.134:5568/ProcessDefinitions/Testing/TestServer/GSCP/GSCP-service.serviceagent?wsdl';


     FUNCTION call_myfuntion(
                             --lets assume that it inputs two parameters called string1, string2
                             vp_parameter1 VARCHAR2,
                             vp_parameter2 VARCHAR2) RETURN VARCHAR2 AS
          ol_req  soap_api.t_request;
          ol_resp soap_api.t_response;
     BEGIN
          -- we initilize a new request
          ol_req := soap_api.new_request(vg_funciton_fnc,
                                         'xmlns="' || vg_ws_address || '"');
                  ol_resp := soap_api.invoke(ol_req,
                                     vg_ws_address,
                                     vg_funciton_fnc);
          -- we get back the results
          RETURN soap_api.get_return_value(ol_resp,
                                           'result', -- result tag name
                                           'xmlns:n1="' || --can be change as "xmlns:n1"
                                           vg_ws_address || '"');
          --  RETURN 'Success';                               
     
     END call_myfuntion;

END pkg_my_webservice;


Soap_Api package is downloaded from http://www.oracle-base.com/dba/miscellaneous/soap_api.sql

Please help me to solve the problem.

[Updated on: Thu, 05 March 2009 02:22] by Moderator

Report message to a moderator

Re: How to call Tibco webservice in PL/SQL [message #390522 is a reply to message #390129] Fri, 06 March 2009 23:37 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
our you posting real code or fictitious code?

you offer line numbers in the error message but not in your posted code. Did you expect people to count lines?

Last time I got this error, it was because data was truncated. that is to say, not all of the xml data made it to the caller.

How much data are you passing?
Re: How to call Tibco webservice in PL/SQL [message #391122 is a reply to message #390522] Wed, 11 March 2009 02:33 Go to previous messageGo to next message
bala_id
Messages: 27
Registered: July 2005
Junior Member
Hi Kevin,
Thanks for your reply. Yes, you are right, because of data truncation I have encountered that error. Now I have rectified,
but I have got following error.

ORA-30625: method dispatch on NULL SELF argument is disallowed
ORA-06512: at "SOAP_API_ORG", line 101
ORA-06512: at "SOAP_API_ORG", line 148
ORA-06512: at "GET_Account_STATUS", line 26


Let me explain you about my requirement. I have tibco wsdl service. URL of the webservice is http://10.100.217.134:5574/ProcessDefinitions/Testing/TestServer/APPS/APPS.serviceagent?wsdl. I am able to see the different operations, when I type this URL in IE. Attached is the code of the service. You can view the services by opening the attached code in IE.
In this webservice, I wanted to call "GetSettlementAccountRequest" operation which doesn't require in parameter and the out parameter is varchar2.
When I call the function, I am encountering above error.
Following is the web service call function and soap_api package code.

SQL> CREATE OR REPLACE FUNCTION get_Account_status
  2    RETURN VARCHAR2
  3  AS
  4    l_request   soap_api_org.t_request;
  5    l_response  soap_api_org.t_response;
  6    l_return    VARCHAR2(32767);
  7  
  8    l_url          VARCHAR2(32767);
  9    l_namespace    VARCHAR2(32767);
 10    l_method       VARCHAR2(32767);
 11    l_soap_action  VARCHAR2(32767);
 12    l_result_name  VARCHAR2(32767);
 13  BEGIN
 14  
 15  
 16    l_url         := 'http://10.100.217.134:5574/ProcessDefinitions/Testing/TestServer/APPS/APPS.serviceagent?wsdl';
 17    l_namespace   := 'xmlns="' || l_url || '"';
 18    l_method      := 'GetSettlementAccountRequest';
 19    l_soap_action := l_url || '#GetSettlementAccountRequest';
 20    l_result_name := 'getDBStatusReturn';
 21  
 22    l_request := soap_api_org.new_request(p_method       => l_method,
 23                                            p_namespace    => l_namespace);
 24  
 25  
 26    l_response := soap_api_org.invoke(p_request => l_request,
 27                                  p_url     => l_url,
 28                                  p_action  => l_soap_action);
 29  
 30    l_return := soap_api_org.get_return_value(p_response  => l_response,
 31                                          p_name      => l_result_name,
 32                                          p_namespace => l_namespace);
 33  
 34    RETURN l_return;
 35  END;
 36  



SOAP code
=========

SQL> CREATE OR REPLACE PACKAGE soap_api_org AS
  2  -- --------------------------------------------------------------------------
  3  -- Name         : http://www.oracle-base.com/dba/miscellaneous/soap_api
  4  -- Author       : DR Timothy S Hall
  5  -- Description  : SOAP related functions for consuming web services.
  6  -- Ammedments   :
  7  --   When         Who       What
  8  --   ===========  ========  =================================================
  9  --   04-OCT-2003  Tim Hall  Initial Creation
 10  --   23-FEB-2006  Tim Hall  Parameterized the "soap" envelope tags.
 11  -- --------------------------------------------------------------------------
 12  
 13  TYPE t_request IS RECORD (
 14    method        VARCHAR2(256),
 15    namespace     VARCHAR2(256),
 16    body          VARCHAR2(32767),
 17    envelope_tag  VARCHAR2(30)
 18  );
 19  
 20  TYPE t_response IS RECORD
 21  (
 22    doc           XMLTYPE,
 23    envelope_tag  VARCHAR2(30)
 24  );
 25  
 26  PROCEDURE set_proxy_authentication(p_username  IN  VARCHAR2,
 27                                     p_password  IN  VARCHAR2);
 28  
 29  FUNCTION new_request(p_method        IN  VARCHAR2,
 30                       p_namespace     IN  VARCHAR2,
 31                       p_envelope_tag  IN  VARCHAR2 DEFAULT 'SOAP-ENV')
 32    RETURN t_request;
 33  
 34  
 35  PROCEDURE add_parameter(p_request  IN OUT NOCOPY  t_request,
 36                          p_name     IN             VARCHAR2,
 37                          p_type     IN             VARCHAR2,
 38                          p_value    IN             VARCHAR2);
 39  
 40  FUNCTION invoke(p_request  IN OUT NOCOPY  t_request,
 41                  p_url      IN             VARCHAR2,
 42                  p_action   IN             VARCHAR2)
 43    RETURN t_response;
 44  
 45  FUNCTION get_return_value(p_response   IN OUT NOCOPY  t_response,
 46                            p_name       IN             VARCHAR2,
 47                            p_namespace  IN             VARCHAR2)
 48    RETURN VARCHAR2;
 49  
 50  END soap_api_org;
 51  /



SQL> CREATE OR REPLACE PACKAGE BODY soap_api_org AS
  2  -- --------------------------------------------------------------------------
  3  -- Name         : http://www.oracle-base.com/dba/miscellaneous/soap_api
  4  -- Author       : DR Timothy S Hall
  5  -- Description  : SOAP related functions for consuming web services.
  6  -- Ammedments   :
  7  --   When         Who       What
  8  --   ===========  ========  =================================================
  9  --   04-OCT-2003  Tim Hall  Initial Creation
 10  --   23-FEB-2006  Tim Hall  Parameterized the "soap" envelope tags.
 11  --   08-JUN-2006  Tim Hall  Add proxy authentication functionality.
 12  -- --------------------------------------------------------------------------
 13  
 14  g_proxy_username  VARCHAR2(50) := NULL;
 15  g_proxy_password  VARCHAR2(50) := NULL;
 16  
 17  
 18  -- ---------------------------------------------------------------------
 19  PROCEDURE set_proxy_authentication(p_username  IN  VARCHAR2,
 20                                     p_password  IN  VARCHAR2) AS
 21  -- ---------------------------------------------------------------------
 22  BEGIN
 23    g_proxy_username := p_username;
 24    g_proxy_password := p_password;
 25  END;
 26  -- ---------------------------------------------------------------------
 27  
 28  
 29  
 30  -- ---------------------------------------------------------------------
 31  FUNCTION new_request(p_method        IN  VARCHAR2,
 32                       p_namespace     IN  VARCHAR2,
 33                       p_envelope_tag  IN  VARCHAR2 DEFAULT 'SOAP-ENV')
 34    RETURN t_request AS
 35  -- ---------------------------------------------------------------------
 36    l_request  t_request;
 37  BEGIN
 38    l_request.method       := p_method;
 39    l_request.namespace    := p_namespace;
 40    l_request.envelope_tag := p_envelope_tag;
 41    RETURN l_request;
 42  END;
 43  -- ---------------------------------------------------------------------
 44  
 45  
 46  
 47  -- ---------------------------------------------------------------------
 48  PROCEDURE add_parameter(p_request    IN OUT NOCOPY  t_request,
 49                          p_name   IN             VARCHAR2,
 50                          p_type   IN             VARCHAR2,
 51                          p_value  IN             VARCHAR2) AS
 52  -- ---------------------------------------------------------------------
 53  BEGIN
 54    p_request.body := p_request.body||'<'||p_name||' xsi:type="'||p_type||'">'||p_value||'</'||p_name||'>';
 55  END;
 56  -- ---------------------------------------------------------------------
 57  
 58  
 59  
 60  -- ---------------------------------------------------------------------
 61  PROCEDURE generate_envelope(p_request  IN OUT NOCOPY  t_request,
 62  		                        p_env      IN OUT NOCOPY  VARCHAR2) AS
 63  -- ---------------------------------------------------------------------
 64  BEGIN
 65    p_env := '<'||p_request.envelope_tag||':Envelope xmlns:'||p_request.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/" ' ||
 66                 'xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance" xmlns:xsd="http://www.w3.org/1999/XMLSchema">' ||
 67               '<'||p_request.envelope_tag||':Body>' ||
 68                 '<'||p_request.method||' '||p_request.namespace||' '||p_request.envelope_tag||':encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">' ||
 69                     p_request.body ||
 70                 '</'||p_request.method||'>' ||
 71               '</'||p_request.envelope_tag||':Body>' ||
 72             '</'||p_request.envelope_tag||':Envelope>';
 73  END;
 74  -- ---------------------------------------------------------------------
 75  
 76  
 77  
 78  -- ---------------------------------------------------------------------
 79  PROCEDURE show_envelope(p_env  IN  VARCHAR2) AS
 80  -- ---------------------------------------------------------------------
 81    i      PLS_INTEGER;
 82    l_len  PLS_INTEGER;
 83  BEGIN
 84    i := 1; l_len := LENGTH(p_env);
 85    WHILE (i <= l_len) LOOP
 86      DBMS_OUTPUT.put_line(SUBSTR(p_env, i, 60));
 87      i := i + 60;
 88    END LOOP;
 89  END;
 90  -- ---------------------------------------------------------------------
 91  
 92  
 93  
 94  -- ---------------------------------------------------------------------
 95  PROCEDURE check_fault(p_response IN OUT NOCOPY  t_response) AS
 96  -- ---------------------------------------------------------------------
 97    l_fault_node    XMLTYPE;
 98    l_fault_code    VARCHAR2(256);
 99    l_fault_string  VARCHAR2(32767);
100  BEGIN
101    l_fault_node := p_response.doc.extract('/'||p_response.envelope_tag||':Fault',
102                                           'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/');
103    IF (l_fault_node IS NOT NULL) THEN
104      l_fault_code   := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultcode/child::text()',
105                                             'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
106      l_fault_string := l_fault_node.extract('/'||p_response.envelope_tag||':Fault/faultstring/child::text()',
107                                             'xmlns:'||p_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
108      RAISE_APPLICATION_ERROR(-20000, l_fault_code || ' - ' || l_fault_string);
109    END IF;
110  END;
111  -- ---------------------------------------------------------------------
112  
113  
114  
115  -- ---------------------------------------------------------------------
116  FUNCTION invoke(p_request IN OUT NOCOPY  t_request,
117                  p_url     IN             VARCHAR2,
118                  p_action  IN             VARCHAR2)
119    RETURN t_response AS
120  -- ---------------------------------------------------------------------
121    l_envelope       VARCHAR2(32767);
122    l_http_request   UTL_HTTP.req;
123    l_http_response  UTL_HTTP.resp;
124    l_response       t_response;
125  BEGIN
126    generate_envelope(p_request, l_envelope);
127    show_envelope(l_envelope);
128    l_http_request := UTL_HTTP.begin_request(p_url, 'POST','HTTP/1.0');
129    IF g_proxy_username IS NOT NULL THEN
130      UTL_HTTP.set_authentication(r         => l_http_request,
131                                  username  => g_proxy_username,
132                                  password  => g_proxy_password,
133                                  scheme    => 'Basic',
134                                  for_proxy => TRUE);
135    END IF;
136    UTL_HTTP.set_header(l_http_request, 'Content-Type', 'text/xml');
137    UTL_HTTP.set_header(l_http_request, 'Content-Length', LENGTH(l_envelope));
138    UTL_HTTP.set_header(l_http_request, 'SOAPAction', p_action);
139    UTL_HTTP.write_text(l_http_request, l_envelope);
140    l_http_response := UTL_HTTP.get_response(l_http_request);
141    UTL_HTTP.read_text(l_http_response, l_envelope);
142    UTL_HTTP.end_response(l_http_response);
143    l_response.doc := XMLTYPE.createxml(l_envelope);
144    l_response.envelope_tag := p_request.envelope_tag;
145    [COLOR=red][B]l_response.doc := l_response.doc.extract('/'||l_response.envelope_tag||':Envelope/'||l_response.envelope_tag||':Body/child::node()',
146                                             'xmlns:'||l_response.envelope_tag||'="http://schemas.xmlsoap.org/soap/envelope/"');[/B][/COLOR]
147    -- show_envelope(l_response.doc.getstringval());
148    check_fault(l_response);
149    RETURN l_response;
150  END;
151  -- ---------------------------------------------------------------------
152  
153  
154  
155  -- ---------------------------------------------------------------------
156  FUNCTION get_return_value(p_response   IN OUT NOCOPY  t_response,
157                            p_name       IN             VARCHAR2,
158                            p_namespace  IN             VARCHAR2)
159    RETURN VARCHAR2 AS
160  -- ---------------------------------------------------------------------
161  BEGIN
162    RETURN p_response.doc.extract('//'||p_name||'/child::text()',p_namespace).getstringval();
163  END;
164  -- ---------------------------------------------------------------------
165  
166  END soap_api_org ;
167 /


I found the problem that I have marked as red. It returns NULL when it extracts the XML data in line 145. I am new to XML, so I couldn't find the way to solve the problem. Please help me to solve the problem.

Thanks in advance.

With Regards,
Bala
Re: How to call Tibco webservice in PL/SQL [message #391236 is a reply to message #389916] Wed, 11 March 2009 09:24 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I am not certain what to do either. I am no expert with web services. I have written a few to get what I need.

I might suggest putting the offending call into a plsql block, and trapping the error. The you can generate a dummy return packet when that happens. Ugly work, you'd think Oracle would have done this for us.

Other than that I have no idea how to get around it.

Maybe someone else can give us guidance.

Kevin
Previous Topic: Comma sepred string
Next Topic: Error inserting special characters to Oracle (merged)
Goto Forum:
  


Current Time: Thu Dec 08 21:51:05 CST 2016

Total time taken to generate the page: 0.07553 seconds