1 2 Previous Next 15 Replies Latest reply: Mar 20, 2012 7:21 AM by Jstraub-Oracle RSS

    Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23

    Martin1
      Hi,

      i upgraded the APEX environment from 4.1 to 4.1.1.00.23 via p13331096_112020_Generic.zip.
      And now i get the error 'ORA-06503: PL/SQL: Function returned without value' when calling the function apex_web_service.make_request.

      I made no changes to the input parameter when calling the function apex_web_service.make_request. They are exact the same as in APEX 4.1.

      Has anybody an idea? - it's very urgent.

      Thanks in advance and best Regards,
      Martin
        • 1. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
          Patrick Wolf-Oracle
          Hi Martin,

          what does the technical info of the error say. Do you have a error stack with the exact line numbers in which package it's failing. I think that could help us to tack down the problem.

          Regards
          Patrick
          • 2. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
            Martin1
            Hi Patrick,

            thanks for your reply.
            At Monday i can give you the content of the error stack.

            Regards,
            Martin
            • 3. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
              Martin1
              Hi Patrick,

              here i have the full error informations:
              *<SQLERRM>ORA-06503: PL/SQL: Function returned without value*

              *<FormatErrorBacktrace>ORA-06512: at "APEX_040100.WWV_FLOW_WEBSERVICES_API", line 129*
              ORA-06512: at "LPDEL.WSHELPER", line 167

              *<FormatErrorStack>ORA-06503: PL/SQL: Function returned without value*

              Regards,
              Martin
              • 4. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                Patrick Wolf-Oracle
                Hi Martin,

                had a look at our code in wwv_flow_webservices_api.make_request. It appears that the error is raised in the exception handler when there is an error. It's missing a raise in a ELSE.
                exception when others then
                    if sqlcode = -31011 then -- its not xml
                        return xmltype.createxml('<?xml version=''1.0'' encoding=''UTF-8''?><err>'||sqlerrm||'</err>');
                    end if;
                But anyway, the cause seems to be that another error already raised in the execution of make_request. It's hard to diagnose that because the original error message is not visible.
                Do you have access to your APEX_040100 schema? Can you reproduce this on a development system?

                Regards
                Patrick
                -----------
                My Blog: http://www.inside-oracle-apex.com
                APEX Plug-Ins: http://apex.oracle.com/plugins
                Twitter: http://www.twitter.com/patrickwolf
                • 5. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                  Martin1
                  Hi Patrick,

                  i did the upgrade to APEX 4.1.1 at the development environment and i have access to the schema APEX_040100. I had a look at wwv_flow_webservices_api but the body is wrapped. Therefore no further debugging for me is possible.

                  Regards,
                  Martin
                  • 6. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                    Patrick Wolf-Oracle
                    Hi Martin,

                    actually apex_web_services.make_request is just a simple wrapper for calling the private package wwv_flow_web_services.
                        l_clob := wwv_flow_web_services.make_soap_request(
                            p_url               => p_url,
                            p_action            => p_action,
                            p_version           => p_version,
                            p_envelope          => p_envelope,
                            p_username          => p_username,
                            p_password          => p_password,
                            p_proxy_override    => p_proxy_override,
                            p_transfer_timeout  => nvl(p_transfer_timeout,180),
                            p_wallet_path       => p_wallet_path,
                            p_wallet_pwd        => p_wallet_pwd
                            );
                        return xmltype.createxml(l_clob);
                    So what you could do to debug the issue is to temporarily grant the execute privilege for wwv_flow_web_services to your application parsing schema LPDEL. This would allow you to directly call it in your WSHELPER package and hopefully we will get a better error message then. After your tests don't forget to revoke the privilege again to not expose any security risks in your development environment.

                    Regards
                    Patrick
                    -----------
                    My Blog: http://www.inside-oracle-apex.com
                    APEX Plug-Ins: http://apex.oracle.com/plugins
                    Twitter: http://www.twitter.com/patrickwolf
                    • 7. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                      Martin1
                      Hi Patrick,

                      here my testcase:

                      DECLARE
                      vClob CLOB;
                      BEGIN
                      vClob := apex_040100.wwv_flow_web_services.make_soap_request(
                      p_url => 'http://....',
                      p_action => 'http://....',
                      p_version => NULL,
                      p_envelope => '<soapenv:....</soapenv:Envelope>',
                      p_username => NULL,
                      p_password => NULL,
                      p_proxy_override => NULL,
                      p_transfer_timeout => NVL(NULL,180),
                      p_wallet_path => NULL,
                      p_wallet_pwd => NULL
                      );
                      EXCEPTION
                      WHEN OTHERS THEN
                      adtLog.logError(pSender => 'Test', pMsg => '<FormatErrorBacktrace>' ||
                      dbms_utility.format_error_backtrace,
                      pApplUser => lpu_apex_api.getRemoteUser(pWithDomain => FALSE, pSessionId => nv('APP_SESSION')));
                      adtLog.logError(pSender => 'Test', pMsg => '<FormatErrorStack>' ||
                      dbms_utility.format_error_stack,
                      pApplUser => lpu_apex_api.getRemoteUser(pWithDomain => FALSE, pSessionId => nv('APP_SESSION')));
                      END;

                      Error information:
                      <FormatErrorBacktrace>ORA-06512: at "SYS.UTL_HTTP", line 1474
                      ORA-06512: at "APEX_040100.WWV_FLOW_WEB_SERVICES", line 631
                      ORA-06512: at line 4
                      <FormatErrorStack>ORA-29273: HTTP request failed
                      ORA-06512: at "SYS.UTL_HTTP", line 1474
                      ORA-06502: PL/SQL: numeric or value error: character string buffer too small

                      Regards,
                      Martin
                      • 8. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                        Martin1
                        Hi Patrick,

                        my customer decided to downgrade to APEX 4.1.0.00.32. Therefore i can't do further research on the errors.

                        Regards,
                        Martin
                        • 9. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                          Jstraub-Oracle
                          Martin:

                          Thank you for reporting the issue. As Patrick pointed out, it is unfortunate that there is deficient error handling in apex_web_service, which hopefully will be improved in the future. I created the following test case, and have not been able to reproduce the issue you reported.
                          declare
                              l_envelope  CLOB;
                              l_xml       XMLTYPE;
                              l_temp      varchar2(4000);
                          BEGIN
                          
                              l_envelope := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:tem="http://tempuri.org/">
                             <soapenv:Header/>
                             <soapenv:Body>
                                <tem:FahrenheitToCelsius>
                                   <tem:Fahrenheit>71</tem:Fahrenheit>
                                </tem:FahrenheitToCelsius>
                             </soapenv:Body>
                          </soapenv:Envelope>';
                          
                              l_xml := apex_web_service.make_request(
                                          p_url               => 'http://www.w3schools.com/webservices/tempconvert.asmx',
                                          p_action            => 'http://tempuri.org/FahrenheitToCelsius',
                                          p_envelope          => l_envelope
                          );
                          
                              l_temp := apex_web_service.parse_xml(l_xml,'//FahrenheitToCelsiusResponse/FahrenheitToCelsiusResult/text()','xmlns="http://tempuri.org/"');
                          
                              dbms_output.put_line('71 fahrenheit is '||l_temp||' celsius.');
                          
                          END;
                          /
                          The same code works on both 4.1.0.00.32 and 4.1.1.00.23. The error you got is kind of cryptic, numeric or value error. If you can provide any other details about how your use of apex_web_service.make_request is different than the test case above, it would be appreciated.

                          Regards,

                          Jason
                          • 10. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                            Martin1
                            Hi Jason,

                            thanks for your reply.

                            In the following manner i use the function apex_web_service.make_request.
                            It works with APEX 4.1.0.00.32 but not with APEX 4.1.1.00.23.
                            DECLARE
                              vEnvelope      VARCHAR2(4000);
                              vSoapResponse  XMLTYPE;
                            BEGIN
                              vEnvelope := '<soapenv:Envelope xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" ' ||
                                           '  xmlns:ser="http://xxxxxxxxx.yy.zzz/"> ' ||
                                           '  <soapenv:Header/> ' ||
                                           '  <soapenv:Body> ' ||
                                           '    <ser:Geta> ' ||
                                           '      <number>123456789</number> ' ||
                                           '      <username>abcd</username> ' ||
                                           '      <password>dcba</password> ' ||
                                           '      <datefrom>20120302</datefrom> ' ||
                                           '      <dateto></dateto> ' ||
                                           '    </ser:Geta> ' ||
                                           '  </soapenv:Body> ' ||
                                           '</soapenv:Envelope>';
                              vSoapResponse := apex_web_service.make_request(
                                p_url => 'http://xxxxxxxxx.yyyyyyyyyy.com/client/xyz',
                                p_action => 'http://xxxxxxxxx.yy.zzz/Geta',
                                p_envelope => vEnvelope);
                            EXCEPTION
                              WHEN OTHERS THEN
                                RAISE;
                            END;
                            Best Regards,
                            Martin
                            • 11. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                              Martin1
                              Hi Jason,

                              could you find what's wrong?

                              Regards,
                              Martin
                              • 12. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                                Jstraub-Oracle
                                Martin:

                                The form of your test case does not look any different than the one I used to validate against both 4.1.0.00.32 and 4.1.1.00.23. I don't have an answer at the moment on why your call stopped working in 4.1.1.00.23. The code line really didn't change, but if I come up with any ideas, I will post them.

                                Regards,

                                Jason
                                • 13. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                                  martinbn
                                  Hi

                                  We have the exact same issue after upgrading to 4.1.1.

                                  Hope you find a solution.

                                  Brgds
                                  Martin Nielsen
                                  • 14. Re: Problem with apex_web_service.make_request after Upgrade to 4.1.1.00.23
                                    martinbn
                                    Hi Again

                                    I found the issue with out webservice call. The problem was the ACL.
                                    Since our APEX schema has changed from APEX_040000 til APEX_040100, we needed to adjust the ACL:

                                    BEGIN
                                    DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE(acl => 'sap_label.xml',
                                    principal => 'APEX_040100',
                                    is_grant => true,
                                    privilege => 'connect');

                                    END;

                                    Now the webservice works fine.

                                    However it would be nice with some more specific error message (just like utl_http.request), where the error message explains the missing setup in the acls.

                                    Brgds
                                    Martin Nielsen
                                    1 2 Previous Next