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.
😉
The term "Vibe Coding" makes me cringe, but don’t worry, I will use it only once more in this post!
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:
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.
🔐
Security is key. When providing access to REST-enabled SQL, you are essentially giving access to run any DDL or DML on the target schema. Using OAuth credentials is more secure (and faster) than basic authentication, which uses the schema name and password.
Language
I chose Python to build the tool for three reasons:
Python is super popular, and LLMs have had thorough training in Python (as opposed to PL/SQL).
I can run Python scripts from the command line.
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.
💡
I suggest using the gpt-4o-copilot model for completions. It was trained for programming specifically and seems to do a better job.
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.
💡
I don’t believe Copilot ‘knows PL/SQL’ but it does a pretty good job of figuring it out.
⚠
Of course, all of the usual warnings apply. I use Copilot as a junior assistant and something to run ideas by; I do not use it to develop software. Not yet anyway.
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:
💡
There are many options for adding context to your prompts. You can include folders and their contents as well as GitHub repositories.
Copilot then went off for about a minute and came back with the starting point for the Python program:
💡
In Edit mode, Copilot makes changes and asks you to confirm before the changes are applied.
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 should note that I have not yet started using this tool for production deployments, like any good programmer, I want to ensure it is thoroughly tested first.
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.
