Forum Stats

  • 3,751,483 Users
  • 2,250,366 Discussions
  • 7,867,436 Comments

Discussions

REST enabled SQL enabled schema not recognised

libraking123
libraking123 Member Posts: 10 Blue Ribbon

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

Best Answer

  • libraking123
    libraking123 Member Posts: 10 Blue Ribbon
    Accepted 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.

    Thank you that solved the problem.

    Craig

Answers

  • Carsten Czarski-Oracle
    Carsten Czarski-Oracle Member Posts: 1,221 Employee

    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
    libraking123 Member Posts: 10 Blue Ribbon

    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
    Carsten Czarski-Oracle Member Posts: 1,221 Employee

    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
    libraking123 Member Posts: 10 Blue Ribbon
    Accepted 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.

    Thank you that solved the problem.

    Craig