12 Replies Latest reply: Apr 19, 2013 9:01 AM by BruceKing RSS

    SOAP Webservice Error

    BruceKing
      Hi All,

      I have referenced web service WSDL in apex 4.2, the web service allows for a database record to be submitted in a remote database. When I test it using the test function from web reference page in apex it works fine. When I create a page for the user interface and submit, I get an error.

      Server.userExceptionARERR [149] A user name must be supplied in the control.

      It seems that the user name is not being passed from the page although it's mapped, maybe the soap header is not getting set with the username and password. Anyone have any ideas why this works when testing from the web service reference page and not the user interface that was created.

      Thanks,
      Bruce.
        • 1. Re: SOAP Webservice Error
          Denes Kubicek
          Try using webservice API instead of webservice wizard.
          APEX_WEB_SERVICE.MAKE_REQUEST (
              p_url               IN VARCHAR2,
              p_action            IN VARCHAR2 default null,
              p_version           IN VARCHAR2 default '1.1',
              p_collection_name   IN VARCHAR2 default null,
              p_envelope          IN CLOB,
              p_username          IN VARCHAR2 default null,
              p_password          IN VARCHAR2 default null,
              p_proxy_override    IN VARCHAR2 default null,
              p_wallet_path       IN VARCHAR2 default null,
              p_wallet_pwd        IN VARCHAR2 default null );
          Denes Kubicek
          -------------------------------------------------------------------
          http://deneskubicek.blogspot.com/
          http://www.apress.com/9781430235125
          http://apex.oracle.com/pls/apex/f?p=31517:1
          http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
          -------------------------------------------------------------------
          • 2. Re: SOAP Webservice Error
            BruceKing
            Hi Denes,

            Thanks for the reply! Would you have any sample code that you could pass along.
            • 3. Re: SOAP Webservice Error
              Denes Kubicek
              If you test your webservice it will generate an envelope and show it. You can use that envelope, create a package wich generates it and uses the posted API call.

              Denes Kubicek
              -------------------------------------------------------------------
              http://deneskubicek.blogspot.com/
              http://www.apress.com/9781430235125
              http://apex.oracle.com/pls/apex/f?p=31517:1
              http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
              -------------------------------------------------------------------
              • 4. Re: SOAP Webservice Error
                BruceKing
                Hi Denes,

                Thanks for the reply. I managed to get the webservice to work at least part way. I used a page process on submit and PL/SQL with the following. Now I have to try to manage the response, if you have any suggestion?

                declare
                l_envelope CLOB;
                l_xml XMLTYPE;

                BEGIN

                l_envelope :=
                '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:HPD_WorkLog">
                <soapenv:Header>
                <urn:AuthenticationInfo>
                <urn:userName>username</urn:userName>
                <urn:password>password</urn:password>
                <!--Optional:-->
                <urn:authentication></urn:authentication>
                <!--Optional:-->
                <urn:locale></urn:locale>
                <!--Optional:-->
                <urn:timeZone></urn:timeZone>
                </urn:AuthenticationInfo>
                </soapenv:Header>
                <soapenv:Body>
                <urn:OpCreate>
                <urn:Assign_WorkLog_Flag></urn:Assign_WorkLog_Flag>
                <urn:Assigned_Group></urn:Assigned_Group>
                <urn:Assigned_Group_ID></urn:Assigned_Group_ID>
                <urn:Assigned_Group_Shift_ID></urn:Assigned_Group_Shift_ID>
                <urn:Assigned_Group_Shift_Name></urn:Assigned_Group_Shift_Name>
                <urn:Assigned_Support_Company></urn:Assigned_Support_Company>
                <urn:Assigned_Support_Organization></urn:Assigned_Support_Organization>
                <urn:Assigned_To></urn:Assigned_To>
                <urn:Assignee></urn:Assignee>
                <urn:Assignee_Groups></urn:Assignee_Groups>
                <urn:Assignee_Login_ID></urn:Assignee_Login_ID>
                <urn:Assignment_Log_Created></urn:Assignment_Log_Created>
                <urn:Communication_Source></urn:Communication_Source>
                <urn:Communication_Type></urn:Communication_Type>
                <urn:Company></urn:Company>
                <urn:DataTags></urn:DataTags>
                <urn:Description>&P35_SUMMARY.</urn:Description>
                <urn:Detailed_Description>&P35_NOTE.</urn:Detailed_Description>
                <urn:Full_Name>Bruce King</urn:Full_Name>
                <urn:HPD_Attachment_ID></urn:HPD_Attachment_ID>
                <urn:Incident_Entry_ID></urn:Incident_Entry_ID>
                <urn:Incident_Number>&P35_INCIDENT_NUMBER.</urn:Incident_Number>
                <urn:InstanceId></urn:InstanceId>
                <urn:Number_of_Attachments></urn:Number_of_Attachments>
                <urn:Number_of_URLs></urn:Number_of_URLs>
                <urn:Secure_Work_Log>Yes</urn:Secure_Work_Log>
                <urn:Shifts_Flag></urn:Shifts_Flag>
                <urn:Short_Description>NA</urn:Short_Description>
                <urn:Status>Enabled</urn:Status>
                <urn:Submitter>brucek</urn:Submitter>
                <urn:Total_Time_Spent></urn:Total_Time_Spent>
                <urn:URL01></urn:URL01>
                <urn:URL02></urn:URL02>
                <urn:URL03></urn:URL03>
                <urn:View_Access>Internal</urn:View_Access>
                <urn:Work_Log_Date></urn:Work_Log_Date>
                <urn:Work_Log_Submit_Date>2013-04-13T00:00:00Z</urn:Work_Log_Submit_Date>
                <urn:Work_Log_Submitter>brucek</urn:Work_Log_Submitter>
                <urn:Work_Log_Type>General Information</urn:Work_Log_Type>
                <urn:WorkLog_Action_Completed></urn:WorkLog_Action_Completed>
                <urn:WorkLog_Action_Status></urn:WorkLog_Action_Status>
                </urn:OpCreate>
                </soapenv:Body>
                </soapenv:Envelope>';

                l_xml := apex_web_service.make_request(
                p_url => 'http://xxx.domain.com/arsys/services/ARService?server=xxx.domain.com&webService=HPD_WorkLog',
                p_action => 'http://xxx.domain.com/arsys/services/ARService?server=xxx.domain.com&webService=HPD_WorkLog/OpCreate',
                p_envelope => l_envelope
                );

                END;
                • 5. Re: SOAP Webservice Error
                  Hari_639
                  Hello,

                  Add xml response to apex_collections, then use xquery to fetch data from XML.

                  See {message:id=10927559} for sample query

                  Regards,
                  Hari
                  • 6. Re: SOAP Webservice Error
                    BruceKing
                    Hi Hari

                    When I run the WS using soapUI I get the following response. How would I use the xquery to handle it?

                    <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                    <soapenv:Body>
                    <ns0:OpCreateResponse xmlns:ns0="urn:BMCHPD">
                    <ns0:Request_ID>000000000000121</ns0:Request_ID>
                    </ns0:OpCreateResponse>
                    </soapenv:Body>
                    </soapenv:Envelope>
                    • 7. Re: SOAP Webservice Error
                      Hari_639
                      here is the query..
                      with t as (select XMLType('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                      <soapenv:Body>
                      <ns0:OpCreateResponse xmlns:ns0="urn:BMCHPD">
                      <ns0:Request_ID>000000000000121</ns0:Request_ID>
                      </ns0:OpCreateResponse>
                      </soapenv:Body>
                      </soapenv:Envelope>') xml_data from dual)
                      SELECT xmlresponse.*
                      FROM t, XMLTable(Xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
                                                                                                  'urn:BMCHPD' AS "ns0"),
                                                              '/soapenv:Envelope/soapenv:Body/ns0:OpCreateResponse'            
                                      PASSING xml_data
                                                              COLUMNS
                                                              Request_ID NUMBER Path 'ns0:Request_ID'  
                                                              ) xmlresponse;
                      Regards,
                      Hari
                      • 8. Re: SOAP Webservice Error
                        BruceKing
                        Hi Hari, Thanks for the quick reply. Sorry, I'm pretty new to APEX and PL/SQL. I have added the code you sent to the PL/SQL and I get ORA-06550: line 75, column 1: PLS-00428: an INTO clause is expected in this SELECT statement.

                        I'm sure it's my error, I'm not sure if I added it to the correct location. Just an FYI, this webservice allows me to create a record in a remote database and the response returned is the ID (Request_ID) of the record if successful. The response will also return with any errors that may occur. The entire block of PL/SQL that I'm trying including you code is below. I need the response returned to a page item. Thanks again for the help!

                        declare
                        l_envelope CLOB;
                        l_xml XMLTYPE;

                        BEGIN

                        l_envelope :=
                        '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:HPD_WorkLog">
                        <soapenv:Header>
                        <urn:AuthenticationInfo>
                        <urn:userName>username</urn:userName>
                        <urn:password>password</urn:password>
                        <!--Optional:-->
                        <urn:authentication></urn:authentication>
                        <!--Optional:-->
                        <urn:locale></urn:locale>
                        <!--Optional:-->
                        <urn:timeZone></urn:timeZone>
                        </urn:AuthenticationInfo>
                        </soapenv:Header>
                        <soapenv:Body>
                        <urn:OpCreate>
                        <urn:Assign_WorkLog_Flag></urn:Assign_WorkLog_Flag>
                        <urn:Assigned_Group></urn:Assigned_Group>
                        <urn:Assigned_Group_ID></urn:Assigned_Group_ID>
                        <urn:Assigned_Group_Shift_ID></urn:Assigned_Group_Shift_ID>
                        <urn:Assigned_Group_Shift_Name></urn:Assigned_Group_Shift_Name>
                        <urn:Assigned_Support_Company></urn:Assigned_Support_Company>
                        <urn:Assigned_Support_Organization></urn:Assigned_Support_Organization>
                        <urn:Assigned_To></urn:Assigned_To>
                        <urn:Assignee></urn:Assignee>
                        <urn:Assignee_Groups></urn:Assignee_Groups>
                        <urn:Assignee_Login_ID></urn:Assignee_Login_ID>
                        <urn:Assignment_Log_Created></urn:Assignment_Log_Created>
                        <urn:Communication_Source></urn:Communication_Source>
                        <urn:Communication_Type></urn:Communication_Type>
                        <urn:Company></urn:Company>
                        <urn:DataTags></urn:DataTags>
                        <urn:Description>&P35_SUMMARY.</urn:Description>
                        <urn:Detailed_Description>&P35_NOTE.</urn:Detailed_Description>
                        <urn:Full_Name>Bruce King</urn:Full_Name>
                        <urn:HPD_Attachment_ID></urn:HPD_Attachment_ID>
                        <urn:Incident_Entry_ID></urn:Incident_Entry_ID>
                        <urn:Incident_Number>&P35_INCIDENT_NUMBER.</urn:Incident_Number>
                        <urn:InstanceId></urn:InstanceId>
                        <urn:Number_of_Attachments></urn:Number_of_Attachments>
                        <urn:Number_of_URLs></urn:Number_of_URLs>
                        <urn:Secure_Work_Log>Yes</urn:Secure_Work_Log>
                        <urn:Shifts_Flag></urn:Shifts_Flag>
                        <urn:Short_Description>NA</urn:Short_Description>
                        <urn:Status>Enabled</urn:Status>
                        <urn:Submitter>brucek</urn:Submitter>
                        <urn:Total_Time_Spent></urn:Total_Time_Spent>
                        <urn:URL01></urn:URL01>
                        <urn:URL02></urn:URL02>
                        <urn:URL03></urn:URL03>
                        <urn:View_Access>Internal</urn:View_Access>
                        <urn:Work_Log_Date></urn:Work_Log_Date>
                        <urn:Work_Log_Submit_Date>2013-04-13T00:00:00Z</urn:Work_Log_Submit_Date>
                        <urn:Work_Log_Submitter>brucek</urn:Work_Log_Submitter>
                        <urn:Work_Log_Type>General Information</urn:Work_Log_Type>
                        <urn:WorkLog_Action_Completed></urn:WorkLog_Action_Completed>
                        <urn:WorkLog_Action_Status></urn:WorkLog_Action_Status>
                        </urn:OpCreate>
                        </soapenv:Body>
                        </soapenv:Envelope>';

                        l_xml := apex_web_service.make_request(
                        p_url => 'http://xxx.domain.com/arsys/services/ARService?server=xxx.domain.com&webService=HPD_WorkLog',
                        p_action => 'http://xxx.domain.com/arsys/services/ARService?server=xxx.domain.com&webService=HPD_WorkLog/OpCreate',
                        p_envelope => l_envelope
                        );

                        with t as (select XMLType('<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                        <soapenv:Body>
                        <ns0:OpCreateResponse xmlns:ns0="urn:BMCHPD">
                        <ns0:Request_ID>000000000000121</ns0:Request_ID>
                        </ns0:OpCreateResponse>
                        </soapenv:Body>
                        </soapenv:Envelope>') xml_data from dual)
                        SELECT xmlresponse.*
                        FROM t, XMLTable(Xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
                                                                                                   'urn:BMCHPD' AS "ns0"),
                                                                '/soapenv:Envelope/soapenv:Body/ns0:OpCreateResponse'
                        PASSING xml_data
                                                                COLUMNS
                                                                Request_ID NUMBER Path 'ns0:Request_ID'
                                                                ) xmlresponse;

                        END;
                        • 9. Re: SOAP Webservice Error
                          Hari_639
                          DECLARE
                               l_envelope CLOB;
                               l_xml XMLTYPE;
                          BEGIN
                               l_envelope :=
                               '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:urn="urn:HPD_WorkLog">
                               <soapenv:Header>
                               <urn:AuthenticationInfo>
                               <urn:userName>username</urn:userName>
                               <urn:password>password</urn:password>
                               <!--Optional:-->
                               <urn:authentication></urn:authentication>
                               <!--Optional:-->
                               <urn:locale></urn:locale>
                               <!--Optional:-->
                               <urn:timeZone></urn:timeZone>
                               </urn:AuthenticationInfo>
                               </soapenv:Header>
                               <soapenv:Body>
                               <urn:OpCreate>
                               <urn:Assign_WorkLog_Flag></urn:Assign_WorkLog_Flag>
                               <urn:Assigned_Group></urn:Assigned_Group>
                               <urn:Assigned_Group_ID></urn:Assigned_Group_ID>
                               <urn:Assigned_Group_Shift_ID></urn:Assigned_Group_Shift_ID>
                               <urn:Assigned_Group_Shift_Name></urn:Assigned_Group_Shift_Name>
                               <urn:Assigned_Support_Company></urn:Assigned_Support_Company>
                               <urn:Assigned_Support_Organization></urn:Assigned_Support_Organization>
                               <urn:Assigned_To></urn:Assigned_To>
                               <urn:Assignee></urn:Assignee>
                               <urn:Assignee_Groups></urn:Assignee_Groups>
                               <urn:Assignee_Login_ID></urn:Assignee_Login_ID>
                               <urn:Assignment_Log_Created></urn:Assignment_Log_Created>
                               <urn:Communication_Source></urn:Communication_Source>
                               <urn:Communication_Type></urn:Communication_Type>
                               <urn:Company></urn:Company>
                               <urn:DataTags></urn:DataTags>
                               <urn:Description>&P35_SUMMARY.</urn:Description>
                               <urn:Detailed_Description>&P35_NOTE.</urn:Detailed_Description>
                               <urn:Full_Name>Bruce King</urn:Full_Name>
                               <urn:HPD_Attachment_ID></urn:HPD_Attachment_ID>
                               <urn:Incident_Entry_ID></urn:Incident_Entry_ID>
                               <urn:Incident_Number>&P35_INCIDENT_NUMBER.</urn:Incident_Number>
                               <urn:InstanceId></urn:InstanceId>
                               <urn:Number_of_Attachments></urn:Number_of_Attachments>
                               <urn:Number_of_URLs></urn:Number_of_URLs>
                               <urn:Secure_Work_Log>Yes</urn:Secure_Work_Log>
                               <urn:Shifts_Flag></urn:Shifts_Flag>
                               <urn:Short_Description>NA</urn:Short_Description>
                               <urn:Status>Enabled</urn:Status>
                               <urn:Submitter>brucek</urn:Submitter>
                               <urn:Total_Time_Spent></urn:Total_Time_Spent>
                               <urn:URL01></urn:URL01>
                               <urn:URL02></urn:URL02>
                               <urn:URL03></urn:URL03>
                               <urn:View_Access>Internal</urn:View_Access>
                               <urn:Work_Log_Date></urn:Work_Log_Date>
                               <urn:Work_Log_Submit_Date>2013-04-13T00:00:00Z</urn:Work_Log_Submit_Date>
                               <urn:Work_Log_Submitter>brucek</urn:Work_Log_Submitter>
                               <urn:Work_Log_Type>General Information</urn:Work_Log_Type>
                               <urn:WorkLog_Action_Completed></urn:WorkLog_Action_Completed>
                               <urn:WorkLog_Action_Status></urn:WorkLog_Action_Status>
                               </urn:OpCreate>
                               </soapenv:Body>
                               </soapenv:Envelope>';
                          
                               l_xml := apex_web_service.make_request(
                               p_url => 'http://xxx.domain.com/arsys/services/ARService?server=xxx.domain.com&webService=HPD_WorkLog',
                               p_action => 'http://xxx.domain.com/arsys/services/ARService?server=xxx.domain.com&webService=HPD_WorkLog/OpCreate',
                               p_envelope => l_envelope
                               );
                          
                               BEGIN
                                    with t as (select l_xml xml_data from dual)
                                    SELECT xmlresponse.Request_ID
                                    -- replace P1_REQUEST_ID with your actual page item
                                    INTO :P1_REQUEST_ID
                                    FROM t, XMLTable(Xmlnamespaces('http://schemas.xmlsoap.org/soap/envelope/' AS "soapenv",
                                    'urn:BMCHPD' AS "ns0"),
                                    '/soapenv:Envelope/soapenv:Body/ns0:OpCreateResponse'
                                    PASSING xml_data
                                    COLUMNS
                                    Request_ID NUMBER Path 'ns0:Request_ID'
                                    ) xmlresponse;
                               EXCEPTION WHEN NO_DATA_FOUND THEN
                                --log error
                                --check  webservice response format for error message
                                -- then you can extract error message and log & riase error using RAISE_APPLICATION_ERROR
                                NULL; --do nothing for time being 
                               END;
                          
                          END;
                          Regards,
                          Hari
                          • 10. Re: SOAP Webservice Error
                            BruceKing
                            Hi Hari,

                            Finally getting to test the code out and your example works great! I sent you the wrong response message, but I followed your sample code. One last question, below is a response message for an invalid login to the web service. Do I have to use the select statement or is there away to just catch any response with faultcode and faultstring. If the submit is successful I get a response with the Request_ID, if not I get a faultcode and faultstring and there can be any number of codes. Thanks again for your help, and If you've had enough of this post I understand, my orginal question has been answered.

                            <soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                            <soapenv:Body>
                            <soapenv:Fault>
                            <faultcode>soapenv:Server.userException</faultcode>
                            <faultstring>ARERR [623] Authentication failed</faultstring>
                            <detail>
                            <ns1:hostname xmlns:ns1="http://xml.apache.org/axis/">BOSCH</ns1:hostname>
                            </detail>
                            </soapenv:Fault>
                            </soapenv:Body>
                            </soapenv:Envelope>
                            • 11. Re: SOAP Webservice Error
                              Hari_639
                              Hello Bruce,

                              Yes, in your page process, you can just check if you can find node "soapenv:Fault" (using XQUERY) and then you can handle it the way you like, RAISE_APPLICATION_ERROR or log to error table or any other frame-work you use to handle errors.

                              Regards,
                              Hari
                              • 12. Re: SOAP Webservice Error
                                BruceKing
                                Thanks again Hari!