How to pass sql string into Handler of Collecti...

1. Re: How to pass sql string into Handler of Collection Query

EJ-Egyed Jul 22, 2020 12:41 PM (in response to Sasha Gomanuke)

Bind variables are passed the same no matter the source type of the handler. Here is an example of using different types of parameters with a collection feed and their responses:

BEGIN
    ORDS.ENABLE_SCHEMA (p_enabled               => TRUE,
                        p_url_mapping_type      => 'BASE_PATH',
                        p_url_mapping_pattern   => 'api-test',
                        p_auto_rest_auth        => FALSE);

    ORDS.DEFINE_MODULE (p_module_name      => 'v1',
                        p_base_path        => '/v1/',
                        p_items_per_page   => 25,
                        p_status           => 'PUBLISHED',
                        p_comments         => NULL);
    ORDS.DEFINE_TEMPLATE (p_module_name   => 'v1',
                          p_pattern       => 'demo/:route_value',
                          p_priority      => 0,
                          p_etag_type     => 'HASH',
                          p_etag_query    => NULL,
                          p_comments      => NULL);
    ORDS.DEFINE_HANDLER (
        p_module_name      => 'v1',
        p_pattern          => 'demo/:route_value',
        p_method           => 'GET',
        p_source_type      => ords.source_type_collection_feed,
        p_items_per_page   => 0,
        p_mimes_allowed    => '',
        p_comments         => NULL,
        p_source           =>
            'select :route_value as route_value, :header_value as header_value from dual connect by level <= nvl(:rows,1)');
    ords.define_parameter (p_module_name          => 'v1',
                           p_pattern              => 'demo/:route_value',
                           p_method               => 'GET',
                           p_name                 => 'Header-Value',
                           p_bind_variable_name   => 'header_value');
    COMMIT;
END;

curl http://localhost/ords/api-test/v1/demo/some_string

{"items":[{"route_value":"some_string","header_value":null}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self","href":"http://localhost/ords/api-test/v1/demo/some_string"},{"rel":"describedby","href":"http://localhost/ords/api-test/metadata-catalog/v1/demo/item"}]}

curl http://localhost/ords/api-test/v1/demo/some%20string%20with%20spaces

{"items":[{"route_value":"some string with spaces","header_value":null}],"hasMore":false,"limit":0,"offset":0,"count":1,"links":[{"rel":"self","href":"http://localhost/ords/api-test/v1/demo/some%20string%20with%20spaces"},{"rel":"describedby","href":"http://localhost/ords/api-test/metadata-catalog/v1/demo/item"}]}

curl http://localhost/ords/api-test/v1/demo/some%20string%20with%20spaces?rows=4

{"items":[{"route_value":"some string with spaces","header_value":null},{"route_value":"some string with spaces","header_value":null},{"route_value":"some string with spaces","header_value":null},{"route_value":"some string with spaces","header_value":null}],"hasMore":false,"limit":0,"offset":0,"count":4,"links":[{"rel":"self","href":"http://localhost/ords/api-test/v1/demo/some%20string%20with%20spaces?rows=4"},{"rel":"describedby","href":"http://localhost/ords/api-test/metadata-catalog/v1/demo/item"}]}

curl -H "Header-Value: some header value" http://localhost/ords/api-test/v1/demo/some%20string%20with%20spaces?rows=4

{"items":[{"route_value":"some string with spaces","header_value":"some header value"},{"route_value":"some string with spaces","header_value":"some header value"},{"route_value":"some string with spaces","header_value":"some header value"},{"route_value":"some string with spaces","header_value":"some header value"}],"hasMore":false,"limit":0,"offset":0,"count":4,"links":[{"rel":"self","href":"http://localhost/ords/api-test/v1/demo/some%20string%20with%20spaces?rows=4"},{"rel":"describedby","href":"http://localhost/ords/api-test/metadata-catalog/v1/demo/item"}]}

If you are still receiving errors, it is likely a problem with the code in your handler so you should share that if you are still getting errors.

1 person found this helpful

2. Re: How to pass sql string into Handler of Collection Query

Sasha Gomanuke Jul 22, 2020 1:56 PM (in response to EJ-Egyed)

I do appreciate You for Your excellent answer! Here You are my case:

I'd like to send as parameter p_sql whole sql string itself, so p_source => ':p_sql'

begin

ORDS.ENABLE_SCHEMA (p_enabled => TRUE,

p_url_mapping_type => 'BASE_PATH',

p_url_mapping_pattern => 'api-test',

p_auto_rest_auth => FALSE);

ORDS.DEFINE_MODULE (p_module_name => 'v1',

p_base_path => '/v1/',

p_items_per_page => 25,

p_status => 'PUBLISHED',

p_comments => NULL);

ORDS.DEFINE_HANDLER (

p_module_name => 'v1',

p_pattern => 'demo2',

p_method => 'GET',

p_source_type => ords.source_type_collection_feed,

p_items_per_page => 0,

p_mimes_allowed => '',

p_comments => NULL,

p_source => ':p_sql');

ords.define_parameter (p_module_name => 'v1',

p_pattern => 'demo2',

p_method => 'GET',

p_name => 'SQL',

p_bind_variable_name => 'p_sql');

commit;

end;

3. Re: How to pass sql string into Handler of Collection Query

EJ-Egyed Jul 22, 2020 2:43 PM (in response to Sasha Gomanuke)

I do not think you will be able to execute dynamic SQL like that. Your best option is to use the REST Enabled SQL Service described here: https://docs.oracle.com/en/database/oracle/oracle-rest-data-services/20.2/aelig/rest-enabled-sql-service.html#GUID-BA9F9…

Using that, the users of the endpoint will be able to run any query that they would like (that your ORDS enabled schema has privileges to query)

1 person found this helpful

4. Re: How to pass sql string into Handler of Collection Query

Sasha Gomanuke Jul 22, 2020 5:03 PM (in response to EJ-Egyed)

I see. Well, i solved that task by means of common REST solution as handler with PL/SQL. And great Thnak You for Your participance!

declare
    l_result clob;
    l_cursor SYS_REFCURSOR;
BEGIN
    OPEN l_cursor FOR :p_sql;

    APEX_JSON.initialize_clob_output;
    APEX_JSON.open_object;
    APEX_JSON.write ('result', l_cursor);
    APEX_JSON.close_object;
    l_result := APEX_JSON.get_clob_output;
    APEX_JSON.free_output;

    while length(l_result)>0 loop
     sys.htp.print(substr(l_result,1,16383));
     l_result := substr(l_result,16384);
    end loop;

    exception when others then sys.htp.print('{"result":[{"error":"'||replace(SQLERRM,'"', '''')|| '"}]}');
END;