Building a REST API for Oracle’s DBMS_STATS Package

Earlier this week, SQLMaria published a blog post showing how to create a report comparing the current optimizer stats on a table vs any PENDING statistics (Docs.)

I offered Maria a write-up to do the same work via an Oracle Database REST API. She said ‘yes!’, and this is that post 🙂

The REST API

Things of Interest

• I’ve put the schema, table name, and threshold into the URI Template
• The GET Handler Source Type is ‘resource’/lob’
• I’ve added a mime-type to the Query so your browser can handle it appropriately
• I’ve hardcoded the time_stamp value to fit MY needs

Here’s what I mean about putting the parameters into the URI Template. This means I don’t need HTTP Request Headers or a Request Body to deliver the information to the REST API, it’s all contained in the Request URI itself.

And our REST API media source types are expecting the mime time followed by the LOB, so in this case it’s ‘text/plain’ followed by the CLOB coming back from the function call.

Calling the API

Before doing a DIFF report, I need to collect some stats without publishing them, so I’ll do what Maria did, just with one of my tables.

I’ve chosen to do that with SQL, but there’s no reason why I couldn’t have defined REST APIs for GATHER_TABLE_STATS and SET_TABLE_PREFS as well.

Maybe if Maria reads this far into my post, she’ll take that as a challenge to try for herself 🙂

Since my API is a GET returning text, no reason we can’t do that in the browser.

The Code

Here’s my module. In my environment, for some reason the TIMESTAMP parameter was actually TIME_STAMP, so adjust as necessary. Remember you can always read the Package Specs in SQL Developer for these sorts of details vs googling the Database Docs.

-- Generated by ORDS REST Data Services 22.4.4.r0411526
-- Schema: ADMIN  Date: Fri Mar 24 04:56:09 2023 
--

BEGIN
  ORDS.ENABLE_SCHEMA(
      p_enabled             => TRUE,
      p_schema              => 'ADMIN',
      p_url_mapping_type    => 'BASE_PATH',
      p_url_mapping_pattern => 'admin',
      p_auto_rest_auth      => FALSE);
    
  ORDS.DEFINE_MODULE(
      p_module_name    => 'maria',
      p_base_path      => '/maria/',
      p_items_per_page => 25,
      p_status         => 'PUBLISHED',
      p_comments       => 'REST APIs inspired by SQLMaria blog posts');

  ORDS.DEFINE_TEMPLATE(
      p_module_name    => 'maria',
      p_pattern        => 'stats/diff/:user/:tab/:threshold',
      p_priority       => 0,
      p_etag_type      => 'HASH',
      p_etag_query     => NULL,
      p_comments       => 'interface for dbms_stats.diff_table_stats_in_pending');

  ORDS.DEFINE_HANDLER(
      p_module_name    => 'maria',
      p_pattern        => 'stats/diff/:user/:tab/:threshold',
      p_method         => 'GET',
      p_source_type    => 'resource/lob',
      p_mimes_allowed  => NULL,
      p_comments       => NULL,
      p_source         => 
'SELECT ''text/plain'', report
  FROM TABLE
         (dbms_stats.diff_table_stats_in_pending(
                  ownname  => :user,
                  tabname  => :tab,
                time_stamp => SYSTIMESTAMP AT TIME ZONE ''US/Eastern'',
             pctthreshold  => :threshold))');
  
        
COMMIT;

END;