Forum Stats

  • 3,854,986 Users
  • 2,264,446 Discussions
  • 7,905,852 Comments

Discussions

Categories

Problem requesting PL/SQL web page through ORDS

User_0ZK6U
User_0ZK6U Member Posts: 11 Green Ribbon
in ORDS, SODA & JSON in the Database

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;

/

Tagged:
· Share on TwitterShare on Facebook

Answers

  • Suresh Sangaran-Oracle
    Suresh Sangaran-Oracle Member, Moderator Posts: 334 Employee

    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

    · Share on TwitterShare on Facebook
  • User_0ZK6U
    User_0ZK6U Member Posts: 11 Green Ribbon

    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;

    · Share on TwitterShare on Facebook
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,774 Employee

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

    · Share on TwitterShare on Facebook
  • User_0ZK6U
    User_0ZK6U Member Posts: 11 Green Ribbon

    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.

    · Share on TwitterShare on Facebook
  • User_0ZK6U
    User_0ZK6U Member Posts: 11 Green Ribbon
    edited Oct 13, 2021 5:42PM

    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.

    · Share on TwitterShare on Facebook
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,774 Employee

    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?

    · Share on TwitterShare on Facebook
  • User_0ZK6U
    User_0ZK6U Member Posts: 11 Green Ribbon

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

    · Share on TwitterShare on Facebook
  • thatJeffSmith-Oracle
    thatJeffSmith-Oracle Distinguished Product Manager Posts: 8,774 Employee

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

    · Share on TwitterShare on Facebook
  • User_0ZK6U
    User_0ZK6U Member Posts: 11 Green Ribbon

    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                                                                              

    · Share on TwitterShare on Facebook