Generating SQL scripts using the VALUES clause

Generating SQL Scripts Using the VALUES Clause in Oracle AI Database 26ai 🚀

Sometimes, a small SQL feature can make a big difference in developer productivity.

Oracle AI Database 26ai introduces a table value constructor for SELECT, allowing the VALUES clause to act as an inline table in the FROM or WITH clause.

Why is this useful? You can work with a small set of values directly in SQL without creating a physical table or writing multiple UNION ALL statements.

💡 A practical use case: Dynamically generating SQL scripts

Suppose you're building Oracle database demos or lab environments and need to create database users with specific privileges.

Instead of manually writing every GRANT statement, you can generate them dynamically:

select
    'grant ' || privilege || ' to  singhsha;'
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);

The VALUES clause creates an inline table named p, with a column called privilege. The SELECT statement then generates a GRANT command for each row.

The result? A ready-to-review list of SQL statements that can be incorporated into an initialization script, checked into Git, and reused when setting up demo environments.

Which results in this neat script:

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

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

Why I like this approach

✅ Less boilerplate SQL

✅ Better readability and maintainability

✅ No need for repetitive UNION ALL queries

✅ Convenient generation of scripts for database initialization, migrations, and administrative tasks

The generated statements should always be reviewed before execution, particularly when dealing with database privileges.

The takeaway: The VALUES clause isn't just a convenient way to define inline data. In Oracle AI Database 26ai, it can also simplify the process of generating SQL scripts dynamically.

📚 Explore the Oracle AI Database 26ai documentation to learn more about the VALUES clause.

Happy scripting!

#OracleDatabase #Oracle26ai #SQL #DatabaseDevelopment #Automation #PLSQL