Three Ways To Pass Environment Variables into SQLcl

Trying to reference an environment variable in SQLcl can be a bit tricky. Here are three ways you can do it.

1. Pass the Variable to a Script.

# Create a script that accepts a command line parameter
cat > test.sql <<EOF
define FOO='&1'
insert into t1 (txt) values ('&FOO');
select txt from t1;
exit;
EOF

# Set the environment variable
export FOO='BAR'

# Pass the environment variable to the script
sql -L -s usr/pwd@db @test.sql ${FOO}

old:insert into t1 (txt) values ('&FOO')
new:insert into t1 (txt) values ('BAR')

1 row inserted.


TXT      
________   
BAR      

2. Use JavaScript in SQLcl

export FOO="BAR"
sql -L -s usr/pwd@db
SQL> script
var foovariable = java.lang.System.getenv("FOO");
var binds = {
  "FOO": foovariable
};

var sql = [
    "begin",
    "insert into t1 (txt) values(:FOO);",
    "end;"
].join("\n");

var result = util.execute(sql, binds);

if (!result){
  ctx.write("ERROR\n");
  ctx.write(util.getLastException());
  ctx.write("\n");
}
/

select txt from t1;

TXT      
________   
BAR      

3. Use a Liquibase Changelog

Create a Changelog File

--liquibase formatted sql
--changeset  christoph:1
insert into t1 (txt) values ('${FOO}');
--rollback not required

After exporting the variable as above, log into SQLcl and execute the Liquibase changelog.

SQL> lb update -changelog my_changelog.sql
--Starting Liquibase at 14:16:44 (version 4.9.1 #0 built at 2022-05-03 17:23+2221)

-- Loaded 1 changeSets
Running Changeset: my_changelog.sql::1::christoph
No Errors Encountered
SQL> select txt from t1;

TXT      
________   
BAR