Oracle SQL statement dynamic schema variable

Question

I have some hundred lines of code to create tables but schema name is hardcoded like SCHEMA_NAME1.TABLE_NAME in all DDL statements. How can I have this as a variable and use in all the places so that we can easily change in a single place for multiple locations of SCHEMA_NAME requirement.Please give your thoughts.

create table SCHEMA_NAME1.TABLE_NAME1(....);
create table SCHEMA_NAME1.TABLE_NAME2(....);
create table SCHEMA_NAME1.TABLE_NAME3(....);

I want something like this

var SCHEMA_NAME_VALUE ;
create table SCHEMA_NAME_VALUE.TABLE_NAME1(....);
create table SCHEMA_NAME_VALUE.TABLE_NAME2(....);
create table SCHEMA_NAME_VALUE.TABLE_NAME3(....);

You will have to write a PL/SQL program to get to that and use dynamic sql. Can't you just use a text editor and replace the instances before creating DDL scripts for different environments? — Incognito
– Incognito, Commented Dec 29, 2015 at 14:55
If you can use SQL*Plus then use substitution variables - create table &SCHEMA_NAME.TABLE_NAME1(....); and execute the script with schema parameter. If the code creates only tables, view or grants you can turn everything into single CREATE SCHEMA AUTHORIZATION command. — Husqvik
– Husqvik, Commented Dec 29, 2015 at 15:07
Using only a single ampersand & for each instance of the schema name would require the user to enter the schema name once for each instance of the substitution variable. Using either a double ampersand && for the first occurrence, or an ACCEPT command at the beginning of the script would allow for entering the value only once. The ACCEPT command is the more advisable method since it ensures you can set the value each time you run the script. You should also include an UNDEFINE at the end. — Sentinel
– Sentinel, Commented Dec 29, 2015 at 15:22
When using substitution (&) variables, don't forget to double up the trailing period since a period is used as an optional termination character for the variable names. The period termination character is required when the variable is name is not followed by white space as is the case above, so you need to double it in order to not lose the period separating the schema name from the object name. — Sentinel
– Sentinel, Commented Dec 29, 2015 at 15:27

Sentinel · Accepted Answer · 2015-12-29 15:36:56Z

4

When using SQL*Plus or SQLcl to run your object creation script, use substitution (&) variables in place of the hard coded schema names, with a leading ACCEPT command and a trailing UNDEFINE command. When the variable has no trailing white space, terminate the variable name with a period (.).

ACCEPT SCHEMA_NAME_VALUE
create table &SCHEMA_NAME_VALUE..TABLE_NAME1(....);
create table &SCHEMA_NAME_VALUE..TABLE_NAME2(....);
create table &SCHEMA_NAME_VALUE..TABLE_NAME3(....);
UNDEFINE SCHEMA_NAME_VALUE

answered Dec 29, 2015 at 15:36

Sentinel

6,4871 gold badge20 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Jericho Over a year ago

But normally without anytools its not useful.

Sentinel Over a year ago

@sunleo if you don't have any tools how do you plan to run it in the first place?

Jericho Over a year ago

This can be done like batch job from java, so I don't think we need some tools in that case.

Collectives™ on Stack Overflow

Oracle SQL statement dynamic schema variable

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related