Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 110 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
Problem requesting PL/SQL web page through ORDS

I have a PL/SQL web application that I am trying to migrate to ORDS. I have setup Oracle Database 18 XE, APEX 20.2, and ORDS 20.3, with Tomcat 8.5.
I have a public procedure, shown below, in a database package that I want to have run when I go to localhost:8080/ords/gator/gator$session.Hello, but currently I am getting a 400 Page Not Found error. The package compiles without errors and ORDS is running on Tomcat. What have I not setup properly?
----------------
PL/SQL Procedure (within package gator$session)
----------------
PROCEDURE Hello IS
BEGIN
htp.init;
htp.p('GATOR says Hello.');
END;
------------------------------
Setup steps with SYS as SYSDBA
------------------------------
alter session set container=XEPDB1;
create user GATOR identified by gator
default tablespace users quota unlimited on users;
alter user GATOR grant connect through ORDS_PUBLIC_USER;
grant create session to GATOR;
grant create sequence to GATOR;
grant create public synonym to GATOR;
grant create database link to GATOR;
grant create synonym to GATOR;
grant create trigger to GATOR;
grant create table to GATOR;
grant create role to GATOR;
grant create procedure to GATOR;
grant create materialized view to GATOR;
grant alter session to GATOR;
grant create view to GATOR;
grant create any DIRECTORY to GATOR;
grant drop any directory to GATOR;
-----------------------------------
Setup steps with gator/[email protected]
-----------------------------------
BEGIN
ords.enable_schema(
p_enabled => TRUE,
p_schema => 'GATOR',
p_url_mapping_type => 'BASE_PATH',
p_url_mapping_pattern => 'gator',
p_auto_rest_auth => FALSE
);
COMMIT;
END;
/
Answers
-
If AUTOREST is enabled on "gator$session", not sure how the handler is created.
Try creating handlers manually call the PROC over rest.
Ref below documents:
Thakns, Suresh
-
Thanks for the info. Nothing really jumped out at me other than than maybe needing to enable the database package, which I did using ords.enabled_object with the p_object_type as 'PACKAGE'. But no improvement.
Is there a way to tell if ORDS is actually recognizing that /ords/gator/ is associated with enabled schema? I can see the url requested being logged by Tomcat but not what if anything ORDS is doing.
is the command below giving ORDS what it needs to see the GATOR database or is something else needed:
alter user GATOR grant connect through ORDS_PUBLIC_USER;
-
there's the plsql gateway - no ords_public_user
and there's autorest - enable schema, enable plsql, call it - via ords_public_user
Pick one - the REST side is the modern way to go...
-
I tried following the steps at oss.oracle.com/projects/mod_owa/dist/documentation/ords_setup.htm looking for steps I might have missed. I did seem to miss:
java -jar ords.war map-url --type base-path /gator gator
I tried running ORDS in standalone mode with debugging enabled. When I get the 404 No Found error nothing shows up on the console but I do get a DispatcherNotFoundException stack strace in my browser window, which doesn't help me.
Is there a guide somewhere that lays out the specific steps needed to get a PL/SQL Web Application that uses the PL/SQL Web Toolkit working with ORDS? I am not needing to call PL/SQL as web services - I just want to render web pages.
-
I went into the gator.xml settings in the ords\conf folder and changed the db.port from 8080 to 1521. My Tomcat is at 8080, but ORDS is at 1521.
I still get a 404 No Found error, but with a ProcedureNotFoundException saying the procedure named gator$session.Hello could not be accessed. The package/procedure is in the XEPDB1/GATOR schema. Do I need to grant execute privilege to some ORDS or APEX account? I tried granting execute to the package to both ORDS_PUBLIC_USER and APEX_PUBLIC_USER.
The exception is coming from oracle.dbtools.apex.ModApex.validateRequest.
-
I went into the gator.xml settings in the ords\conf folder and changed the db.port from 8080 to 1521. My Tomcat is at 8080, but ORDS is at 1521.
Ok...so in the tomcat logs when ords starts are the pools able to be created? Do you see connections in our database that match the connection pool users?
-
I am running ORDS standalone for now. Tomcat is not running.
-
so same question then, ords logs/STDOUT, what do you see when it starts, and what do you see in your database?
-
Standalone ORDS console output
2021-10-13T18:32:40.950Z INFO HTTP and HTTP/2 cleartext listening on host: localhost port: 8080
2021-10-13T18:32:41.028Z INFO Disabling document root because the specified folder does not exist: c:\Temp\ords\ords\standalone\doc_root
2021-10-13T18:32:46.082Z INFO Configuration properties for: |apex||
database.api.enabled=true
db.connectionType=basic
db.hostname=localhost
db.port=1521
db.servicename=XEPDB1
debug.debugger=true
debug.printDebugToScreen=true
feature.sdw=true
restEnabledSql.active=true
security.requestValidationFunction=wwv_flow_epg_include_modules.authorize
security.validationFunctionType=plsql
db.password=******
db.username=APEX_PUBLIC_USER
resource.templates.enabled=true
2021-10-13T18:32:46.098Z WARNING *** jdbc.MaxLimit in configuration |apex|| is using a value of 10, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:46.099Z WARNING *** jdbc.InitialLimit in configuration |apex|| is using a value of 3, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:47.650Z INFO Configuration properties for: |apex|al|
database.api.enabled=true
db.connectionType=basic
db.hostname=localhost
db.port=1521
db.servicename=XEPDB1
debug.debugger=true
debug.printDebugToScreen=true
feature.sdw=true
restEnabledSql.active=true
security.requestValidationFunction=wwv_flow_epg_include_modules.authorize
security.validationFunctionType=plsql
db.password=******
db.username=APEX_LISTENER
resource.templates.enabled=true
2021-10-13T18:32:47.652Z WARNING *** jdbc.MaxLimit in configuration |apex|al| is using a value of 10, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:47.654Z WARNING *** jdbc.InitialLimit in configuration |apex|al| is using a value of 3, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:48.113Z INFO Configuration properties for: |apex|pu|
database.api.enabled=true
db.connectionType=basic
db.hostname=localhost
db.port=1521
db.servicename=XEPDB1
debug.debugger=true
debug.printDebugToScreen=true
feature.sdw=true
restEnabledSql.active=true
security.requestValidationFunction=wwv_flow_epg_include_modules.authorize
security.validationFunctionType=plsql
db.password=******
db.username=ORDS_PUBLIC_USER
resource.templates.enabled=true
2021-10-13T18:32:48.116Z WARNING *** jdbc.MaxLimit in configuration |apex|pu| is using a value of 10, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:48.116Z WARNING *** jdbc.InitialLimit in configuration |apex|pu| is using a value of 3, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:48.467Z INFO Configuration properties for: |apex|rt|
database.api.enabled=true
db.connectionType=basic
db.hostname=localhost
db.port=1521
db.servicename=XEPDB1
debug.debugger=true
debug.printDebugToScreen=true
feature.sdw=true
restEnabledSql.active=true
security.requestValidationFunction=wwv_flow_epg_include_modules.authorize
security.validationFunctionType=plsql
db.password=******
db.username=APEX_REST_PUBLIC_USER
resource.templates.enabled=true
2021-10-13T18:32:48.468Z WARNING *** jdbc.MaxLimit in configuration |apex|rt| is using a value of 10, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:48.468Z WARNING *** jdbc.InitialLimit in configuration |apex|rt| is using a value of 3, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:49.048Z INFO Configuration properties for: |gator||
database.api.enabled=true
db.connectionType=basic
db.hostname=localhost
db.port=1521
db.servicename=XEPDB1
debug.debugger=true
debug.printDebugToScreen=true
feature.sdw=true
restEnabledSql.active=true
security.requestValidationFunction=wwv_flow_epg_include_modules.authorize
security.validationFunctionType=plsql
db.password=******
db.username=APEX_PUBLIC_USER
resource.templates.enabled=true
2021-10-13T18:32:49.049Z WARNING *** jdbc.MaxLimit in configuration |gator|| is using a value of 10, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:49.050Z WARNING *** jdbc.InitialLimit in configuration |gator|| is using a value of 3, this setting may not be sized adequately for a production environment ***
2021-10-13T18:32:55.626Z INFO Oracle REST Data Services initialized
Oracle REST Data Services version : 20.3.0.r3011819
Oracle REST Data Services server info: jetty/9.4.30.v20200611
After startup I see the following connections in XEDPB1:
SQL> select sid, username from v$session where username is not null;
SID USERNAME
--- --------------------------
15 APEX_PUBLIC_USER
30 ORDS_PUBLIC_USER
32 APEX_PUBLIC_USER
154 APEX_PUBLIC_USER
155 ORDS_PUBLIC_USER
157 APEX_PUBLIC_USER
158 APEX_REST_PUBLIC_USER
159 APEX_PUBLIC_USER
277 APEX_LISTENER
278 ORDS_PUBLIC_USER
279 APEX_REST_PUBLIC_USER
401 APEX_LISTENER
402 APEX_REST_PUBLIC_USER
404 APEX_LISTENER
406 SYS
407 APEX_PUBLIC_USER