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

Question

We have a bunch of scripts to re-baseline our database. The script we run calls a bunch of other scripts:

@@set_target_schema
@@drop_all

-- do some other stuff - create tables, insert data, etc

commit;

set_target_schema.sql looks like:

define TARGET_SCHEMA="OUR_APP_NAME"

Is there any way to pass an optional parameter into our top-level script and then pass that parameter into set_target_schema.sql and use that value as the name of the schema if it's provided, otherwise use the default value?

Although, honestly, it might be worth connecting via a scripting language and using dynamic SQL. — user554546
– user554546, Commented Dec 14, 2012 at 20:34

Nick Krasnov · Accepted Answer · 2012-12-16 10:34:47Z

To be able to use default values you could do something like this:

In you 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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related