Since SQLcl is actively developing new features, and sqlplus appears to be effectively stagnant, something I'd love to see would be better handling of optional script parameters. Namely a more intuitive method for assigning default values to parameters which aren't provided.
The only method I'm currently aware for handling this involves use of a query which returns no rows, and selects NULL for all expected parameters. This somehow sets any undefined positional parameters to NULL, while leaving the other values intact. It's often followed by a secondary assignment, to transform the NULL values into the desired default. Here's a quick example of what I mean, which simply defaults the first parameter to 10 and the second to abc and displays the final value.
-- Initialize unset parameters to NULL, using a query which returns no results,
-- in case the values weren't provided. Suppress prompting for omitted
-- parameters, in other words.
set termout off
column 1 new_value 1
column 2 new_value 2
select NULL "1", NULL "2"
from dual
where 0 = 1;
column 1 clear
column 2 clear
set termout on
-- Assign parameter values to final variables. This will make later usage
-- easier to follow, by avoiding the need to wrap every reference with the
-- NVL() function.
set termout off
column p1 new_value p1
column p2 new_value p2
select nvl( '&1', 10 ) p1,
nvl( '&2', 'abc' ) p2
from dual;
column p1 clear
column p2 clear
set termout on
prompt Final parameter 1 is &p1
prompt Final parameter 2 is &p2
undefine 1
undefine 2
undefine p1
undefine p2
Here's the output from a few test runs.
SQL> @test
Final parameter 1 is 10
Final parameter 2 is abc
SQL> @test 1
Final parameter 1 is 1
Final parameter 2 is abc
SQL> @test 1 foo
Final parameter 1 is 1
Final parameter 2 is foo
SQL> @test "" bar
Final parameter 1 is 10
Final parameter 2 is bar