We had our 2nd episode from our monthly news webcast yesterday, and the recording is already available.
This time we remembered to keep the mics open to take some Q&A !
TL/DW
The database highlights Kris and I covered:
- Sessionless Transactions – do work over multiple sessions, perfect for REST APIs!
- DBMS_DEVELOPER – a new utility package, get metadata for your objects, fast!
- LiveSQL – refresher, plus we added support for working with both 19c & 23ai
You can read up on all the new features in 23ai in our Database Docs.
There were some questions, including:
- will SQLDev for VS Code get DBA features, like Backup & Recovery
Yes, we’re talking to the RMAN team now about just that! - will the Autonomous Developer container get an ARM dist?
Yes, the Autonomous team is working on that right now, stay tuned!
A couple of new things regarding LiveSQL.
The aforementioned 19 or 23ai, just use the drop-down selector.
Note if you sign in so you can create your own objects, those schemas are not replicated between those two versions of the database. They’re distinct, so creating your EMPS table in the 19c instance, won’t have any impact on the 23ai instance you would be using if you switched over.
Something we didn’t cover because it didn’t go live until just after our webcast, something really nice for those of you who take your personal time to help people learn SQL.
Share your sample code, send folks to run it on LiveSQL.
I can share a SQL tutorial here, and give my readers (you lovely people!) a ‘run it now’ button.
Run my 23ai concepts tutorial script in LiveSQL – no database required!
The code you’re going to be running…
PURGE RECYCLEBIN;
drop domain if exists emails;
drop domain if exists cc;
create domain emails as varchar2(100)
constraint email_chk check (regexp_like (emails, '^(\S+)\@(\S+)\.(\S+)$'))
display lower(emails)
order lower(emails)
annotations (Description 'An email address with a check constraint for name @ domain dot (.) something');
create domain cc as varchar2(19)
constraint cc_chk check (regexp_like (cc, '^\d+(\d+)*$'))
annotations (Description 'Credit card "number" with a check constraint no dashes, no spaces!');
DROP TABLE if exists orders CASCADE CONSTRAINTS purge;
DROP TABLE if exists customers CASCADE CONSTRAINTS purge;
-- Create a table to store order data
CREATE TABLE if not exists orders (
id NUMBER,
product_id NUMBER,
order_date TIMESTAMP,
customer_id NUMBER,
total_value NUMBER(6,2),
order_shipped BOOLEAN,
warranty INTERVAL YEAR TO MONTH
);
-- Create a table to store customer data
CREATE TABLE if not exists customers (
id NUMBER,
first_name VARCHAR2(100),
last_name VARCHAR2(100),
dob DATE,
email emails,
address VARCHAR2(200),
zip VARCHAR2(10),
phone_number VARCHAR2(20),
credit_card cc,
joined_date TIMESTAMP DEFAULT SYSTIMESTAMP,
gold_customer BOOLEAN DEFAULT FALSE,
CONSTRAINT new_customers_pk PRIMARY KEY (id)
);
-- Add foreign key constraint to new_orders table
ALTER TABLE orders ADD (CONSTRAINT orders_pk PRIMARY KEY (id));
ALTER TABLE orders ADD (CONSTRAINT orders_fk FOREIGN KEY (customer_id) REFERENCES customers (id));
INSERT INTO customers (id, first_name, last_name, dob, email, address, zip, phone_number, credit_card)
VALUES (1, 'Alice', 'Brown', DATE '1990-01-01', '[email protected]', '123 Maple Street', '12345', '555-1234', '4111111111110000'),
(3, 'Bob', 'Brown', DATE '1990-01-01', '[email protected]', '333 Maple Street', '12345', '555-5678', '4111111111111111'),
(4, 'Clarice', 'Jones', DATE '1990-01-01', '[email protected]', '222 Bourbon Street', '12345', '555-7856', '4111111111111110'),
(5, 'David', 'Smith', DATE '1990-01-01', '[email protected]', '111 Walnut Street', '12345', '555-3221', '4111111111111112'),
(6, 'Eunice', 'Williams', DATE '1990-01-01', '[email protected]', '13 Oak Street', '12345', '555-4321', '4111111111111113'),
(7, 'Frank', 'Cox', DATE '1990-01-01', '[email protected]', '23 Hickory Street', '12345', '555-9876', '4111111111111114'),
(8, 'Georgia', 'Clemens', DATE '1990-01-01', '[email protected]', '12 Elm Street', '12345', '555-1111', '4111111111111115'),
(9, 'Hank', 'Ryan', DATE '1990-01-01', '[email protected]', '13 Pine Street', '12345', '555-2222', '4111111111111116'),
(10, 'Iris', 'Rice', DATE '1990-01-01', '[email protected]', '321 Rhododendron Street', '12345', '555-3333', '4111111111111117'),
(11, 'Jack', 'Giles', DATE '1990-01-01', '[email protected]', '1 Birch Street', '12345', '555-4444', '4111111111111118'),
(12, 'Lucy', 'Lynch', DATE '1990-01-01', '[email protected]', '3 Redwood Street', '12345', '555-5555', '4111111111111119'),
(13, 'Mark', 'Young', DATE '1990-01-01', '[email protected]', '23 Cherry Street', '12345', '555-6665', '4111111111111110')
;
INSERT INTO orders (id, customer_id, product_id, order_date, total_value, order_shipped, warranty)
VALUES
(100, 1, 101, SYSTIMESTAMP, 300.00, null, null),
(101, 12, 101, SYSTIMESTAMP-30, 129.99, true, interval '5' year)
;
CREATE OR REPLACE FORCE JSON RELATIONAL DUALITY VIEW CUSTOMERS_DV AS
customers @insert @update @delete
{
_id : id,
FirstName : first_name,
LastName : last_name,
DateOfBirth : dob,
Email : email,
Address : address,
Zip : zip
phoneNumber : phone_number
creditCard : credit_card
joinedDate : joined_date
goldStatus : gold_customer
};
INSERT INTO customers_DV values ('{"_id": 2, "FirstName": "Jim", "LastName":"Brown", "Email": "[email protected]", "Address": "456 Maple Street", "Zip": 12345}');
commit;
-- alternative GraphQL syntax
--CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW customer_orders_dv
-- annotations (Description 'JSON Relational Duality View sourced from --CUSTOMERS and ORDERS')
-- AS
-- customers
-- {
-- _id : id,
-- FirstName : first_name,
-- LastName : last_name,
-- Address : address,
-- Zip : zip,
-- orders : orders @insert @update @delete
-- [
-- {
-- OrderID : id,
-- ProductID : product_id,
-- OrderDate : order_date,
-- TotalValue : total_value,
-- OrderShipped : order_shipped
-- }
-- ]
-- };
-- traditional DDL / SQL
CREATE OR REPLACE JSON RELATIONAL DUALITY VIEW CUSTOMER_ORDERS_DV
annotations (Description 'JSON Relational Duality View sourced from CUSTOMERS and ORDERS')
AS SELECT JSON {
'_id' : c.ID,
'FirstName' : c.FIRST_NAME,
'LastName' : c.LAST_NAME,
'Address' : c.ADDRESS,
'Zip' : c.ZIP,
'orders' :
[ SELECT JSON {
'OrderID' : o.ID WITH NOUPDATE,
'ProductID' : o.PRODUCT_ID,
'OrderDate' : o.ORDER_DATE,
'TotalValue' : o.TOTAL_VALUE,
'OrderShipped' : o.ORDER_SHIPPED
}
FROM ORDERS o
WITH INSERT UPDATE DELETE
WHERE o.CUSTOMER_ID = c.ID
]
}
FROM CUSTOMERS c;
UPDATE customer_orders_dv c
SET c.data = json_transform(
data,
APPEND '$.orders' = JSON {'OrderID':123, 'ProductID' : 202, 'OrderDate' : SYSTIMESTAMP, 'TotalValue' : 150.00}
)
WHERE c.data."_id" =1;
commit;
select * from customer_orders_dv o where o.data."_id" = 1;
That button, once again…
Run in Live SQL
How to make your own
From LiveSQL, hit this button –
From there you’ll get this dialog, where you can get the HTML you can use to embed on your blog, app, whatever.
The code I’m sharing is from this tutorial I wrote last year that combines a few different new features from 23ai:
- JSON Relational Duality Views
- Domains
- Annotations
There’s two coding styles for defining your Duality Views (GraphQL and traditional SQL), and I share both. Give it a try and let me know what you think!
A warning..if someone wants to try your code
Sound advice whether it’s my code, a friend’s or most definitely generated by a LLM – your responsibility does not go away. You need to run and understand the code you’re about to run. ESPECIALLY if it is going anywhere near production or a project that might evolve to a production service.
