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

ORDS 18.3 and multiple connections

Yvan Poulin Oct 15, 2018 8:26 AM

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 Oct 15, 2018 4:57 AM (in response to Yvan Poulin)

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.
Like Show 0 Likes(0)

Actions
2. Re: ORDS 18.3 and multiple connections

Yvan Poulin Oct 15, 2018 6:39 AM (in response to Billy~Verreynne)

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 ?
Like Show 0 Likes(0)

Actions
3. Re: ORDS 18.3 and multiple connections

Billy~Verreynne Oct 15, 2018 7:01 AM (in response to Yvan Poulin)

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.
Like Show 0 Likes(0)

Actions
4. Re: ORDS 18.3 and multiple connections

Yvan Poulin Oct 15, 2018 7:28 AM (in response to Billy~Verreynne)

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.
Like Show 0 Likes(0)

Actions
5. Re: ORDS 18.3 and multiple connections

Billy~Verreynne Oct 15, 2018 8:16 AM (in response to Yvan Poulin)

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?
Like Show 0 Likes(0)

Actions
6. Re: ORDS 18.3 and multiple connections

Yvan Poulin Oct 15, 2018 8:25 AM (in response to 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 ?
Like Show 0 Likes(0)

Actions
7. Re: ORDS 18.3 and multiple connections

Billy~Verreynne Oct 15, 2018 8:42 AM (in response to Yvan Poulin)

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.
Like Show 0 Likes(0)

Actions
8. Re: ORDS 18.3 and multiple connections

Yvan Poulin Oct 15, 2018 9:03 AM (in response to Billy~Verreynne)

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
Like Show 0 Likes(0)

Actions

Go to original post