This discussion is archived
9 Replies Latest reply: Jun 17, 2009 10:51 AM by 707349 RSS

SR: Calling Web Services from PL/SQL gives http 500 errors

459769 Newbie
Currently Being Moderated
Hi, people from Oracle Support sent me to this forum.

I'm trying to call the Calendar Web Services from PL/SQL using the utl_http package, but I'm getting http-500 errors.

I'm using the example from http://www.oracle.com/technology/tech/webservices/htdocs/samples/dbwebservice/DBWebServices_PLSQL.html
Here's the code

procedure showHtml
as
req soap_rpc.request;
resp soap_rpc.response;
BEGIN
req := soap_rpc.new_request('cwsl:NoOp',
'xmlns:cwsl="http://www.oracle.com/WebServices/Calendaring/1.0/"');
resp := soap_rpc.invoke(req,
'http://nllx004.nl.oracle.com/ocws-bin/ocas.fcgi',
'""');
dbms_output.put_line(resp.doc.getStringVal());
END;

Can anyone help?
  • 1. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    459769 Newbie
    Currently Being Moderated
    Here is the soap_rpc package:

    CREATE OR REPLACE PACKAGE soap_rpc AS

    /* A type to represent a SOAP RPC request */
    TYPE request IS RECORD (
    method VARCHAR2(256),
    namespace VARCHAR2(256),
    body VARCHAR2(32767));

    /* A type to represent a SOAP RPC response */
    TYPE response IS RECORD (
    doc xmltype);

    /*
    * Create a new SOAP RPC request.
    */
    FUNCTION new_request(method IN VARCHAR2,
    namespace IN VARCHAR2)
    RETURN request;

    /*
    * Add a simple parameter to the SOAP RPC request.
    */
    PROCEDURE add_parameter(req IN OUT NOCOPY request,
    name IN VARCHAR2,
    type IN VARCHAR2,
    value IN VARCHAR2);

    /*
    * Make the SOAP RPC call.
    */
    FUNCTION invoke(req IN OUT NOCOPY request,
    url IN VARCHAR2,
    action IN VARCHAR2) RETURN response;

    /*
    * Retrieve the sipmle return value of the SOAP RPC call.
    */
    FUNCTION get_return_value(resp IN OUT NOCOPY response,
    name IN VARCHAR2,
    namespace IN VARCHAR2) RETURN VARCHAR2;

    END;
    /
    CREATE OR REPLACE PACKAGE BODY soap_rpc AS

    FUNCTION new_request(method IN VARCHAR2,
    namespace IN VARCHAR2)
    RETURN request AS
    req request;
    BEGIN
    req.method := method;
    req.namespace := namespace;
    RETURN req;
    END;

    PROCEDURE add_parameter(req IN OUT NOCOPY request,
    name IN VARCHAR2,
    type IN VARCHAR2,
    value IN VARCHAR2) AS
    BEGIN
    req.body := req.body ||
    '<'||name||' xsi:type="'||type||'">'||value||'</'||name||'>';
    END;

    PROCEDURE generate_envelope(req IN OUT NOCOPY request,
                   env IN OUT NOCOPY VARCHAR2) AS
    BEGIN

    env := '<SOAP-ENV:Envelope
    xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"
    xmlns:xsi="http://www.w3.org/1999/XMLSchema-instance"
    xmlns:xsd="http://www.w3.org/1999/XMLSchema">
    <SOAP-ENV:Header></SOAP-ENV:Header>
    <SOAP-ENV:Body><'||req.method||' '||req.namespace||
    ' SOAP-ENV:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">'||
    req.body||'</'||req.method||'></SOAP-ENV:Body></SOAP-ENV:Envelope>';
    END;

    PROCEDURE show_envelope(env IN VARCHAR2) AS
    i pls_integer;
    len pls_integer;
    BEGIN
    i := 1; len := length(env);
    WHILE (i <= len) LOOP
    dbms_output.put_line(substr(env, i, 255));
    i := i + 255;
    END LOOP;
    END;

    PROCEDURE check_fault(resp IN OUT NOCOPY response) AS
    fault_node xmltype;
    fault_code VARCHAR2(256);
    fault_string VARCHAR2(32767);
    BEGIN
    fault_node := resp.doc.extract('/soap:Fault',
    'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/');
    IF (fault_node IS NOT NULL) THEN
    fault_code := fault_node.extract('/soap:Fault/faultcode/child::text()',
         'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
    fault_string := fault_node.extract('/soap:Fault/faultstring/child::text()',
         'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/').getstringval();
    raise_application_error(-20000, fault_code || ' - ' || fault_string);
    END IF;
    END;

    FUNCTION invoke(req IN OUT NOCOPY request,
    url IN VARCHAR2,
    action IN VARCHAR2) RETURN response AS
    env VARCHAR2(32767);
    http_req utl_http.req;
    http_resp utl_http.resp;
    resp response;
    BEGIN
    generate_envelope(req, env);
    show_envelope(env);
    http_req := utl_http.begin_request(url, 'POST','HTTP/1.0');
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
    utl_http.set_header(http_req, 'Content-Length', length(env));
    utl_http.set_header(http_req, 'SOAPAction', action);
    utl_http.write_text(http_req, env);
    http_resp := utl_http.get_response(http_req);
    utl_http.read_text(http_resp, env);
    utl_http.end_response(http_resp);
    -- Debug
    dbms_output.put_line('Before show env 2');
    dbms_output.put_line(' ');
    show_envelope(env);
    resp.doc := xmltype.createxml(env);
    resp.doc := resp.doc.extract('/soap:Envelope/soap:Body/child::node()',
    'xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/"');
    show_envelope(resp.doc.getstringval());
    check_fault(resp);
    RETURN resp;
    END;

    FUNCTION get_return_value(resp IN OUT NOCOPY response,
    name IN VARCHAR2,
    namespace IN VARCHAR2) RETURN VARCHAR2 AS
    BEGIN
    RETURN resp.doc.extract('//'||name||'/child::text()',
    namespace).getstringval();
    END;

    END;
    /
  • 2. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    ward.flores Newbie
    Currently Being Moderated
    Hi,

    If you're getting an http-500 error, you are probably failing to establish a request to your webservices application. Unfortunately, if the problem is strictly related to how to place a request from PL/SQL, I won't be able to help you because I'm not familiar with PL/SQL.

    However, if you can send me the generated SOAP messages, I may be able to narrow a bit the issue. For example, this would give us the information to confirm that the SOAP messages are valid.

    Finally, I've performed a sample NoOP request using the calendarlet.jar to your web services application and it was successful.

    TARGET URL = http://nllx004.nl.oracle.com:80/ocws-bin/ocas.fcgi

    SOAP message Sent
    ----------------------------------
    <?xml version='1.0' encoding='UTF-8'?>
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SOAP-ENV:Body>
    <cwsl:NoOp xmlns:cwsl="http://www.oracle.com/WebServices/Calendaring/1.0/"/>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

    SOAP message Received
    ---------------------------------------
    <?xml version="1.0" encoding="utf-8" ?>
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
    <soap:Header>
    <auth:BasicChallenge xmlns:auth="http://soap-authentication.org/2002/01/" soap:mustUnderstand="1">
    <Realm>Oracle Calendar Web Services</Realm>
    </auth:BasicChallenge>
    </soap:Header>
    <soap:Body>
    <NoOpReply>
    </NoOpReply>
    </soap:Body>
    </soap:Envelope>

    Regards.
  • 3. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    459769 Newbie
    Currently Being Moderated
    I only get this error, without SOAP error codes:

    request:

    <?xml version='1.0' encoding='UTF-8'?>
    <SOAP-ENV:Envelope xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema">
    <SOAP-ENV:Body>
    <cwsl:NoOp xmlns:cwsl=
    "http://www.oracle.com/WebServices/Calendaring/1.0/"/>
    </SOAP-ENV:Body>
    </SOAP-ENV:Envelope>

    reply:

    <!DOCTYPE HTML PUBLIC "-//IETF//DTD HTML 2.0//EN">
    <HTML><HEAD>
    <TITLE>500 Internal Server Error</TITLE>
    </HEAD><BODY>
    <H1>Internal Server Error</H1>
    The server encountered an internal error or
    misconfiguration and was unable to complete
    your request.<P>

    Please contact the server administrator,
    you@your.address and inform them of the time the error occurred,
    and anything you might have done that may have
    caused the error.<P>
    More information about this error may be available
    in the server error log.<P>
    <
    HR>
    <ADDRESS>Oracle-Application-Server-10g/10.1.2.0.2 Oracle-HTTP-Server Server at nllx004.nl.oracle.com Port 80</ADDRESS>
    </BODY></HTML>
  • 4. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    459769 Newbie
    Currently Being Moderated
    I copied your soap envelope and I still get the same http 500 error.

    It looks like the problem is in the http request headers, what should they be?
  • 5. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    user495727 Newbie
    Currently Being Moderated
    Try this form (works for me):

    set pagesize 0
    set long 100000
    declare
    peticion VARCHAR2(32767);
    env VARCHAR2(32767);
    http_req utl_http.req;
    http_resp utl_http.resp;
    begin
    -- soap:encodingStyle="http://schemas.xmlsoap.org/soap/encoding/">
    -- <auth:BasicChallenge
    -- xmlns:auth="http://www.soap-authentication.org/2002/01/">
    -- <Realm>Oracle Calendar Web Services</Realm>
    -- </auth:BasicChallenge>
    peticion:='
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Header>
    <auth:BasicAuth xmlns:auth="http://www.soap-authentication.org/2002/01/">
    <Name>sysadmin</Name>
    <Password>sysadmin1</Password>
    </auth:BasicAuth>
    </soap:Header>
    <soap:Body>
    <cwsl:NoOp xmlns:cwsl="http://www.oracle.com/WebServices/Calendaring/1.0/"/>
    </soap:Body>
    </soap:Envelope>';
    delete from <TABLE>;
    http_req := utl_http.begin_request('http://<HOST>:7778/ocws-bin/ocas.fcgi', 'POST','HTTP/1.0');
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
    utl_http.set_header(http_req, 'Content-Length', length(peticion));
    utl_http.set_header(http_req, 'SOAPAction', '"http://www.oracle.com/WebServices/Calendaring/1.0/NoOp"');
    utl_http.write_text(http_req, peticion);
    dbms_output.put_line (length(peticion));
    http_resp := utl_http.get_response(http_req);
    utl_http.read_text(http_resp, env);
    utl_http.end_response(http_resp);
    insert into <TABLE> (a) values (env);
    -- dbms_output.put_line(env);
    end;
    /
    select * from <TABLE>;

    Change TABLE by a TABLE you create (it is used to output the result)

    CREATE <TABLE> (A LONG);
    User in this sample is sysadmin/sysadmin1, put a user that exists in your case with the correct password.
    And set HOST to your host and the Apache port
  • 6. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    459769 Newbie
    Currently Being Moderated
    Thanks!

    utl_http.set_header(http_req, 'SOAPAction', '"http://www.oracle.com/WebServices/Calendaring/1.0/NoOp"');

    After changing the SOAPAction to exactly that line, including quotes, it works.
  • 7. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    630526 Newbie
    Currently Being Moderated
    I am new to web services and am having trouble getting the utl_http.set_header call to work using pl/sql. listed below is the code.
    CREATE OR REPLACE PACKAGE BODY adm_iModules_test
    AS
    /******************************************************************************
    NAME: Admissions iModules testing program
    PURPOSE:

    Ver Date Author Description
    REVISIONS:
    --------- ---------- --------------- ------------------------------------
    1.0 01/04/08 Julie Michael 1. Created this package body.
    ******************************************************************************/
    PROCEDURE adm_iModules_test_output
    IS
    --cursor to select data to populate output file
    CURSOR c_iModules_main
    IS
    SELECT DISTINCT spriden_id sprid_id,
                                  spriden_pidm sprid_pidm,
                                  spriden_last_name sprid_last,
    spriden_first_name sprid_first,
                                  spriden_MI sprid_mid,
    spriden_create_date sprid_create_date,
    spriden_activity_date sprid_activity_date,
                                  spbpers_pidm pers_pidm,
    spbpers_name_prefix prefix,
    spbpers_name_suffix suffix,
                                  spbpers_dead_ind deceased_ind,
                                  spbpers_dead_date deceased_date,
                                  spbpers_birth_date, --added birth date
                                  spbpers_sex, --added gender
                                  goremal_pidm emal_pidm,
    goremal_email_address email_address,
                                  saradap_pidm app_pidm,
                                  zzrimod_member_id imod_id,
                                  gobtpac_external_user user_id
    FROM spriden,
    spbpers,
    goremal,
                                  saradap,
                                  zzrimod,
                                  gobtpac
    WHERE spriden_pidm = spbpers_pidm
    AND spriden_change_ind IS NULL
    AND spriden_pidm = goremal_pidm
                             AND goremal_emal_code = 'PERS'
                             AND goremal_preferred_ind = 'Y'
                             AND spriden_pidm = saradap_pidm
                             AND spriden_pidm = 120116
                             AND spriden_pidm = gobtpac_pidm
                             AND spriden_pidm = zzrimod_pidm(+)
                             AND goremal_preferred_ind = 'Y'
                             AND saradap_term_code_entry in ('200810','200820','200830');
                             
         v_record      VARCHAR2 (32000) := null;
         v_record_counter               NUMBER                    := 0;
    f_iModules_output_test UTL_FILE.file_type;
    f_iModules_filename_test VARCHAR2 (50) := 'Adm_iModules_Test.TXT';
    f_iModules_dir_test VARCHAR2 (100) := 'TMP';
         v_separator                    VARCHAR2 (50)          := ',';
         v_id                              VARCHAR2 (90)          := '';
         v_fname                         VARCHAR2 (40)          := '';
         v_lname                         VARCHAR2 (40)          := '';
         v_user                         VARCHAR2 (90)          := '';
         v_login                         VARCHAR2 (100)          := '';
         v_password                    VARCHAR2 (100)          := '';
    -- v_link                         VARCHAR2 (32767)     := 'http://imodules.com/Web%20Services/';
         --the below item 'http://admin.imodules.com - was https://admin.imodules.com
         v_link                         VARCHAR2 (32767)      := 'http://admin.imodules.com/ws_10/MemberQuery.asmx?WSDL';
         v_non_existing_ids          VARCHAR2 (100)          := '"false"';
         v_style                         VARCHAR2 (5)           := '"1"';
         v_imod_member_id               VARCHAR2 (100)      := '';
         v_spriden_first_name           VARCHAR2 (60)          := '';
         req                Utl_Http.req;           --for posting
         resp                Utl_Http.resp;          --for posting
         v_msg                VARCHAR2(80);          --for posting
         H_Name                VARCHAR2(255);          --for posting
         H_Value                VARCHAR2(1023);     --for posting
         v_data_xml                    VARCHAR2(10000);


         BEGIN
                   DBMS_OUTPUT.ENABLE(1000000);
              f_iModules_output_test := UTL_FILE.fopen (f_iModules_dir_test, f_iModules_filename_test, 'w',32767);
                   v_record := '';
                   --needed for posting
                   
    --                Utl_http.set_proxy(proxy => 'xxx.yyy.com',
    --      no_proxy_domains => 'xxx.com');

                   req := Utl_Http.begin_request(url => v_link,
    method => 'POST');

         
    --                          FOR v_iModules2 IN c_iModules_main
    --                          LOOP     
    --                                    v_record_counter := v_record_counter + 1;
    --                          END LOOP;
         
                             -- v_record := 'DUQ'||','||v_record_counter||','||to_char(SYSDATE,'mm/dd/yyyy');
                             UTL_FILE.put_line (f_iModules_output_test, v_record, false);     
                                       v_record := null;
                                  FOR v_iModules IN c_iModules_main
                                  LOOP
                                            v_id := v_imodules.sprid_id;
                                            v_fname := v_imodules.sprid_first||'test';
                                            v_lname := v_imodules.sprid_last;     
                                            v_user := '"'||v_imodules.email_address||'"';
                                            v_imodules.imod_id := '29';
                                            v_imod_member_id := '"'||v_imodules.imod_id||'"';
                                            v_spriden_first_name := '"SPRIDEN_FIRST_NAME"';
                                            
                                            v_login := '"xxxxxxxx-xxxx-xxxx-xxxx-xxxxxxxxxx"';
                                            v_password := '"xxxxxxxxxxxxxxxxxxxxxxxx"';
                                            
                                            v_record :='<?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>'||'<Update xmlns:m='||v_link||'>'||'<AUTHENTICATION login='||v_login||' '||
                                            'password='||v_password||' '||'addNonExistingIDs='||v_non_existing_ids||' '||'style='||
                                            v_style||'/>'||'<MemberInformation ZZRIMOD_MEMBER_ID='||v_imod_member_id||'>'||
                                            '<SPRIDEN_FIRST_NAME>'||v_fname||'</SPRIDEN_FIRST_NAME>'||'</MemberInformation>'||'</Update>'||
                                            '</soap:Body></soap:Envelope>';
                                                      
    --                                         v_record := v_record := v_record||'<Update xmlns:m='||v_link||'>'||'<AUTHENTICATION login='||v_user||'>'||' '||
    --                                                   'password='||v_password||' '||'addNonExistingIDs='||v_non_existing_ids||' '||'style='||
    --                                                   v_style||'/>'||'<MemberInformation ZZRIMOD_MEMBER_ID='||v_imod_member_id||' '||
    --                                                   'column='||'"SPRIDEN_FIRST_NAME"'||' '||'newValue='||v_fname||'/>'||'</Update>';
                                            
                                            dbms_output.put_line('code: '||v_record);
                                                      
                                            --v_record := v_record||v_separator||v_id||v_separator||v_name||v_separator||v_email;
                                       
                                       --Http posting calls
                                            Utl_Http.set_header(r => req,
                                            name => 'Content-Type',
                                            value => 'text/xml');

                                       Utl_Http.set_header(r => req,
                                            name => 'Content-Length',
                                            value => to_char(length(v_record)) );
                                                                     
                                            --JAM 03/25/08 - added to accomodate the missing soap action error                           
                                            Utl_Http.set_header(r => req,
                                            name => 'SOAPAction',
                                            value => 'http://admin.imodules.com/ws_10/MemberQuery.asmx?WSDL');

                                            Utl_Http.set_header(r => req,
                                            name => 'MessageType',
                                            value => 'CALL');
         
    --                                    Utl_Http.set_authentication(r => req,
    --                                         username => 'zzz',
    --                                                   password => 'ppppp',
    --                               scheme => 'Basic',
    --                               for_proxy => FALSE);
                                            
                                       Utl_Http.write_text(r => req,
                                            data => v_record);     
         
                                            resp := Utl_Http.get_response(r => req,
                             return_info_response => TRUE);
                                                                     
                                            DBMS_OUTPUT.put_line('Status Code: ' || resp.status_code);
                                            DBMS_OUTPUT.put_line('Reason Phrase: ' || resp.reason_phrase);
                                            dbms_output.put_line('testing');
         
                                            FOR i IN 1 .. Utl_Http.get_header_count(r => resp)
                                            LOOP
                                            Utl_Http.get_header(r => resp,
                   n => i,
                   name => H_Name,
                   value => H_Value);

                                            --DBMS_OUTPUT.put_line( ... );
                                            END LOOP;
         
                                            BEGIN
                                            LOOP
                                            Utl_Http.read_text(r => resp, DATA => v_msg);
                                            DBMS_OUTPUT.put_line(v_msg);
                                            END LOOP;
         
                                                 EXCEPTION
                                                 WHEN Utl_Http.end_of_body THEN
                                                 NULL;
                                                 END;          
              
                                                 Utl_Http.end_response(r => resp);      

    --                                              EXCEPTION
    --                                              WHEN Utl_Http.request_failed THEN
    --                                                   DBMS_OUTPUT.put_line('Request failed: '||Utl_Http.Get_Detailed_Sqlerrm);
    --
    --                                                   WHEN Utl_Http.http_server_error THEN
    --                                                   DBMS_OUTPUT.put_line('Http_Server_Error: '||Utl_Http.Get_Detailed_Sqlerrm);
    --                     
    --                                                   WHEN Utl_Http.http_client_error THEN
    --                                                   DBMS_OUTPUT.put_line('Client Error: '||Utl_Http.Get_Detailed_Sqlerrm);
    --                     
    --                                                   WHEN others THEN     
    --                                                   DBMS_OUTPUT.put_line('Others2: '||Utl_Http.Get_Detailed_Sqlerrm);     
    --                                                   Utl_Http.end_response(r => resp);     
    --                                              END;
                        
                                            --End of Htp posting calls
                                       
                                            
                                            
                                            --UTL_FILE.put_line (f_iModules_output_test, v_record, false);
                                            --htp.p('<post>'||v_record||'</post>');
         
                                            
                                            END LOOP;
                                            
                                                 EXCEPTION
                                                 WHEN Utl_Http.request_failed THEN
                                                      DBMS_OUTPUT.put_line('Request failed: '||Utl_Http.Get_Detailed_Sqlerrm);

                                                      WHEN Utl_Http.http_server_error THEN
                                                      DBMS_OUTPUT.put_line('Http_Server_Error: '||Utl_Http.Get_Detailed_Sqlerrm);
                        
                                                      WHEN Utl_Http.http_client_error THEN
                                                      DBMS_OUTPUT.put_line('Client Error: '||Utl_Http.Get_Detailed_Sqlerrm);
                        
                                                      WHEN others THEN     
                                                      DBMS_OUTPUT.put_line('Others2: '||Utl_Http.Get_Detailed_Sqlerrm);     
                                                      Utl_Http.end_response(r => resp);     
                                                 END;

                        -- close output file
                   --UTL_FILE.fclose (f_iModules_output_test);
                             --COMMIT;
                             /*EXCEPTION
                        WHEN OTHERS
                        THEN
                   ROLLBACK;
                   DBMS_OUTPUT.put_line ('Other Exception:' || SQLERRM);
                             -- DBMS_OUTPUT.put_line ('Other Exception:' || Utl_Http.Get_Detailed_Sqlerrm);
                             --COMMIT; */
                             -- END adm_iModules_test_output;
                             END adm_iModules_test;
    /

    Message was edited by:
    user627523
  • 8. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    637653 Newbie
    Currently Being Moderated
    Based on the program mentioned, I wrote a program to call the url.
    Following is the program
    set pagesize 0
    set long 100000
    declare
    peticion VARCHAR2(32767);
    env VARCHAR2(32767);
    http_req utl_http.req;
    http_resp utl_http.resp;
    begin
    dbms_output.put_line ('a');
    peticion:='
    <soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
    <soap:Header>
    <auth:BasicAuth xmlns:auth="http://www.soap-authentication.org/2002/01/">
    <Name>Oracle</Name>
    <Password>Oracle</Password>
    </auth:BasicAuth>
    </soap:Header>
    <soap:Body>
    <cwsl:NoOp xmlns:cwsl="http://10.80.7.75:5977/invoke/Default:testOracleCall"/>
    </soap:Body>
    </soap:Envelope>';
    --delete from <TABLE>;
    dbms_output.put_line ('b');
    http_req := utl_http.begin_request('http://10.80.7.75:5977/invoke/Default:testOracleCall', 'POST','HTTP/1.0');
    dbms_output.put_line ('c');
    utl_http.set_header(http_req, 'Content-Type', 'text/xml');
    dbms_output.put_line ('d');
    utl_http.set_header(http_req, 'Content-Length', 32000);
    dbms_output.put_line ('e');
    --utl_http.set_header(http_req, 'SOAPAction', '"http://www.oracle.com/WebServices/Calendaring/1.0/NoOp"');
    utl_http.set_header(http_req, 'SOAPAction', '"http://10.80.7.75:5977/invoke/Default:testOracleCall"');
    dbms_output.put_line ('f');
    utl_http.write_text(http_req, peticion);
    dbms_output.put_line ('abc '||length(peticion));
    http_resp := utl_http.get_response(http_req);
    dbms_output.put_line ('g');
    utl_http.read_text(http_resp, env);
    dbms_output.put_line ('h');
    utl_http.end_response(http_resp);
    dbms_output.put_line ('i');
    --insert into test_http (a) values (env);
    -- dbms_output.put_line(env);
    end;
    /

    I am getting error http request failed, timeout on the execution of http_resp := utl_http.get_response(http_req);

    Can you pls help.
  • 9. Re: SR: Calling Web Services from PL/SQL gives http 500 errors
    707349 Newbie
    Currently Being Moderated
    can somebody help me out in this please

    declare

    l_service SYS.UTL_DBWS.service;

    l_call SYS.UTL_DBWS.call;

    l_result ANYDATA;

    l_wsdl_url VARCHAR2(32767);

    l_namespace VARCHAR2(32767);

    l_service_qname SYS.UTL_DBWS.qname;

    l_port_qname SYS.UTL_DBWS.qname;

    l_operation_qname SYS.UTL_DBWS.qname;

    l_input_params SYS.UTL_DBWS.anydata_list;

    l_return_string VARCHAR2 (100);

    XML XMLTYPE;

    string_type_qname sys.utl_dbws.QNAME;

    retx sys.xmltype;

    xml_string sys.xmltype;

    l_str long;

    BEGIN

    v_err_loc :=1;

    -- Set the Target NameSpace

    l_wsdl_url := url watever rquired ;

    l_namespace := namespace wat ever required';

    -- Set the Service

    l_service_qname := SYS.UTL_DBWS.to_qname(l_namespace, 'DataviewService');

    -- Set the Port

    l_port_qname := SYS.UTL_DBWS.to_qname(l_namespace, 'DataviewServiceSoap');

    -- Set the Operation

    l_operation_qname := SYS.UTL_DBWS.to_qname(l_namespace, 'Application name');

    -- Create a Service. The Service created here will be used to create a call

    -- to the Web Service.

    l_service := SYS.UTL_DBWS.create_service (wsdl_document_location => URIFACTORY.getURI(l_wsdl_url),

    service_name => l_service_qname);

    -- Create a Call to the Web Service. The call created here will be used to

    -- invoke the Web Services, using the End Point URL.

    l_call := SYS.UTL_DBWS.create_call (

    service_handle => l_service,

    port_name => NULL,

    operation_name => l_operation_qname);

    -- Set the SOAP Action Properties to use.

    sys.utl_dbws.set_property (l_call,'SOAPACTION_USE','TRUE');

    sys.utl_dbws.set_property (l_call,'SOAPACTION_URI', l_namespace /application name);

    sys.utl_dbws.set_property (l_call,'OPERATION_STYLE','document');

    sys.utl_dbws.set_property(l_call, 'ENCODINGSTYLE_URI','schemas.xmlsoap.org/.../&);

    string_type_qname := sys.utl_dbws.to_qname('www.w3.org/.../XMLSchema&, '<InquiryData input string</InquiryData>');

    sys.utl_dbws.add_parameter(l_call, 'Request', string_type_qname, 'ParameterMode.IN');

    sys.utl_dbws.set_return_type(l_call, string_type_qname);

    xml_string := xmltype('<?xml version="1.0" encoding="utf-8"?>

    <ProcessApplication xmlns="l_namespace/">

    <XmlRequest>To be, or not to be</XmlRequest>

    </application name>');

    retx := sys.utl_dbws.invoke(call_Handle => l_call ,request => xml_string);

    l_str := retx.extract('/*').getstringval();

    loop

    exit when l_str is null;

    dbms_output.put_line (substr (l_str, 1, instr (l_str, chr(10)) - 1));

    l_str := substr (l_str, instr (l_str, chr(10)) + 1);

    end loop;

    dbms_output.put_line ('Message returned is: ' || nvl(l_return_string, 'No success message returned'));

    -- Release the call to the Web Service. A Call to a Web Service is a

    -- Resource that needs to be released after the work is done.

    SYS.UTL_DBWS.release_call (call_handle => l_call);

    -- Release the Service. A Service to a Web Service is a

    -- Resource that needs to be released after the work is done.

    SYS.UTL_DBWS.release_service (service_handle => l_service);

    dbms_output.put_line(ANYDATA.AccessVarchar2(l_result));

    -- RETURN ANYDATA.AccessVarchar2(l_result);

    EXCEPTION WHEN OTHERS THEN

    SHOW_MESSAGE('==ErrorCode=='||SQLCODE);

    SHOW_MESSAGE('==ErrorText=='||SQLERRM);

    SHOW_MESSAGE(DBMS_UTILITY.format_error_backtrace);

    end;

    when i run this from sql developer it's throwing this error:
    =ORA-29540: class oracle/jpub/runtime/dbws/DbwsProxy does not exist.

    please tell me how to solve this and c an you tell me how to pass parameters to webservices to invoke a third party engine from oracle.