11 Replies Latest reply on Oct 1, 2015 3:04 AM by Scott Wesley

    APEX webservice visibility vs ORDS

    Scott Wesley

      Hi,

       

      We've upgrade to APEX 5.0.1 and ORDS 3.0 (not sure if 3.0.1), and we have working webservices using ORDS, but they are not shown in SQL Workshop, nor do APP_IMAGES get rendered.

       

      I'm a little sketchy on the details as I did not set this up and my colleague is not at hand, but I'll do best to provide some facts that I'm sure will highlight some simple setting that needs adjusting.

       

      My colleague had web service issues with our existing workspace so he tried creating another, and in the second new workspace it's all working correctly.

      ie - I can see services listed in the workshop and images from the repository are rendered correctly using #APP_IMAGES#my_image.png.

       

      For instance, in the new workspace the path resolves as

      <img src="125117155154685/r/103/files/static/v2/button_colours.png">

      in the workspace that doesn't work it is

      <img src="our_workspace_name/r/101/files/static/v2Y/button_colours.png">

      and the browser complains it couldn't GET the file.

       

      Selecting from apex_050000.WWV_FLOW_RT$MODULES returns three older records we tested with a while ago, that I see listed in the workshop only in the new workspace.

      ORDS_METADATA.ORDS_MODULES shows three different modules, created by my colleage, not seen in either workspace in SQL workshop. But these are active modules managed by my colleague in SQL Developer (I think).

       

      Users APEX_LISTENER and APEX_REST_PUBLIC_USER exist and are unlocked.

       

      I've been using Dietmar's kscope session to try get my head around it, and it seems slide 13 relates to our problem.

      daust.blogspot.com.au/2015/09/presentation-slides-for-my-ords-talk-at.html

       

      Any suggestions on things to check/tweak?

       

      Scott.

        • 1. Re: APEX webservice visibility vs ORDS
          Dietmar Aust

          Hi Scott,

           

          please start the ords.war file on the command line:

           

          java -jar ords.war
          

           

          You might even want to turn on debugging in the defaults.xml file:

           

          <entry key="debug.debugger">true</entry>
          

           

          On the command line it will check the validity of all connection pools and also directly show you the error messages, it is just a lot easier to spot.

           

          Cheers,

          ~Dietmar.

          • 2. Re: APEX webservice visibility vs ORDS
            Scott Wesley

            Thanks, I'll wait for my colleague to get back and we'll look deeper.

             

            Here is relevant result from proxy_users (as per twitter comment)

              

            PROXYCLIENT
            ORDS_PUBLIC_USEROUR_SCHEMA
            APEX_REST_PUBLIC_USERAPEX_PUBLIC_USER

             

            Not sure if we migrated existing repository, but I can find out. Unlikely as we didn't have anything here beyond test cases before upgrade.

            • 3. Re: Re: APEX webservice visibility vs ORDS
              Dietmar Aust

              Hi Scott,

               

              I could reproduce the issue on my system (almost). Typically this error comes up when the connection pools for APEX_REST_PUBLIC_USER and APEX_LISTENER are not working properly.

               

              This is my working config directory. As you can see, I have four connection pools configured (apex.xml for apex_public_user, apex_rt.xml for APEX_REST_PUBLIC_USER, apex_pu.xml for ORDS_PUBLIC_USER and apex_al.xml for APEX_LISTENER).

              app.png

              They all need to be working properly.

               

              Once I delete apex_rt.xml and apex_al.xml (and restart ords) I get the same error as you, the image won't show up in my application. Thus, the original rest users (apex_rest_public_user and apex_listener) are still required in APEX for displaying the APP_IMAGES and WORKSPACE_IMAGES.

               

              The only thing I cannot get my head around is that the images show up in a newly created workspace (as you noted), this is indeed strange.

               

              So:

              1) make sure you use the latest version 3.0.1

              2) make sure that the connection pools for apex_rest_public_user and apex_listener are working correctly.

              3) turn on debugging in defaults.xml:

               

              <entry key="debug.debugger">false</entry>
              <entry key="debug.printDebugToScreen">false</entry>
              

               

              Perhaps you might even have to reinstall everything. Make sure to use a fresh config directory and download/extract into a clean directory, ords will "remember" the values you have entered in the file params/ords_params.properties.

               

              If all that doesn't help, perhaps your problem is related to a different "feature" of APEX 5. They have tightened up the reference syntax in 5.0, you cannot reference #APP_IMAGES# via the #WORKSPACE_IMAGES# tag and vice versa (if you are running in APEX 5.0 compatibility mode, it still works in 4.2 compatibility mode I guess). So make sure, you reference the images correctly.

               

              Hope that helps,

              ~Dietmar.

              1 person found this helpful
              • 4. Re: Re: APEX webservice visibility vs ORDS
                Dietmar Aust

                Perhaps you might even have to reinstall everything. Make sure to use a fresh config directory and download/extract into a clean directory, ords will "remember" the values you have entered in the file params/ords_params.properties.

                Just to clarify, with "everything" I mean just rerunning the ORDS setup so that all connection pools are created again. You shouldn't even have to rerun the installation of the database schemas ORDS_METADATA and ORDS_PUBLIC_USER, just the correct xml files should be all you need.

                • 5. Re: APEX webservice visibility vs ORDS
                  Kiran Pawar

                  Hi swesley_perth,

                  swesley_perth wrote:

                   

                  We've upgrade to APEX 5.0.1 and ORDS 3.0 (not sure if 3.0.1), and we have working webservices using ORDS, but they are not shown in SQL Workshop, nor do APP_IMAGES get rendered.

                  I'm a little sketchy on the details as I did not set this up and my colleague is not at hand, but I'll do best to provide some facts that I'm sure will highlight some simple setting that needs adjusting.

                  My colleague had web service issues with our existing workspace so he tried creating another, and in the second new workspace it's all working correctly.

                  ie - I can see services listed in the workshop and images from the repository are rendered correctly using #APP_IMAGES#my_image.png.

                  For instance, in the new workspace the path resolves as

                  <img src="125117155154685/r/103/files/static/v2/button_colours.png">

                  in the workspace that doesn't work it is

                  <img src="our_workspace_name/r/101/files/static/v2Y/button_colours.png">

                  and the browser complains it couldn't GET the file.

                  Selecting from apex_050000.WWV_FLOW_RT$MODULES returns three older records we tested with a while ago, that I see listed in the workshop only in the new workspace.

                  ORDS_METADATA.ORDS_MODULES shows three different modules, created by my colleage, not seen in either workspace in SQL workshop. But these are active modules managed by my colleague in SQL Developer (I think).

                  Users APEX_LISTENER and APEX_REST_PUBLIC_USER exist and are unlocked.

                  I've been using Dietmar's kscope session to try get my head around it, and it seems slide 13 relates to our problem.

                  daust.blogspot.com.au/2015/09/presentation-slides-for-my-ords-talk-at.html

                  Any suggestions on things to check/tweak?

                       For debugging this issue you are facing on ORDS:

                   

                       Does this problem occur for only one file or all the static workspace/application files?

                       Enable the Network tab of the Browser Development Tools and inspect what is happening if you load a page with static workspace/application files.

                       If you are getting 404 for the workspace/application static files then it is the issue of bad RESTful Services Configuration.

                   

                       The RESTful Services configuration is mandatory in APEX 5 and it is not just limited to configuration using apex_rest_config.sql.

                       The second step involves configuration of users required for RESTful Services while installing ORDS?

                       Refer : http://docs.oracle.com/cd/E56351_01/doc.30/e56293/install.htm#CHDGAAHJ

                   

                       Refer the following threads with same issue :

                   

                       Also check whether the RESTful users(APEX_LISTENER and APEX_REST_PUBLIC_USER) are unlocked as that may be also causing the problem.

                       Refer : 503-service unavailable error after deploying apex listener on glassfish

                   

                  Regards,

                  Kiran

                  • 6. Re: Re: APEX webservice visibility vs ORDS
                    Scott Wesley

                    Tracing has already been enabled.

                    The ORDS server has been restarted.

                    The ORDS users appear to be correctly configured.

                    Existing WS definitions defined using SQL Developer

                    Pretty sure our version is 3.0.0, but we're not sure how to double check.

                     

                    Sep 21, 2015 10:38:16 AM oracle.dbtools.common.config.db.ConfigurationValues log
                    WARNING: *** jdbc.MaxLimit in configuration apex is using a value of 10, this setting may not be sized adequately for a production environment ***
                    Sep 21, 2015 10:38:16 AM oracle.dbtools.common.config.db.ConfigurationValues log
                    WARNING: *** jdbc.InitialLimit in configuration apex is using a value of 3, this setting may not be sized adequately for a production environment ***
                    Sep 21, 2015 10:38:16 AM oracle.ucp.common.UniversalConnectionPoolBase initInactiveConnectionTimeoutTimer
                    INFO: inactive connection timeout timer scheduled
                    Sep 21, 2015 11:38:54 AM oracle.ucp.common.UniversalConnectionPoolBase disableInactiveConnectionTimeoutTimer
                    INFO: inactive connection timeout timer canceled
                    Sep 21, 2015 11:38:54 AM oracle.ucp.common.UniversalConnectionPoolBase disableInactiveConnectionTimeoutTimer
                    INFO: inactive connection timeout timer canceled
                    Sep 21, 2015 11:38:54 AM oracle.ucp.common.UniversalConnectionPoolBase disableInactiveConnectionTimeoutTimer
                    INFO: inactive connection timeout timer canceled
                    Sep 21, 2015 11:39:00 AM oracle.dbtools.common.config.db.DatabasePools validatePool
                    INFO: Validating pool: apex
                    Sep 21, 2015 11:39:00 AM oracle.dbtools.common.config.db.DatabasePools validatePool
                    INFO: Pool: apex is correctly configured
                    Sep 21, 2015 11:39:00 AM oracle.dbtools.common.config.db.DatabasePools validatePool
                    INFO: Validating pool: apex_al
                    Sep 21, 2015 11:39:00 AM oracle.dbtools.common.config.db.DatabasePools validatePool
                    INFO: Pool: apex_al is correctly configured
                    Sep 21, 2015 11:39:00 AM oracle.dbtools.common.config.db.DatabasePools validatePool
                    INFO: Validating pool: apex_pu
                    Sep 21, 2015 11:39:00 AM oracle.dbtools.common.config.db.DatabasePools validatePool
                    INFO: Pool: apex_pu is correctly configured
                    Sep 21, 2015 11:39:00 AM oracle.dbtools.common.config.db.DatabasePools validatePool
                    INFO: Validating pool: apex_rt
                    Sep 21, 2015 11:39:00 AM oracle.dbtools.common.config.db.DatabasePools validatePool
                    INFO: Pool: apex_rt is correctly configured
                    Sep 21, 2015 11:39:03 AM oracle.dbtools.common.config.db.DatabasePools createPool
                    INFO: Configuration properties for: apex_pu
                    Sep 21, 2015 11:39:03 AM oracle.dbtools.common.config.db.DatabasePools listConfiguration
                    INFO: cache.caching=false
                    cache.directory=/tmp/apex/cache
                    cache.duration=days
                    cache.expiration=7
                    cache.maxEntries=500
                    cache.monitorInterval=60
                    cache.procedureNameList=
                    cache.type=lru
                    db.hostname=our_host.com.au
                    db.port=1521
                    db.servicename=
                    db.sid=dev
                    debug.debugger=true
                    debug.printDebugToScreen=true
                    error.keepErrorMessages=true
                    error.maxEntries=50
                    jdbc.DriverType=thin
                    jdbc.InactivityTimeout=1800
                    jdbc.InitialLimit=3
                    jdbc.MaxConnectionReuseCount=1000
                    jdbc.MaxLimit=10
                    jdbc.MaxStatementsLimit=10
                    jdbc.MinLimit=1
                    jdbc.statementTimeout=900
                    log.logging=false
                    log.maxEntries=50
                    misc.compress=
                    misc.defaultPage=apex
                    security.crypto.enc.password=******
                    security.crypto.mac.password=******
                    security.disableDefaultExclusionList=false
                    security.maxEntries=2000
                    db.password=******
                    db.username=ORDS_PUBLIC_USER
                    

                     

                    Here is the debug information when opening the image in the url

                    ords_error.png

                    I think if there was an issue with the ORDS setup, then it would affect both workspaces. So we've been concentrating our efforts to determine any differences between workspaces, to no avail.

                    • 7. Re: APEX webservice visibility vs ORDS
                      Scott Wesley

                      Speaking of workspace differences, I thought I had mentioned this already but I was wondering if it could have an impact here.

                       

                      The workspace status is most pertinent because they are both provisioned in another instance.

                      ws_differences.png

                      What does the workspace status mean, and how can I influence it?

                      1 person found this helpful
                      • 8. Re: APEX webservice visibility vs ORDS
                        Kiran Pawar

                        Hi Scott,

                        swesley_perth wrote:

                         

                        Tracing has already been enabled.

                        The ORDS server has been restarted.

                        The ORDS users appear to be correctly configured.

                        Existing WS definitions defined using SQL Developer

                        Pretty sure our version is 3.0.0, but we're not sure how to double check.

                             Login into your workspace and click the "About" link:

                             about1.png

                         

                             You will get the APEX Listener Version here :

                             about2.png

                             Regarding this:

                        Here is the debug information when opening the image in the url

                        ords_error.png

                        I think if there was an issue with the ORDS setup, then it would affect both workspaces. So we've been concentrating our efforts to determine any differences between workspaces, to no avail.

                             Is this the only debug trace? Then, it is possible that you have hit a dead end.

                             Refer : Possible bug ORDS 3.0/3.0.1 404 - oracle.dbtools.http.errors.NotFoundException at oracle.dbtools.url.mapping.URLMappingsProvider.map(URLMappingsProvider.java:79)

                             As you have seen above apex.oracle.com also uses the ORDS 2.0.10 instead of ORDS 3.0.0/3.0.1 and refer the reply in the following thread.

                             Refer : Re: Issue in ORDS Setup.

                         

                        Regards,

                        Kiran

                        • 9. Re: APEX webservice visibility vs ORDS
                          Dietmar Aust

                          Hi Scott,

                           

                          it seems like you are hitting a bug.

                           

                          I am currently doing our annual APEX training together with Denes in Germany. Tomorrow I will talk about the known issues for the current release ... and here i stumbled across:

                           

                          Application Express 5.0.1 Known Issues

                           

                          Application_Express_5_0_1_Known_Issues.png

                           

                          This sounds a lot like your problem ... especially that it works for newly created workspaces.

                           

                          Cheers,

                          ~Dietmar.

                          • 10. Re: APEX webservice visibility vs ORDS
                            Scott Wesley

                            Thanks Dietmar, this was exactly our issue. That workspace was defined circa 4.0

                             

                            Workaround applied, problem fixed.

                             

                            And a little red in the face for not spotted that known issue!

                            • 11. Re: APEX webservice visibility vs ORDS
                              Scott Wesley

                              We're not sure how my colleague found the relevant document (2054141.1), because none of our searches in MOS for 21537995 or the relevant text return results?

                              Note neither of us use MOS very often...