Problem requesting PL/SQL web page through ORDS

User_0ZK6U

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;

/

Suresh Sangaran-Oracle

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:

https://oracle-base.com/articles/misc/oracle-rest-data-services-ords-create-basic-rest-web-services-using-plsql

https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/21.1/aelig/developing-REST-applications.html#GUID-4CE630AA-2F06-41D9-96F6-DA77AB1E6395

https://www.thatjeffsmith.com/archive/2019/03/calling-a-pl-sql-function-via-ords/

Thakns, Suresh

User_0ZK6U

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;

thatJeffSmith-Oracle

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...

User_0ZK6U

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.

User_0ZK6U

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.

thatJeffSmith-Oracle

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?

User_0ZK6U

I am running ORDS standalone for now. Tomcat is not running.

thatJeffSmith-Oracle

so same question then, ords logs/STDOUT, what do you see when it starts, and what do you see in your database?

User_0ZK6U

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