Oracle AI Database 26ai adds a table value constructor to SELECT, allowing VALUES to act as a small inline table in the FROM or WITH clause (📕 docs). This is useful when you want to work with a short list of values without creating a table or writing multiple UNION ALL queries. The VALUES clause is available for INSERT and MERGE statements, too.

This post might be a bit niche, but it nicely demonstrates the benefit you get from the VALUES clause:

The introduction of the new VALUES clause allows developers to write less code for ad-hoc SQL commands, leading to better readability with less effort.

Here’s what might be a rather uncommon use case: since I build demos frequently I have to come up with database user creation commands in Oracle AI Database Free. One way to do that is to manually write every single command into a text file, put it in Git, and use it whenever it’s needed. When I was starting working with Oracle a while ago I soon used to create SQL scripts and commands dynamically, and this is what the post is about. In the past I selected and concatenated various columns from tables to form SQL commands that I’d spool into a file. Nowadays I add that file to Git and pass it to a container image for database initialisation.

Here’s the concrete example. Let’s assume I need to create a user in a Pluggable Database (PDB). That’s easy:

create user martin identified by "&password"
default tablespace users
temporary tablespace temp
quota 1g on users
profile ora_stig_profile;

That’s the user created. This account is of little use, because I didn’t grant it any rights on the database. If this were a lab environment on my laptop all I’d have to do is grant db_developer_role to martin and be done with it, but my goal is to create something closer to a potential production setup.

If I wanted to grant things directly, this is a valid approach in Oracle AI Database 26ai and later:

select
    'grant ' || privilege || ' to  martin;'
from
    (
        values
            ('create assertion'),
            ('create domain'),
            ('create job'),
            ('create mle'),
            ('create session'),
            ('debug connect session'),
            ('execute dynamic mle'),
            ('create materialized view'),
            ('create procedure'),
            ('create property graph'),
            ('create sequence'),
            ('create synonym'),
            ('create table'),
            ('create trigger'),
            ('create type'),
            ('create view')
    ) p (privilege);

Here you can see the new feature in in action: the VALUES clause creates a one-column inline table named p with column privilege. The select statement concatenates the privilege into a grant statement and prints it on screen.

Which results in this neat script:

grant create assertion to  martin;
grant create domain to  martin;
grant create job to  martin;
grant create mle to  martin;
grant create session to  martin;
grant debug connect session to  martin;
grant execute dynamic mle to  martin;
grant create materialized view to  martin;
grant create procedure to  martin;
grant create property graph to  martin;
grant create sequence to  martin;
grant create synonym to  martin;
grant create table to  martin;
grant create trigger to  martin;
grant create type to  martin;
grant create view to  martin;

After careful review, it can be deployed to my demo/lab environment and checked into Git.

Happy scripting!