CODEWITHSUNDEEP
sqloracle-databaseschemaddl
Chris Williams
Chris Williams

Reputation: 12481

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

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?

Upvotes: 4

Views: 1448

Answers (1)

Nick Krasnov
Nick Krasnov

Reputation: 27251

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

Upvotes: 3

Related Questions