Is it possible to pass a variable to a SQL / DDL script?

Question

We have a bunch of scripts to reuse the base database. The script we run calls with other scripts:

@@set_target_schema @@drop_all -- do some other stuff - create tables, insert data, etc commit; 

set_target_schema.sql looks like this:

 define TARGET_SCHEMA="OUR_APP_NAME" 

Is there a way to pass an optional parameter to our upper level script, and then pass this parameter to set_target_schema.sql and use this value as the schema name if specified, otherwise use the default value?

Answer 1

To be able to use the default values, you can do something like this:

In the main file:

 SET VERIFY OFF -- specify as many substitution variable as you need to. COLUMN 1 NEW_VALUE 1 noprint COLUMN 2 NEW_VALUE 2 noprint REM COLUMN 3 NEW_VALUE 3 noprint REM .......... REM COLUMN <N> NEW_VALUE <N> noprint SELECT '' "1" , '' "2" FROM dual WHERE 0 = 1; -- Default values. select decode('&1', null, 'Default1', '&1') "1" , decode('&2', null, 'Default1', '&2') "2" from dual; -- prints substitution variables' values @@set_target_schema.sql '&1' '&2' undefine 1 undefine 2 

Result:

 -- without parameters SQL> @c:\main.sql 'DEFAULT 'DEFAULT -------- -------- Default1 Default1 -- with parameters SQL> @c:\main.sql parameter1 parameter2 'PARAMETER 'PARAMETER ---------- ---------- parameter1 parameter2