Oracle SQL and Generative AI

I had done the first post around use of Generative AI tools like #CharGPT to generate Oracle SQL here.

I decided to test it further using an Oracle APEX workspace which is free to obtain. While there is a approval process, it is usually very quick. The overall goal is to see if we have to start from scratch an Oracle database environment for testing or training, can we do it quickly using Generative Tools along with generation of sample data.

Since, I wanted to start from scratch, I started by asking it to give me the SQL for employee table:

Provide SQL to create an employee table with columns emp_id, emp_name, start_dt, position, comments, dept_id in Oracle database;

ChatGPT gave me the SQL with foreign key to Department table.

CREATE TABLE employee (

?emp_id NUMBER(10) PRIMARY KEY,

?emp_name VARCHAR2(50) NOT NULL,

?start_dt DATE NOT NULL,

?position VARCHAR2(50),

?comments VARCHAR2(200),

?dept_id NUMBER(10),

?CONSTRAINT fk_dept_id FOREIGN KEY (dept_id)

??REFERENCES department (dept_id)

);

ChatGPT provided a decent explanation as well of the SQL:

This SQL statement creates a table named "employee" with the specified columns and data types. The "emp_id" column is defined as the primary key, while the "dept_id" column is defined as a foreign key referencing the "dept_id" column in a table named "department". The "NOT NULL" constraint is used to ensure that certain columns cannot be left empty when inserting data into the table.

Since ChatGPT gave me the SQL with foreign key to Department table, I asked it for the SQL to create the Department table.

I got that as well:

CREATE TABLE department (

?dept_id NUMBER(10) PRIMARY KEY,

?dept_name VARCHAR2(50) NOT NULL

);

After creating the Department and the Employee table in that sequence, in APEX environment, I asked ChatGPT to help create 10 rows of sample data to populate the Department table.

create 10 records to create sample data in table department table and the SQL to insert those records in Oracle database

That is where the issue started. Here is the SQL code I got:

INSERT INTO department (dept_id, dept_name)

VALUES

?(1, 'Sales'),

?(2, 'Marketing'),

?(3, 'Engineering'),

?(4, 'Finance'),

?(5, 'Human Resources'),

?(6, 'Customer Service'),

?(7, 'Operations'),

?(8, 'Research and Development'),

?(9, 'Legal'),

?(10, 'Information Technology');

A quick inspection would reveal that code would run in MySQL or SQL Server but not in Oracle.

By the way when I asked Google #BARD to create Oracle SQL for inserting one row, it gave up!

create oracle SQL insert statement for one sample row in table named department with columns dept_id, dept_name

BARD: I'm not trained for coding yet, so I can't help with that right now. I can help you write things, brainstorm ideas, or answer other complex questions. Maybe we can try something like that?

ChatGPT did a fine job for one row in Oracle:

create oracle SQL insert statement for one sample row in table named department with columns dept_id, dept_name

INSERT INTO department (dept_id, dept_name)

VALUES (1, 'Sales');

I did the manual workaround to fix the SQL and make it run for Oracle DB, to insert multiple rows. Look at this site for more details on how INSERT SQL varies across different databases.

I manually created the INSERT SQL for multiple records as below:

INSERT INTO employee (emp_id, emp_name, start_dt, position, comments, dept_id)

WITH e AS (

??SELECT 5, 'John Smith', TO_DATE('2022-01-01', 'YYYY-MM-DD'), 'Manager', 'Joined the company recently', 1 FROM dual UNION ALL

??SELECT 2, 'Jane Doe', TO_DATE('2021-06-01', 'YYYY-MM-DD'), 'Engineer', 'Works on the frontend team', 2 FROM dual UNION ALL

??SELECT 3, 'Bob Johnson', TO_DATE('2020-03-15', 'YYYY-MM-DD'), 'Designer', 'Expert in UI design', 1 FROM dual UNION ALL

??SELECT 4, 'Sara Lee', TO_DATE('2019-09-01', 'YYYY-MM-DD'), 'Engineer', 'Works on the backend team', 2 FROM dual

?)

SELECT * FROM e;

And then executed it in Oracle APEX, worked fine. Then tested it by Selecting the rows for the Employee table. This is how it looked like in Oracle APEX environment:

My conclusion so far is that Generative AI tools like ChatGPT have a good promise but requires human intervention to deal with the differences between different flavors of database. I am yet to test more complex scenarios of SQL, PL/SQL and complex joins.

Please feel free to share you feedback on this topic. Ravi Sharma Arup Nanda Richard Niemiec Y V Ravi Kumar (Oracle Certified Master) Satyendra Pasalapudi? Jennifer Nicholson