REST enabled SQL enabled schema not recognised

libraking123

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

libraking123

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.

Thank you that solved the problem.

Craig

Carsten Czarski-Oracle

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

Carsten Czarski-Oracle

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

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.

Thank you that solved the problem.

Craig