Passing parameters to oracle script

Question

This is my scenario.

I have Test1.sql, Test2.sql and Test3.sql files with create, drop, insert, etc statements. The number of files could increase in the future.

I wanted to run all of them in a single script so I created a master script Master.sql, which has the below

    @@Test1.sql
    @@Test2.sql
    @@Test3.sql

I run it as @"Path to the script\master.sql" using SQL plus command prompt on my windows and if I wanted to run it through SQL developer, I just open the master.sql file and run it. So far so good…

Now, I have to run the master.sql in various environments with different user and schema names but the user and schema names are hard coded in the scripts. I want to be able to replace all the occurrences of the hard coded names with parameters and pass them just once on Master script execution. Both from the command prompt and using SQL developer. How do I do it? Please advice.

What about removing hardcoded dependencies from child scripts? Just connect once with selected credentials and call all child scripts ... — ThinkJet
– ThinkJet, Commented Oct 1, 2013 at 21:18
@ThinkJet - that works, until you have 2 schemas, which may not have the same names in all environments. Which is why my search brought up this page :-) — Roboprog
– Roboprog, Commented Nov 3, 2016 at 0:52

DCookie · Accepted Answer · 2013-10-01 22:49:04Z

Have a look at the documentation of the SQL*PLUS User Guide and Reference.

From the manual:

You can bypass the prompts for values associated with substitution variables by passing values to parameters in a script through the START command. You do this by placing an ampersand (&) followed by a numeral in the script in place of a substitution variable. Each time you run this script, START replaces each &1 in the file with the first value (called an argument) after START filename, then replaces each &2 with the second value, and so forth. For example, you could include the following commands in a script called MYFILE:

SELECT * FROM EMP_DETAILS_VIEW
WHERE JOB_ID='&1'
AND SALARY='&2';

In the following START command, SQL*Plus would substitute PU_CLERK for &1 and 3100 for &2 in the script MYFILE:

 START MYFILE PU_CLERK 3100

When you use arguments with the START command, SQL*Plus DEFINEs each parameter in the script with the value of the appropriate argument.

Collectives™ on Stack Overflow

Passing parameters to oracle script

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related