Oracle SQL statement dynamic schema variable

问题:

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(....);

回答1:

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