ChatGPT & Oracle Development

This blog is part of a series about aspects and features of Oracle 26 and Autonomous Transaction Processing Database.

TL;DR

This is an opinion piece about the impact of AI on developers and administrators.  I'll tell you my opinion here at the start:  

AI won't be replacing us, at least not yet, but we may be replaced by someone who is more productive because they are using AI!  I certainly advocate using it, but do so thoughtfully.  Consider whether the answers are sensible, and then test them carefully.

Introduction

To learn more about Oracle 26ai Autonomous Database, I returned to a project I created in 2021 to explore spatial data.  I had exported my activity data from Strava as flat files and then imported them into an Oracle database.  

Now, I have migrated that project to an Autonomous database on OCI and integrated it directly with Strava through their API.  Notifications of new activities are received via a REST service, some processing is done in the Oracle database, and results are written back to the Strava activity description.  All quite simple, but it made me use techniques and technologies that I have never used before.

ChatGPT

When I created the original project in 2021, I had the Oracle documentation and Google.  I had to design and write every bit of code myself.  It all took time.

Now, I have been able to use ChatGPT (other AI Chatbots are available, but this is where I started), and the effect has been remarkable.  I have been pointed at features and techniques that are new to me, and often I have been given a concrete example to start work on, and therefore I have learned about them.

I asked ChatGPT questions in plain language about the details of both Oracle 26 and the Strava API, and it gave me sensible answers in plain language that were generally sensible.  In some cases, it designed complete processing flows; sometimes it just illustrated the answer with code examples.  I could ask follow-up questions, and it would answer them in the context of the earlier question, refining its response.  It became a genuine conversation.  Though it is not going to pass the Turing Test!

ChatGPT's answers were mostly accurate, though some of its generated code was not always completely correct.  On some subjects, such as character set, we went round in circles.  Sometimes, I would point out mistakes, and it would say 'Yes, you are right!' or 'Well spotted!'.  I am not convinced it learnt anything from that.  Over time, I learnt that I needed to ask quite precise questions, otherwise it would go off in other directions.  Nevertheless, I found I got very quickly from a first draft of code to debugging almost working code.  I have no doubt that using ChatGPT increased my productivity.  If I had to quantify the effect, I would estimate that it improved my productivity by a factor of about 3.

These are some of my early questions to ChatGPT:

• "How can Strava notify my Oracle database, using only PL/SQL, that an activity has been added, deleted or updated?" 
• The result included a complete design for creating a Strava webhook to send an HTTP message to a REST service, including a database data model design and how to process it by calling the Strava API to extract the activity data

• "How would I load GeoJSON … into an Oracle spatial data object geometry in an Oracle autonomous database using just PL/SQL"
• I got a complete PL/SQL procedure to extract the GeoJSON from the data.gov.ie website, and then how to read the GeoJSON into an Oracle spatial geometry.
• I was able to ask follow-up questions.   When one particular public data set produced errors from sdo_util.from_geojson, after a few other suggestions, ChatGPT provided a complete alternative PL/SQL procedure to create a spatial geometry from just the array of coordinates.  It is slower, but it works reliably.  I use it as an alternative when I get an error from the Oracle function.

There were some notable examples of code that ChatGPT produced correctly the first time, and much faster than I could have.  In particular, extracting all the data in a Strava activity (see strava_http.get_activity_stream) as both an Oracle spatial geometry and a GPX file, including heart monitor, cadence and power meter data if also present (that must conform to the Topographix and Garmin XML schemas).  My code is on GitHub, so you can judge the result for yourself!

Nullius in Verba

This motto (it can be translated as "Take Nobody's Word for It!") is at the heart of the scientific principle.  It can usefully be applied to many things, and certainly to ChatGPT.  

ChatGPT is a hugely powerful tool that seems to be capable of answering any reasonable query.  I would encourage anyone to use it to help develop code faster.  However, every response should be treated with healthy scepticism and be tested carefully.   Whether code compiles and executes is a straightforward question with an essentially binary answer.  Whether that code then does what it is supposed to do requires thorough testing, but then so does human-written code!

Nonetheless, I am hugely impressed by ChatGPT.  I have no doubt that I got further and got there much faster than I ever would otherwise! 

Job Classes on Autonomous Database

This blog is part of a series about aspects and features of Oracle 26 and Autonomous Transaction Processing Database.

I have written about using Job Classes with the database scheduler.  It is essentially the same on Autonomous database, but some configuration is delivered by Oracle.  You may choose to use it directly as delivered.  However, I suggest using it as the basis for a custom configuration.

The Autonomous Transaction Processing (ATP) database is delivered with 5 consumer groups and 5 corresponding job classes that map to them.  

OWNER JOB_CLASS_NAME RESOURCE_CONSUMER_GROUP SERVICE
----- -------------- ----------------------- ------------------------------------------------------
LOGGING_LEVEL LOG_HISTORY COMMENTS                                
------------- ----------- ----------------------------------------
SYS    TPURGENT      TPURGENT                GE***********09_GOFASTER1_tpurgent.adb.oraclecloud.com 
RUNS                      Urgent transaction processing jobs     

SYS    TP            TP                      GE***********09_GOFASTER1_tp.adb.oraclecloud.com     
RUNS                      Transaction processing jobs            

SYS    HIGH          HIGH                    GE***********09_GOFASTER1_high.adb.oraclecloud.com   
RUNS                      High priority jobs                     

SYS    MEDIUM        MEDIUM                  GE***********09_GOFASTER1_medium.adb.oraclecloud.com 
RUNS                      Medium priority jobs                   

SYS    LOW           LOW                     GE***********09_GOFASTER1_low.adb.oraclecloud.com    
RUNS                      Low priority jobs                      

It is easy and perfectly reasonable to allocate these delivered job classes to scheduler jobs.  However, these job classes cannot be changed, even by the ADMIN user.

BEGIN dbms_Scheduler.set_attribute('SYS.TPURGENT', 'comments', 'A Comment'); END;
*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_ISCHED", line 3513
ORA-06512: at "SYS.DBMS_SCHEDULER", line 3460
ORA-06512: at line 1

Note that the service names are different and unique to every autonomous database.  I have been careful to avoid hard-coding this anywhere within my scripts and code.  Instead, I duplicate the delivered job classes and then alter as necessary.  Thus, each job or group of jobs has its own job class.  I prefer to manage the job and the job scheduler, as far as possible, within a packaged procedure.  This has several advantages.

• The right version is always available because it has been installed into the database and can be migrated like any other version-controlled source code.  This also covers when a database has been cloned, restored or flashed back.  This saves looking for the right version of the right script.  
• Jobs can be created and managed by a user who does not have access to manage the job scheduler, but who can execute procedures in the package.
• I have created a procedure to clone a job class and adjust attributes as required.
• The code is available on GitHub.

CREATE OR REPLACE PACKAGE BODY strava.strava_job AS
...
e_job_already_exists EXCEPTION;
PRAGMA EXCEPTION_INIT(e_job_already_exists,-27477);
...
PROCEDURE create_job_class
(p_job_class_name          all_scheduler_job_classes.job_class_name%TYPE
,p_based_on_job_class      all_scheduler_job_classes.job_class_name%TYPE
,p_resource_consumer_group all_scheduler_job_classes.resource_consumer_group%TYPE DEFAULT NULL
,p_service                 all_scheduler_job_classes.service%TYPE                 DEFAULT NULL
,p_logging_level           all_scheduler_job_classes.logging_level%TYPE           DEFAULT NULL
,p_log_history             all_scheduler_job_classes.log_history%TYPE             DEFAULT NULL
,p_comments                all_scheduler_job_classes.comments%TYPE                DEFAULT NULL)
IS
  r_job_class all_scheduler_job_classes%ROWTYPE;
...
BEGIN
...  
  SELECT * INTO r_job_class FROM all_scheduler_job_classes
  WHERE owner = 'SYS' AND job_class_name = p_based_on_job_class;
  
  BEGIN
    DBMS_SCHEDULER.CREATE_JOB_CLASS(p_job_class_name); 
  EXCEPTION WHEN e_job_already_exists THEN NULL;
  END;
  
  IF p_resource_consumer_group IS NOT NULL THEN r_job_class.resource_consumer_group := p_resource_consumer_group; END IF;
  IF p_service                 IS NOT NULL THEN r_job_class.service := p_service; END IF;
  IF p_logging_level           IS NOT NULL THEN r_job_class.logging_level := p_logging_level; END IF;
  IF p_log_history             IS NOT NULL THEN r_job_class.log_history := p_log_history; END IF;
  IF p_comments                IS NOT NULL THEN r_job_class.comments := p_comments; END IF;
  
  dbms_Scheduler.set_attribute(p_job_class_name, 'resource_consumer_group', r_job_class.resource_consumer_group);
  dbms_Scheduler.set_attribute(p_job_class_name, 'service'                , r_job_class.service);
  IF    r_job_class.logging_level = 'OFF'         THEN dbms_Scheduler.set_attribute(p_job_class_name, 'logging_level', DBMS_SCHEDULER.LOGGING_OFF);
  ELSIF r_job_class.logging_level = 'RUNS'        THEN dbms_Scheduler.set_attribute(p_job_class_name, 'logging_level', DBMS_SCHEDULER.LOGGING_RUNS);
  ELSIF r_job_class.logging_level = 'FAILED RUNS' THEN dbms_Scheduler.set_attribute(p_job_class_name, 'logging_level', DBMS_SCHEDULER.LOGGING_FAILED_RUNS);
  ELSIF r_job_class.logging_level = 'FULL'        THEN dbms_Scheduler.set_attribute(p_job_class_name, 'logging_level', DBMS_SCHEDULER.LOGGING_FULL);
  END IF;
  dbms_Scheduler.set_attribute(p_job_class_name, 'log_history'            , r_job_class.log_history);
  dbms_Scheduler.set_attribute(p_job_class_name, 'comments'               , r_job_class.comments);
...
EXCEPTION 
  WHEN no_data_found THEN
...
    RAISE;
END create_job_class;

This new procedure is called from the procedures that create jobs.  In the example below, the LOW job class is cloned into a new PURGE_API_LOG_CLASS that is used by the PURGE_API_LOG job.  I have set the log history retention to 7 days, but all other settings remain the same.  

PROCEDURE create_purge_api_log_job
IS
  k_job_name  CONSTANT VARCHAR2(128 CHAR) := 'STRAVA.PURGE_API_LOG';
  k_job_class CONSTANT VARCHAR2(128 CHAR) :=    'SYS.PURGE_API_LOG_CLASS';
BEGIN
...
  create_job_class(k_job_class,'LOW', p_log_history=>7);
  BEGIN
    dbms_scheduler.create_job(
    (job_name => k_job_name
    ,job_type => 'STORED_PROCEDURE'
    ,job_action => 'STRAVA.STRAVA_HTTP.PURGE_API_LOG'
    ,enabled => FALSE
    );
  EXCEPTION WHEN e_job_already_exists THEN NULL;
  END;
...
  dbms_scheduler.set_attribute(name => k_job_name, attribute => 'JOB_CLASS', value => k_job_class);
...
  dbms_scheduler.enable(name => k_job_name);
...
END create_purge_api_log_job;
...
END strava_job;
/

Now I have several job classes 

OWNER JOB_CLASS_NAME                        RESOURCE_CON SERVICE
----- ------------------------------------- ------------ ------------------------------------------------------
LOGGING_LEVEL LOG_HISTORY COMMENTS                      
------------- ----------- ------------------------------
SYS  CREATE_ACTIVITY_HSEARCH_UPD_ALL_CLASS  LOW          GE***********09_GOFASTER1_low.adb.oraclecloud.com    
RUNS                   7 Low priority jobs            

SYS  ACTIVITY_AREA_LIST_UPD_ALL_CLASS        LOW         GE***********09_GOFASTER1_low.adb.oraclecloud.com    
RUNS                   7 Low priority jobs            

SYS  PURGE_API_LOG_CLASS                     LOW         GE***********09_GOFASTER1_low.adb.oraclecloud.com    
RUNS                   7 Low priority jobs            

SYS  PURGE_EVENT_QUEUE_CLASS                 LOW         GE***********09_GOFASTER1_low.adb.oraclecloud.com    
RUNS                   7 Low priority jobs            

SYS  BATCH_LOAD_ACTIVITIES_CLASS             MEDIUM      GE***********09_GOFASTER1_medium.adb.oraclecloud.com 
RUNS                   7 Medium priority jobs         

SYS  UPDATE_STRAVA_ACTIVTY_CLASS             MEDIUM      GE***********09_GOFASTER1_medium.adb.oraclecloud.com 
RUNS                   7 Medium priority jobs         

SYS  PROCESS_WEBHOOK_QUEUE_CLASS             MEDIUM      GE***********09_GOFASTER1_medium.adb.oraclecloud.com 
RUNS                   7 Medium priority jobs         

SYS  RENEW_STRAVA_TOKENS_CLASS               HIGH        GE***********09_GOFASTER1_high.adb.oraclecloud.com   
RUNS                   7 High priority jobs

Each job has been allocated to a different job class.  In future, I can control the behaviour of each job by adjusting the job class.

OWNER  JOB_NAME                             JOB_CLASS                            
------ ------------------------------------ -------------------------------------
STRAVA ACTIVITY_AREA_LIST_UPD_ALL_JOB      ACTIVITY_AREA_LIST_UPD_ALL_CLASS 
STRAVA BATCH_LOAD_ACTIVITIES_JOB           BATCH_LOAD_ACTIVITIES_CLASS 
STRAVA CREATE_ACTIVITY_HSEARCH_UPD_ALL_JOB CREATE_ACTIVITY_HSEARCH_UPD_ALL_CLASS
STRAVA PROCESS_WEBHOOK_QUEUE_JOB           PROCESS_WEBHOOK_QUEUE_CLASS 
STRAVA PURGE_API_LOG                       PURGE_API_LOG_CLASS 
STRAVA PURGE_EVENT_QUEUE                   PURGE_EVENT_QUEUE_CLASS 
STRAVA RENEW_STRAVA_TOKENS_JOB             RENEW_STRAVA_TOKENS_CLASS 
STRAVA UPDATE_STRAVA_ACTIVTY_JOB           UPDATE_STRAVA_ACTIVTY_CLASS