Introduction

This post describes my experience with “Vibe Coding”, a Python command-line deployment tool for deploying PL/SQL, DML, DDL, and APEX Applications to the Oracle Cloud OCI APEX Application Development Service.

Why did I do this?

The APEX Application Development service offers fantastic value. It has the power of an Autonomous Database at about half the cost. One thing you miss out on is the ability to connect with desktop tools such as SQLCl and SQL Developer. This makes code deployments slow, error-prone, and painful.

Approach

Deployment Method

My approach is to use the following Oracle REST Data Services (ORDS) technologies to ‘POST’ code to the APEX Service Database from a command line tool:

• REST-enabled SQL to deploy database objects and run DML and DDL.

• An ORDS REST API to deploy APEX Applications. You can read how to create this REST API in Carsten Czarski’s post.

I created the following artifacts in the target APEX Service Instance:

• PL/SQL Package containing the code to deploy APEX Applications over REST.

• An ORDS module, template, and handler for the REST API to call the above PLSQL to deploy APEX Applications.

• An ORDS OAuth Client.

• A new Role ‘Deploy APEX Applications’ and Privilege to secure the ORDS Module

• Assign the ‘SQL Developer’ role (which allows access to REST-enabled SQL) and the ‘Deploy APEX Applications’ role to the OAuth2 Client.

Language

I chose Python to build the tool for three reasons:

1. Python is super popular, and LLMs have had thorough training in Python (as opposed to PL/SQL).

2. I can run Python scripts from the command line.

3. I have been curious about Python for a while and wanted to see what it is like.

GitHub Copilot

I am using the paid version of Github Copilot. I chose CoPilot because it is tightly integrated with VS Code, which I use along with SQL Developer for VS Code for my PL/SQL development.

VS Code Extensions

You will need to add the following extensions to VS Code to use Copilot:

Copilot for PL/SQL Development

Before this exercise, I used Copilot for PL/SQL development for about a month.

Code Completions

The main benefit to me is in the Code Completions feature. This is where you start typing, and ghost text appears, predicting what you will type next. You just hit tab to accept the text or escape to cancel the completion. For some tasks, this feature alone sped up my development by 200%; for others, I get a more modest 10% or so advantage.

For example, I recently created ORDS web services to ingest sales orders from e-Business Suite. This involved building a MERGE statement based on the output from a JSON_TABLE statement.

  MERGE INTO cptl_oe_order_headers oh
    USING (SELECT header_id
           ,      order_number
           ...
          FROM   JSON_TABLE(p_json,
                  '$.items[*]'
                  COLUMNS (
                    header_id              NUMBER        PATH '$.header_id',
                    order_number           NUMBER        PATH '$.order_number',
                    ...
                  )
                )) joh
    ON     (oh.header_id = joh.header_id)
    WHEN MATCHED THEN UPDATE
      SET oh.order_number           = joh.order_number
      ...
    WHEN NOT MATCHED THEN INSERT
      (oh.header_id, oh.order_number, 
       ...
      )
      VALUES
       (joh.header_id, joh.order_number,
       ...
       );

I started typing in the MERGE statement with the table creation script open in one tab and the PL/SQL open in another. After entering the outline of the statement, Copilot realized what it was supposed to do. I could then tab through the code completion suggestions from Copilot to complete the MERGE statement containing 30 columns in under 30 seconds. This would have taken me 5 minutes without Copilot.

Other Things I use Copilot for in PL/SQL

• Documenting my code.

• Renaming variables.

• Determining the impact of changing code.

• Looking for performance issues.

• Looking for security issues.

• Suggesting improvements.

• Writing small functions or procedures.

• Checking for unused variables.

Requirements

I had these basic requirements for the deployment tool:

• The code to be deployed should be added to a bill of materials JSON file.

• Ability to deploy PL/SQL, DDL, DML, and APEX Apps.

• Ability to stop or continue on error for each line in the deployment.

• Configure the target instance details in a config.json file.

• Log progress to a log file and output key progress steps to the terminal.

• Perform a check to make sure all deployment files exist before starting.

• Perform a connectivity check before starting deployment.

Details about the target instance are stored in the file config.json

{
    "auth": "oauth2",
    "client_id": "AAA",
    "client_secret": "BBB",
    "grant_type": "client_credentials",
    "token_url": "https://abc.adb.us-chicago-1.oraclecloudapps.com/ords/demo/oauth/token",
    "sql_url": "https://abc.adb.us-chicago-1.oraclecloudapps.com/ords/demo/_/sql",
    "apex_url": "https://abc.adb.us-chicago-1.oraclecloudapps.com/ords/demo/deploy/app/"
}

A bill of materials containing what should be deployed is stored in a separate json file:

[
  {
    "file_name": "/Users/jdixon/Downloads/Personal/Cloud Deploy/Target Server Install/f1011.sql",
    "object_type": "APEX",
    "application_id": "103",
    "workspace": "WKSP_WKSPDEMO",
    "application_alias": "TESTDEPLOY"
  },
  {
    "file_name": "/Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_LINES_CRTB.sql",
    "object_type": "TABLE"
  }
]

Postman

I used Postman to test the calls to REST-enabled SQL to deploy DDL and DML, and the ORDS API to deploy APEX Applications. This allowed me to generate curl statements which I could use to describe these API calls to Copilot.

Deploying an APEX Application using an ORDS API

Deploying DDL and DML using REST-Enabled SQL

I also saved some responses (both successful and failed ) to JSON files to help Copilot understand their format.

Vibe Coding in Python

Let me start by saying I am pretty good at PL/SQL, but do not know Python.

I started by opening up a new file in VS Code and opening up the Copilot Edits feature:

I then dragged and dropped the sample json files into the context area and typed in my initial prompt:

Copilot then went off for about a minute and came back with the starting point for the Python program:

From there on out, it was a matter of iterating. I would send Copilot a new feature request, review and confirm the edits, and repeat the process until the program did what I wanted it to do.

Tips

• Don’t try to add too much functionality in each request.

• Make your requests as specific as possible. Use field names where possible.

• Review each edit and understand it as best you can before confirming it.

• Don’t be afraid to challenge the results.

• Copilot did not seem to be breaking features up into separate functions; I had to prompt it to do this.

• Every so often, prompt Copiloit to review the code holistically and suggest improvements.

• Ask questions like what security risks are present in the code.

• Include relevant files in the context, but don’t include unrelated or unnecessary context.

My Key Takeaways

• I produced a fully functional command-line deployment tool for the OCI APEX Development Service without any Python knowledge. I am not ready to use this approach to build production code without a high level of supervision.

• It took me about six hours to complete an MVP version I was happy with. With what I know now, I think I could get this down to 2-3 hours.

• This is a great way to learn a new programming language.

• 😊 I had a lot of fun and I think you will also 😊

Developers should make fun of this coding style at their peril. It may not be producing production-ready code just yet, but it is improving productivity. Even a 10% improvement in productivity is a big deal.

The Result

The Code

I will let the Python experts judge the quality of the code for themselves. Before judging, however, I would say that I know nothing about Python, and I ended up with a program that does what I want it to do.

Sample Deployment Output

▶️  Checking that all Deployment Files Exist...
▶️  Testing Connectivity...
🟢 Connection Test Successful
➡️  [TABLE] /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_LINES_CRTB.sql
✅   Deployed: /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_LINES_CRTB.sql
➡️  [TABLE] /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_PATH_INST_CRTB.sql
✅   Deployed: /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_PATH_INST_CRTB.sql
➡️  [TABLE] /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_PATHS_CRTB.sql
✅   Deployed: /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_PATHS_CRTB.sql
➡️  [TABLE] /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_REQUESTS_CRTB.sql
✅   Deployed: /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENT_REQUESTS_CRTB.sql
➡️  [TABLE] /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENTS_CRTB.sql
✅   Deployed: /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_DEPLOYMENTS_CRTB.sql
➡️  [TABLE] /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_INSTANCE_ACCESS_CRTB.sql
✅   Deployed: /Users/jdixon/Documents/Work/CN/Nueva Apps/CNCD/Product Install/tables/CNCD_INSTANCE_ACCESS_CRTB.sql
========================================
🏁🏁🏁 Deployment Complete 🏁🏁🏁
========================================

Conclusion

I believe that developers can no longer ignore tools like Copilot. If you don’t improve your productivity by 10% using these tools, someone else will. Rightly or wrongly, these tools open up coding opportunities to people with little or no programming experience. The good news is these tools also take care of some of the tedious tasks and let you focus on the fun stuff.

Related Links

• GitHub Copilot in VS Code

• Tips for building with GitHub Copilot

• VSCode & Copilot Free for PL/SQL from Matt Mulvaney