This discussion is archived
12 Replies Latest reply: Apr 19, 2013 7:01 AM by BruceKing RSS

SOAP Webservice Error

BruceKing Newbie
Currently Being Moderated
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 Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Hi Denes,

    Thanks for the reply! Would you have any sample code that you could pass along.
  • 3. Re: SOAP Webservice Error
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks again Hari!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points