9 Replies Latest reply on Nov 6, 2015 2:09 PM by Kiran Pawar

    ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality

    colerick

      (Hope someone can help me on this, spent a good chunk of the weekend trying to get this to work.)

       

      I installed and deployed ORDS 3.0.1 using Sql Developer 4.1.1.  It's working with my APEX 4.2.5.00.08 on DB 12.1.02 deployed on Weblogic Server 12.1.3 (zip distro from otn) all running on my Windows 10 box.

       

      Now I want to add the ability to run a simple "hello world" mod_plsql procedure.  I simply can not get it to work.  Here's what I did (as best I remember):

       

      create user ken identified by somepassword;

       

      grant create session, resource to ken;

       

      create or replace procedure test

      is

      begin

      htp.p('Hello World');

      end;

      /

       

      I can run the procedure from SQL Developer and get the expected output.

       

      In SQL Dev ORDS Administration, I created a new Database Setting (apex) is already there called ken:

           JDBC: default settings

           URL Mapping: Rule_type: Request Path,   Routing Rule: /ken,   Schema for RESTful services: KEN

           ORDS_PUBLIC_USER: entered password for that user

            PL/SQL Gateway User: KEN

           APEX REST Users: left both passwords blank, ORDS documentation seems to indicate they are not necessary for non APEX usage.

       

      Uploaded configuration.

       

      Weblogic command window provides the following error message:

      INFO: Updated configurations: defaults

      Aug 10, 2015 8:29:50 AM oracle.dbtools.url.mapping.file.FileURLMappings process

      WARNING: Could not find any matching database target for: ken,KEN

      <Aug 10, 2015 8:29:50 AM EDT> <Warning> <Default> <BEA-000000> <Could not find any matching database target for: ken,KEN>

       

       

      I'm figuring something is wrong with the url-mapping.xml:

       

      <?xml version="1.0" encoding="UTF-8"?>

      <pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">

        <pool base-path="/ken" name="ken" workspace-id="KEN"/>

      </pool-config>

       

      I tried configuring it with the java command line syntax:

      SET JAVA_HOME=D:\Oracle\sqldeveloper\jdk\jre\bin

      d:\Oracle\sqldeveloper\ords>java -jar ords.war map-url  --schema-name KEN --type base-path /ken ken

       

      <Aug 10, 2015 8:56:58 AM EDT> <Error> <Default> <BEA-000000> <The pool named: ken_pu is not correctly configured, error: ORA-01017: invalid username/password; logon denied

       

      I've tried URL Path mapping http://alluvion4:7001/ords/ken   from SQL Dev

      NotFoundException [statusCode=404, reasons=[]]

      at oracle.dbtools.url.mapping.URLMappingsProvider.map(URLMappingsProvider.java:79)

      at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:55).......

       

       

      My workaround so far is to copy the ords.war, rename it and redeploy it using SQL Dev, set the PL/SQL Gateway user to my target schema ken, skip the APEX restful services configuration and deploy to weblogic.  That works but I would like it to work from a single deployment and I would like to understand the url-mapping options (what is required and what is not for this configuration).

       

      Any help is greatly appreciated.

       

      - Ken

        • 1. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
          colerick

          Is it really possible that no one else is trying to do this?

          • 2. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
            Kiran Pawar

            Hi colerick,

            colerick wrote:

             

            (Hope someone can help me on this, spent a good chunk of the weekend trying to get this to work.)

            I installed and deployed ORDS 3.0.1 using Sql Developer 4.1.1.  It's working with my APEX 4.2.5.00.08 on DB 12.1.02 deployed on Weblogic Server 12.1.3 (zip distro from otn) all running on my Windows 10 box.

            Now I want to add the ability to run a simple "hello world" mod_plsql procedure.  I simply can not get it to work.  Here's what I did (as best I remember):

            create user ken identified by somepassword;

            grant create session, resource to ken;

            create or replace procedure test

            is

            begin

            htp.p('Hello World');

            end;

            /

            I can run the procedure from SQL Developer and get the expected output.

                 I don't think the procedure test has to do something with URL Mapping in ORDS. The reason is "f" in Oracle APEX URL for e.g http://host:port/ords/f?p=100:1 is also an PL/SQL procedure with "p" as parameter.

                 You have to add the your KEN.TEST to the "Allowed Procedures" in ORDS and you can run that procedure from URL using ORDS (as in mod_plsql in OHS).

                 allowed_procs.png

            I'm figuring something is wrong with the url-mapping.xml:

            <?xml version="1.0" encoding="UTF-8"?>

            <pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">

              <pool base-path="/ken" name="ken" workspace-id="KEN"/>

            </pool-config>

            I tried configuring it with the java command line syntax:

            SET JAVA_HOME=D:\Oracle\sqldeveloper\jdk\jre\bin

            d:\Oracle\sqldeveloper\ords>java -jar ords.war map-url  --schema-name KEN --type base-path /ken ken

            <Aug 10, 2015 8:56:58 AM EDT> <Error> <Default> <BEA-000000> <The pool named: ken_pu is not correctly configured, error: ORA-01017: invalid username/password; logon denied

            I've tried URL Path mapping http://alluvion4:7001/ords/ken   from SQL Dev

            NotFoundException [statusCode=404, reasons=[]]

            at oracle.dbtools.url.mapping.URLMappingsProvider.map(URLMappingsProvider.java:79)

            at oracle.dbtools.url.mapping.filter.URLMappingFilter.doFilter(URLMappingFilter.java:55).......

            My workaround so far is to copy the ords.war, rename it and redeploy it using SQL Dev, set the PL/SQL Gateway user to my target schema ken, skip the APEX restful services configuration and deploy to weblogic.  That works but I would like it to work from a single deployment and I would like to understand the url-mapping options (what is required and what is not for this configuration).    

                 URL Mapping can be put to use while configuring multiple databases with ORDS.

                 Refer : http://docs.oracle.com/cd/E56351_01/doc.30/e56293/config.htm#CBDIAGHB

                 For e.g. the following blog shows how one ORDS configuration can be used to serve multiple Oracle APEX instances.

                 Refer : Single ORDS Install-Multiple DatabasesOracle Is Easy

             

            Regards,

            Kiran

            • 3. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
              dmdamiyo

              I'm also trying to do the same thing.

              When I configure my database using a new war file it works and I can access my plsql procedures.

               

              But when I try to configure it as a second database (the default one is to serve apex applications) using url mapping I get the same error: Could not find any matching database target for test, null

              I have test.xml in place (next to apex.xml) with the same config that works fine when I configure it separately...

              • 4. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
                colerick

                Hi Kiran,

                 

                I did not find that just adding ken.test to the allowed procedures resolved.  I get a 404 Not Found.  It seems to me that I wouldn't want that behavior anyway.  I would want a separate way to access code in the second non-apex schema.  A new "database" is the logical answer and in order for that to be referenced using the same ORDS install, a URL-MAPPING is required, right?  The referenced document for multiple APEX databases may indeed work but my second "database" connection ideally is just to a different schema within the same Oracle instance.  As user "4d977...." noted in this thread, this use case doesn't seem to work.  If I configure a new ORDS install to point to my schema to run my test procedure that works.  Adding as a second non-APEX "database" i.e. schema doesn't work.  In fact when I add the url mapping "java -jar ords.war map-url --type base-path ken ken" the following error is displayed on the standalone console (I removed the Weblogic component to simplify debugging this, so now I'm just trying to do this as a standalone):

                 

                Aug 21, 2015 10:04:52 AM oracle.dbtools.http.errors.ErrorPageFilter log

                SEVERE: null

                java.lang.NullPointerException

                        at oracle.dbtools.url.mapping.file.ApexWorkspaceTenants.apexWorkspaceTenantId(ApexWorkspaceTenants.java:79)

                        at oracle.dbtools.url.mapping.file.FileURLMappings.databaseTarget(FileURLMappings.java:70)

                        at oracle.dbtools.url.mapping.file.FileURLMappings.process(FileURLMappings.java:98)

                        at oracle.dbtools.url.mapping.file.FileURLMappings.urlMappings(FileURLMappings.java:47)

                        at oracle.dbtools.url.mapping.URLMappingsCacheImpl.load(URLMappingsCacheImpl.java:174)

                        at oracle.dbtools.url.mapping.URLMappingsCacheImpl.cached(URLMappingsCacheImpl.java:105)

                        at oracle.dbtools.url.mapping.URLMappingsProvider.map(URLMappingsProvider.java:56) .............................................

                 

                 

                - Ken

                1 person found this helpful
                • 5. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
                  colerick

                  So this is VERY INTERESTING: I downloaded ORDS 2.0.10, ran the main setup for my APEX, started in standalone and APEX worked.  Stopped standalone, created a second database to my schema with my test procedure in it, created the mapping url, ran it in standalone and GUESS WHAT!?!?!?!?!  It all works.

                   

                  3 days wasted, well maybe not if someone else benefits from this.

                   

                  This use case NEEDS to be added/regression tested and well documented in ORDS 3.  There are a lot of legacy mod_plsql apps out there that don't use APEX, this isn't a new requirement and exists in ORDS 2.  The 3.0 configuration process is confusing with all the different "users" now.  If there is a "right" way to accomplish this configuration, it needs to be better explained like "if you are trying to create a legacy mod_plsql DAD, skip this step......"

                  • 6. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
                    colerick

                    Anybody know if this is an issue with ORDS 3.0.1?

                    • 7. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
                      rwendel

                      I am having this problem right now with ORDS 3.0.1

                       

                      Works fine as default 'apex' connection string. Does not work with additional connections using url-mapping. I've tried using 'request path' and 'url path' mapping.

                       

                      AND I Have found the paper-fix solution. In the new url-mapping configuration, they allow for what appear to be different types of connections (apex, rest or generic). The switch to determine which is actually the xml attribute 'schema-name'. IF it is null, then it assumes APEX/REST. In your example, change

                       

                        <pool base-path="/ken" name="ken" workspace-id="KEN"/>

                       

                      to

                       

                        <pool base-path="/ken" name="ken" schema-name="KEN"/>

                       

                      You'll have to do this manually, and editing it with the SQLDeveloper or from the command line utility will probably overwrite it, so don't use the tool to manage it after you set it.

                       

                      colerick Give that a go and see if it works for you.

                      1 person found this helpful
                      • 8. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
                        colerick

                        Sorry this took so long to get back to.  I saw that 3.0.2 came out so I figured I'd try it with the latest and greatest.  I stripped the process down by NOT using SQL Dev or Weblogic.  The issue was in the map-url syntax.  For a simple, plain old, mod_plsql like dad, DO NOT specify the schema-name (or a workspace-id for that matter).  This is counter intuitive but the schema name is specified when you configure the PL/SQL Gateway in the "setup --database" step.

                         

                        The map-url command is then simply: java -jar ords.war map-url --type base-path /ken ken

                         

                        the resulting url-mapping.xml file then looks like this (no schema-name, not workspace-id):

                         

                        <?xml version="1.0" encoding="UTF-8"?>

                        <pool-config xmlns="http://xmlns.oracle.com/apex/pool-config">

                        <pool name="ken" base-path="/ken" updated="2015-11-06T13:51:01.591Z"/>

                        </pool-config>

                        • 9. Re: ORDS 3.0.1 on Weblogic for APEX and basic mod_plsql like functionality
                          Kiran Pawar

                          Hi colerick,

                           

                               Yes. There was a bug fix in latest ORDS 3.0.2 version.

                               With ORDS 3.0.2.294.08.40 installables the "readme.html" file is provided with the section "Changes since 3.0.1" subsection "Issues Fixed since 3.0.1" is provided which says :

                          BUG:21448390 - Fix regression where multiple database mappings configured via url-mapping.xml failed to work correctly

                           

                          Regards,

                          Kiran