6 Replies Latest reply on Aug 17, 2018 8:01 AM by Benjamin_Scheer

    ORDS URL request does not wait for query to complete [returns empty list]

    Benjamin_Scheer

      Hello,

       

      I'm having problems with a ORDS Module, which has a GET-handler.

      When the query is tested from SQL-Developer, it works fine, just takes about 5-15 seconds to complete.

       

      My Query in the Get-handler looks like this:

      select * from table(My_Database.GET_EXECUTION_PLAN(:sql_statement_id), (:database_run_on)))

      The Output is a 1-column Table with the Execution-Plan of a Query run in the past or currently runing. The Data is selected from a Function.

       

      When executed using the very same parameters as in the test in SQL-Developer, it only returns an empty list / empty array. Although the Query is supposed to take its time, the result arrives immediately when trying out the url in (e.g.) Firefox.

       

      ==> Why doesn't the Rest-API wait until the query has completed before it returns the result? How can I make it delay its response until the query has completed?

       

      Thank you in advance for your help on this topic! :-)

       

      Query result in SQL-Developer:

      2018-07-31 17_01_30-Oracle SQL Developer _ GET _db__sqlID.png

      Query-result in Firefox:

      2018-07-31 17_01_58-Mozilla Firefox.png

        • 1. Re: ORDS URL request does not wait for query to complete [returns empty list]
          thatJeffSmith-Oracle

          This doesn't make sense...we don't return anything until the statement has finished. We grab the results and transform it to JSON and return that in the response.

           

          Can you share your RESTful Service definition and your procedure?

           

          or create a simple test case that demonstrates?

          • 2. Re: ORDS URL request does not wait for query to complete [returns empty list]
            Benjamin_Scheer

            HI, thank you for your response!

            I really don't have any idea what is going wrong in this Module. All my other modules word as expected and this one works fine when tested in SQL-Developer.

             

            The user has the priviledged to access the Schema.

            I attached the definition of myREST-Service below.

             

            -- Generated by Oracle SQL Developer REST Data Services 18.1.0.095.1630

            -- Exported REST Definitions from ORDS Schema Version 3.0.11.180.12.34

            -- Schema: MY_SCHEMA_NAME Date: Thu Aug 02 09:19:19 CEST 2018

            --

            BEGIN

              ORDS.ENABLE_SCHEMA(

                  p_enabled             => TRUE,

                  p_schema              => 'MY_SCHEMA_NAME',

                  p_url_mapping_type    => 'BASE_PATH',

                  p_url_mapping_pattern => 'my_schema_name',

                  p_auto_rest_auth      => FALSE); 

             

              ORDS.DEFINE_MODULE(

                  p_module_name    => 'ExpPlan',

                  p_base_path      => '/ExpPlan/',

                  p_items_per_page =>  500,

                  p_status         => 'PUBLISHED',

                  p_comments       => NULL);   

              ORDS.DEFINE_TEMPLATE(

                  p_module_name    => 'ExpPlan',

                  p_pattern        => ':db/:sqlID',

                  p_priority       => 9,

                  p_etag_type      => 'HASH',

                  p_etag_query     => NULL,

                  p_comments       => NULL);

              ORDS.DEFINE_HANDLER(

                  p_module_name    => 'ExpPlan',

                  p_pattern        => ':db/:sqlID',

                  p_method         => 'GET',

                  p_source_type    => 'json/collection',

                  p_items_per_page =>  500,

                  p_mimes_allowed  => '',

                  p_comments       => NULL,

                  p_source         =>

            'select PLAN_TABLE_OUTPUT from table(MY_SCHEMA_TWO.GET_EXP_PLAN_DATA((:sqlId), (:db)))'

                  );

             

             

              COMMIT;

            END;

            • 3. Re: ORDS URL request does not wait for query to complete [returns empty list]
              thatJeffSmith-Oracle

              Can you show or describe what (MY_SCHEMA_TWO.GET_EXP_PLAN_DATA) does?

               

              And not that it matters terribly, but why is the priority set to 9?

              • 4. Re: ORDS URL request does not wait for query to complete [returns empty list]
                3756397

                Hello Jeff,

                 

                Thanks for looking into our case. Benjamin and I work together on that and I will try to explain a bit more below.

                 

                GET_EXP_PLAN_DATA function sets the SQL ID on a remote database in order to get its execution plan.

                BEGIN

                    dbms_application_info.set_client_info@'||db||'('''||sql_id||''');

                END; 

                 

                SELECT schema1.exp_plan_obj(plan_table_output)

                    FROM  (

                               SELECT plan_table_output

                               FROM schema2.QUERY_EXPLAIN_PLAN@'||db||'

                               );

                 

                Then QUERY_EXPLAIN_PLAN view is called with the SQL ID as a parameter:

                CREATE OR REPLACE FORCE VIEW "schema2"."QUERY_EXPLAIN_PLAN" ("PLAN_TABLE_OUTPUT") AS

                  select "PLAN_TABLE_OUTPUT" from table(schema2.GET_EXP_PLAN(sys_context('userenv', 'client_info')));

                 

                Finally, GET_EXP_PLAN function returns the actual data using the client_info / SQL ID:

                SELECT schema2.exp_plan_obj(plan_table_output)

                FROM  (

                                SELECT plan_table_output

                                FROM table(DBMS_XPLAN.DISPLAY_AWR('''|| sql_id ||'''))

                              )

                 

                If I am not wrong, one of the reasons I did it this way was that I wasn't able to query directly system views via DB link.

                 

                My guess is that  dbms_application_info.set_client_info is not working. However, I am not able to verify that. I cannot find the relevant session in gv$session nor the sql_id. How does the ORDS executes the queries and where can we see the details?

                 

                Thanks!

                • 5. Re: ORDS URL request does not wait for query to complete [returns empty list]
                  thatJeffSmith-Oracle

                  It will execute as MY_SCHEMA_NAME and module is set of ORDS, so you can see what's happening in the V$ views or a session trace.

                  • 6. Re: ORDS URL request does not wait for query to complete [returns empty list]
                    Benjamin_Scheer

                    Hello,

                    my collegue got it working by completely recreating a similar service (without copy-pasting from the not working one...).

                     

                    When closely comparing both REST-definitions, it turned out to be a little and unnoticed typo (upper/ lower case) in the template / query.

                    So, capitalization does not matter when testing a service within SQL-developer, but it does of course when passing through the url.

                    Although this was of course my fault, I think an automated check for this misstake in SQL-developer would be great.

                     

                    Thank you very much for your time and help!

                     

                    Best regards,

                    Benjamin

                    2018-08-17 09_49_12-Window.png