8 Replies Latest reply on Oct 15, 2018 9:03 AM by Yvan Poulin

    ORDS 18.3 and multiple connections

    Yvan Poulin

      Dear all,

      I couldn't find any similar issues on internet, therefore I ask my question to you.

       

      I am using ORDS to replace mod_PLSQL connections with DAD files and I am facing a problem after having upgraded ORDS from 18.1.1 to 18.3..

       

      When I installed ORDS 18.1.1, I was able to create multiple connections with different URL mapping. This was creating multiple xml files on the ords/conf/ folder (schemaname-xml, schemaname_rt.xml etc...)

       

      I tried following the upgrade documentation, and the update failed. Then I manually copied the 18.1.1 files into the new 18.3 folder and restart the installation specifying the files location, and it worked. I was able to access APEX and all my previously defined accesses. BUT, when I tried to add a new schema, the new files were not created, the URL mapping was added but it was impossible to connect to the website.

       

      403 when triying to connect to http://servername.xxxx.xx:8080/ords/finapps.intranet.home (where finapps is the name of one schema)

      • Access to the procedure named: finapps.intranet.home is denied.

      500  when trying with http://servername.xxxxx.xx:8080/ords/finapps/intranet.home

      • An unexpected error with the following message occurred: null

       

      Needless to say that on the DB server the grants execute on package to APEX_PUBLIC_USER and the ORDS-enable were already done longtime ago.

       

      After several desperate tentatives to fix the issue, I decided to install ORDS from scratch.   I have a worse result. APEX works, all other  connections fail and I cant find where are stored the configuration files of multiple connections.

       

      So my questions are :

      • Is it still possible to create multiple connections with 18.3 ?
      • How ?
      • where are the configuration files ?

       

      Thanks for your help

        • 1. Re: ORDS 18.3 and multiple connections
          Billy~Verreynne

          Are you running ORDS in standalone mode, or inside Tomcat or Weblogic?

           

          Validate your ORDS database connection's configuration:

          java -jar apache/webapps/ords.war validate --database finapps

           

          Now add the URL mapping as a base path, for the database

          java -jar apache/webapps/ords.war map-url --type base-path /finapps finapps

           

          Make sure that there are no duplicate URL paths in <configdir>/ords/url-mapping.xml.

           

          Use URL http:// <server>:<port> /ords/finapps for access to APEX on the finapps database.

           

          See https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/18.3/aelig/configuring-REST-data-services.html#GUID…  for details.

          • 2. Re: ORDS 18.3 and multiple connections
            Yvan Poulin

            Hi,

            Thanks for your answer.

            Are you running ORDS in standalone mode, or inside Tomcat or Weblogic?

             

            I am running in standalone mode.

             

            Validate your ORDS database connection's configuration:

            java -jar apache/webapps/ords.war validate --database finapps

             

            oraweb@apatite:/u01/app/ords_183> java -jar ords.war validate --database finapps

            Cannot validate database configuration finapps because file finapps_pu.xml does not exist.

             

            Actually the error message is logical as I have only apex.xml files in the ords/conf folder

             

            oraweb@apatite:/u01/app/ords_183/ords/conf> ll

            total 16

            -rw-r--r-- 1 oraweb oinstall 342 Oct 12 13:29 apex_al.xml

            -rw-r--r-- 1 oraweb oinstall 345 Oct 12 13:29 apex_pu.xml

            -rw-r--r-- 1 oraweb oinstall 350 Oct 12 13:29 apex_rt.xml

            -rw-r--r-- 1 oraweb oinstall 336 Oct 12 13:29 apex.xml

             

            Then I did not add the url in the url_mappong-xml file as it already exists

             

            Any idea ?

            • 3. Re: ORDS 18.3 and multiple connections
              Billy~Verreynne

              I used finapps as an example.

               

              Validate:

              java -jar ords.war validate --database apex

               

              Validation needs to be successful.

               

              Delete url-mapping.xml, or move it as backup copy.

               

              Now add a base URL (e.g. /finapps) for your database definition:

              java -jar ords.war map-url --type base-path /finapps apex

               

              You will now have 2 URL references.

               

              Default (no explicit URL mapping needed):

              http:// <server>:<port> /ords

               

              The default direct URL reference to ORDS, implicitly uses the default and apex database definitions.

               

              Using base paths from url-mapping.xml:

              http:// <server>:<port> /ords/finapps

               

              The base path explicitly references the database definition to use for that URL path.

               

              You can now add another database definition, validate it, and add a base path URL mapping for it.

               

              PS. My posting is from a Tomcat-ORDS perspective. I do not use ORDS in standalone mode, and have not tried it either.

              • 4. Re: ORDS 18.3 and multiple connections
                Yvan Poulin

                This is not the result I expect. I must have missed something in my initial request.

                 

                With your solution (which is actually working), I am getting the APEX front end with the URL : http:// <server>:<port> /ords/finapps

                 

                What I want to achieve is that http:// <server>:<port> /ords/finapps/package.procedure gives me the same result as http:// <server>:<port> /dyn/finapps/package.procedure As I get with mod_plsql and DAD file.

                 

                http:// <server>:<port> /ords/ goes directly to my APEX front end  screen and this is fine to me.

                 

                Then all additional mapping such as /finapps, /whatever should identify with corresponding schemas in the DB and let me display HTML results of packages.procedures.

                • 5. Re: ORDS 18.3 and multiple connections
                  Billy~Verreynne

                  Ah - think I understand.

                   

                  You want to use finapps as a schema reference?

                   

                  I have only used the following approach with ORDS URL mappings. Define a database connection, e.g. foo. Using a session to that database, execute PL/SQL proc WebTest in schema SCOTT:

                  http:// <server>:<port>/ords/foo/scott.webtest

                   

                  If you want to make SCOTT part of the URL, e.g. http:// <server>:<port>/ords/foo/scott/webtest , then ORDS will process it as a REST call (https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/18.3/aelig/developing-REST-applications.html) as far as I know.

                   

                  There is not much ito of examples I found for using types regex and uri-template for the ords url-mapping command. Perhaps there is a method to make the schema as part of the base URL, and the procedure to call, the reference part?

                  • 6. Re: ORDS 18.3 and multiple connections
                    Yvan Poulin

                    I started the installation of ORDS18.3 from scratch again and once again paid attention to the messages I got.

                     

                    After initial installation, APEX is working file (http://apatite:8080/ords shows APEX default login screen)

                     

                    Then I try to add the schema FINAPPS and here is the screen result

                    oraweb@apatite:/u01/app/ords_183> java -jar ords.war setup –database FINAPPS

                    Enter the name of the database server [nephrite.xxxxx.xx]:

                    Enter the database listen port [1521]:

                    Enter 1 to specify the database service name, or 2 to specify the database SID [1]:

                    Enter the database service name [TPATCHP]:

                    Enter 1 if you want to verify/install Oracle REST Data Services schema or 2 to skip this step [1]:

                    Enter the database password for ORDS_PUBLIC_USER:

                    Confirm password:

                     

                     

                    Retrieving information.

                    Enter 1 if you want to use PL/SQL Gateway or 2 to skip this step.

                    If using Oracle Application Express or migrating from mod_plsql then you must enter 1 [1]:

                    Enter the PL/SQL Gateway database user name [APEX_PUBLIC_USER]:FINAPPS

                    Enter the database password for FINAPPS:

                    Confirm password:

                    Enter 1 to specify passwords for Application Express RESTful Services database users (APEX_LISTENER, APEX_REST_PUBLIC_USER) or 2 to skip this step [1]:

                    Enter the database password for APEX_LISTENER:

                    Confirm password:

                    Enter the database password for APEX_REST_PUBLIC_USER:

                    Confirm password:

                    Oct 15, 2018 10:16:58 AM

                    INFO: Creating Pool:|apex||

                    Oct 15, 2018 10:16:58 AM

                    INFO: Configuration properties for: |apex||

                    db.hostname=nephrite.xxxx.xx

                    db.password=******

                    db.port=1521

                    db.servicename=TPATCHP

                    db.username=FINAPPS

                    security.requestValidationFunction=wwv_flow_epg_include_modules.authorize

                    security.validationFunctionType=plsql

                     

                     

                    Oct 15, 2018 10:16:58 AM

                    WARNING: *** jdbc.MaxLimit in configuration |apex|| is using a value of 10, this setting may not be sized adequately for a production environment ***

                    Oct 15, 2018 10:16:58 AM

                    WARNING: *** jdbc.InitialLimit in configuration |apex|| is using a value of 3, this setting may not be sized adequately for a production environment ***

                    Oct 15, 2018 10:16:58 AM

                    INFO: Creating Pool:|apex|pu|

                    Oct 15, 2018 10:16:58 AM

                    INFO: Configuration properties for: |apex|pu|

                    db.hostname=nephrite.xxxx.xx

                    db.password=******

                    db.port=1521

                    db.servicename=TPATCHP

                    db.username=ORDS_PUBLIC_USER

                    security.requestValidationFunction=wwv_flow_epg_include_modules.authorize

                    security.validationFunctionType=plsql

                     

                     

                    Oct 15, 2018 10:16:58 AM

                    WARNING: *** jdbc.MaxLimit in configuration |apex|pu| is using a value of 10, this setting may not be sized adequately for a production environment ***

                    Oct 15, 2018 10:16:58 AM

                    WARNING: *** jdbc.InitialLimit in configuration |apex|pu| is using a value of 3, this setting may not be sized adequately for a production environment ***

                    Oct 15, 2018 10:16:58 AM

                    INFO: reloaded pools: [|apex||, |apex|al|, |apex|pu|, |apex|rt|]

                    Oct 15, 2018 10:16:58 AM oracle.dbtools.rt.config.setup.SchemaSetup install

                    INFO: Oracle REST Data Services schema version 18.3.0.r2701456 is installed.

                    As you can see, ORDS did not create the finapps.xml and finapp_pu.xml (etc) as it should have done.

                     

                    Where Am I doing wrong ?

                    • 7. Re: ORDS 18.3 and multiple connections
                      Billy~Verreynne

                      Unsure. Check your <configdir>/ords/conf for the finapps XML definitions. These should have been created.

                       

                      Perhaps a standalone mode issue is resulting in only apex being loaded?

                       

                      I've only use ORDS within Tomcat, and previously in Glassfish. No idea what the standalone limitations are.

                      • 8. Re: ORDS 18.3 and multiple connections
                        Yvan Poulin

                        They've not been created.

                         

                        With ORDS 18.1 they were created (in standalone mode as well) and I was able to add as many schema I wanted.  When I upgraded to 18.3 , what was created with 18.1was "transfered" to 18.3, so any previously defined schema and related mapped URL was still working.  But it was already impossible to ADD new one

                         

                        Thanks for your help