We recently posed a simple question to our most dedicated CloudSQL SQL Editor users: Are you using AI?
The answer? No surprises there—everyone has tried ChatGPT or its equivalents at some point. But here’s the real kicker: a solid 80% of our surveyed users rely on AI regularly to help craft their SQL queries.
Now, we didn’t just stop at stats. We went straight to the SQL experts and asked them to spill their best AI-powered SQL tips. Big shoutout to everyone who participated and shared their insights—you’re the real MVPs!
So, after sifting through all the responses, here’s one golden rule from our experts:
1. Identify yourself as an Oracle Cloud developer.
Alright, let’s get this straight from the start—ChatGPT has a habit of making up table names. It’s true. Many users have ranted about it, and honestly, we get it. So, the first thing to do? Tell ChatGPT loud and clear:
“Hey, I’m an Oracle Cloud Fusion developer, and all SQL queries must be valid for Oracle Cloud!”
Now, does that mean ChatGPT will always behave? Not exactly. Most of the time, it’ll try its best to stick to Oracle Cloud tables. But every now and then, it gets a little too excited and sneaks in some tables from E-Business Suite or just invents something out of thin air. This is where a bit of patience comes in handy.
Think of ChatGPT as that overly eager junior developer we all used to be—enthusiastic, fast, and desperate to impress, but, well… prone to making mistakes. And that’s okay! Just nudge it in the right direction: correct a table name, ask it to refine the query, or gently remind it (again) that this is Oracle Cloud, not some imaginary database. With a bit of back-and-forth, it’ll eventually get there.
2. Still Maintain my Coding Standards
Here’s the thing—ChatGPT is already better than a lot of developers when it comes to formatting SQL. It doesn’t send you code that looks like it was typed by a cat walking across the keyboard. Everything is neatly structured, and that’s great!
But what about your team’s style? The coding conventions that have been around since the dawn of time (or at least since your lead developer declared them sacred)?
That’s where you need to take control. When asking ChatGPT to generate SQL, include your coding standards.
For example, many SQL developers have their own preferences for table aliases. Instead of ChatGPT giving you a generic alias, tell it to follow your team’s convention—like using initials for tables:
AP_INVOICES_ALL → aia
PER_ALL_PEOPLE_F → papf
Little details like this actually make a difference. It keeps your code readable, consistent, and, most importantly, prevents your team lead from sending you passive-aggressive comments in code reviews
3. Prepare your own Prompt Template
Let’s be real—AI is coming for everything, including SQL development. So, what am I doing? Just sitting back, sipping coffee, and letting AI take over? Should I finally hang up my muddy boots of SQL and call it a day?
Not so fast! The truth is, AI is only as good as the prompts you feed it. And that, my friend, is where your unique selling point as a developer comes in. It’s not just about knowing SQL anymore—it’s about knowing how to guide AI to write the right SQL.
The Secret? A Killer Prompt Template
Many of our expert users swear by keeping a ready-made prompt for context-setting. The better your prompt template, the fewer edits you’ll need. No more spending hours fixing AI-generated SQL that looks like it was written by a sleep-deprived intern.
Here’s an example of a battle-tested prompt used by one of our users:
I am an Oracle Cloud Developer working on a BI report and need an SQL query that follows these specific standards:
1. Use only Oracle Cloud tables (no custom tables).
2. Apply non-ANSI JOIN syntax with (+) for outer joins where necessary.
3. Assign aliases to all tables using their initials (e.g., ap_invoices_all → aia).
4. Format all date columns in the SELECT statement as DD-MON-YYYY using TO_CHAR().
5. For HCM tables, always apply the condition:
SYSDATE BETWEEN effective_start_date AND effective_end_date
6. Order conditions properly:
o Join conditions first
o Other filters afterward4. Remember it is a conversation.
Do not try to do everything in one short. Give a conversation. Some of our users will start with the above note giving a context, followed by the below. As many of us did back in the day , in late 2023 playing around with chatgpt and trying to find how stupid it is. That’s not the way. Have a conversation. Develop step by step , provide your expert opinion , get revisions. Go back to previous version, tell it what u like. Give an example. Try all these. Below is an example prompt
User: <<Context Template>> I want to write an SQL to get all the customer accounts created in the last month
ChatGPT:
SELECT
hca.cust_account_id,
hca.account_number,
hca.account_name,
TO_CHAR (hca.creation_date, 'DD-MON-YYYY') AS creation_date
FROM
hz_cust_accounts hca
WHERE
hca.creation_date BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM') AND TRUNC (SYSDATE, 'MM') - 1
ORDER BY
hca.creation_date;
User: please add party_name and remove cust_account_id
ChatGPT:
SELECT
hca.account_number,
hca.account_name,
hp.party_name,
TO_CHAR (hca.creation_date, 'DD-MON-YYYY') AS creation_date
FROM
hz_cust_accounts hca,
hz_parties hp
WHERE
hca.party_id = hp.party_id
AND hca.creation_date BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM') AND TRUNC (SYSDATE, 'MM') - 1
ORDER BY
hca.creation_date;
User: i want only party type of organization
ChatGPT:
SELECT
hca.account_number,
hca.account_name,
hp.party_name,
TO_CHAR (hca.creation_date, 'DD-MON-YYYY') AS creation_date
FROM
hz_cust_accounts hca,
hz_parties hp
WHERE
hca.party_id = hp.party_id
AND hp.party_type = 'ORGANIZATION'
AND hca.creation_date BETWEEN TRUNC (ADD_MONTHS (SYSDATE, -1), 'MM') AND TRUNC (SYSDATE, 'MM') - 1
ORDER BY
hca.creation_date;
Final Thoughts
Let’s face it—AI isn’t taking your job (yet), but it sure can make it a lot easier! ChatGPT is like that eager intern who tries really hard but sometimes needs a little guidance. By setting the right context, enforcing your sacred coding standards, crafting killer prompts, keeping the conversation going, and throwing in a few examples, you can whip ChatGPT into shape and get SQL queries that actually work—on the first try (well, most of the time).
So, go ahead—tame the AI, make it work for you, and save yourself from another day of debugging nonsense joins. Your future self will thank you! ?
