Skip to Main Content
Forums

APEX

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

REST enabled SQL enabled schema not recognised

libraking123Feb 24 2021

Hi
I am attempting to build an APEX app including data from a schema (not the one the app is linked to) that has been REST enabled.
I can POST the service passing sql statements using a "curl" command on the server. I can do the same using a REST client (Insomnia).
This is an example of the URL "http://<host>/<ORDS war file>/EDW/edw_myit_service/_/sql"
Trying now to use this in APEX and have created "REST Enabled SQL" service - using the same URL and using the same credentials used in the tests above that worked. When I test I get the following:
"The URL of this remote server does not point to an REST Enabled SQL Service. Please check the details of your REST Enabled SQL Service definition."
Something I have missed? Any way to debug and see whats causing the problem?
Thanks
This post has been answered by libraking123 on Feb 25 2021
Jump to Answer

Comments

Hi,
when creating the REST Enabled SQL reference, don't add the ~/_/sql~ to the URL; so simply use
http://<host>/<ORDS war file>/EDW/edw_myit_service/
Note that for REST Enabled SQL the user name as well as the password are case sensitive; so you typically need to enter the user name in upper case.
regards
-Carsten

libraking123

Hi Carsten
Url is without the "_/sql"
Checked and double checked user. Authenticating using a user/pwd stored in Tomcat (tomcat-users.xml). However also tried the schema owner (uppercase) and the same message appears telling me the schema is not rest enabled when both curl and Insomnia work with either user/password.

Using Ords 19.4.6 and APEX 20.1

Hi,
OK - so we might need to check generic HTTP connectivity. Can you navigate to SQL Workshop > SQL Commands and execute the following:

declare
    l_clob clob;
begin
    apex_web_service.g_request_headers( 1 ).name := 'Content-Type';
    apex_web_service.g_request_headers( 1 ).value := 'application/json';


    l_clob := apex_web_service.make_rest_request( 
                  p_url         => 'http://..../EDW/edw_myit_service', 
                  p_http_method => 'POST',
                  p_username    => '...',
                  p_password    => '...',
                  p_body        => '{"statementText":"select sysdate from dual"}' );


    htp.p( l_clob );
end;

This should be the equivalent to your "curl" command. Do you see the REST Enabled SQL response JSON ...?
regards
-Carsten

libraking123
Answer

Carsten,
Error returned:
ORA-29273: HTTP request failed
ORA-06512: at "APEX_200100.WWV_FLOW_WEB_SERVICES", line 1285
ORA-06512: at "APEX_200100.WWV_FLOW_WEB_SERVICES", line 924
ORA-24247: network access denied by access control list (ACL)
Seems obvious from this error I needed to run the script to insert into ACL_PATH on that database and make sure the APEX account has 'connect' privs - thought this had been done.
image.pngThank you that solved the problem.
Craig

Marked as Answer by libraking123 · Feb 25 2021
1 - 4

Post Details

Added on Feb 24 2021
#apex, #apex-discussions, #rest, #services, #webservice
4 comments
1,714 views