4 Replies Latest reply on Jul 22, 2020 5:03 PM by Sasha Gomanuke

    How to pass sql string into Handler of Collection Query

    Sasha Gomanuke

      Hi, guys.

       

      I'm just investigating a possibilities of ORDS to plan a development.

      Look, It's not a problem to pass sql-string into Handler with source type PL/SQL and get back a result of executed query by means of htp.p.

      Can i pass sql-string into Handler with type Collection Query to avoid PL/SQL?

       

      Trying to use bind variable as sql in Collection Query leads to error:

      The request could not be processed because an error occurred whilst attempting to evaluate the SQL statement associated with this resource. Please check the SQL statement is correctly formed and executes without error. SQL Error Code: 17439, Error Message: Invalide type SQL: sqlKind = UNINITIALIZED

       

      Appreciate You.

        • 1. Re: How to pass sql string into Handler of Collection Query
          EJ-Egyed

          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

            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

              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

                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;