How to pass a value to a subtitution variable using select script

Question

I have a SQL script in which I declare some substitution variables at the top. The purpose of the script is to create a set of tables and views on a bunch of Oracle schemas when doing multi tenant deployment. In one of the scripts that creates tables, a table space is assigned. Since the table space name varies from tenant to tenant, I want to extract the table space name from the schema and put it in as a substitution variable that I can then use through the script that creates tables and views.

An example:

define VISchema   = FCFVI0

 CREATE TABLE "&VISchema."."FSV_LIST_INFO" 
("LIST_KEY" NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE, 
    "LIST_REFERENCE" VARCHAR2(255 CHAR), 
    "LIST_SOURCE" VARCHAR2(255 CHAR), 
    "LAST_SCREENED" DATE, 
    "LAST_UPDATE" DATE
   ) SEGMENT CREATION IMMEDIATE 
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "&tablespace_nm."   NO INMEMORY ;

I want to assign a value to the tablespace_nm substitution variable using the output from:

select tablespace_name from user_tablespaces;

But I have not figured out how.

If doing:

variable tablespace_nm_1 CHAR;
exec select tablespace_name into :tablespace_nm from user_tablespaces;

and I try to reference that variable as :tablespace_nm it says the tablespace does not exist.

Any help is appreciated

Is it sql-server or oracle ? Those are 2 different databases, please tag the correct ones only. — Koen Lostrie, Jun 22 '21 at 07:55
The syntax _&._ is wrong. You don't need the "." at the end. Also, you cannot bind table names, schema names afaik. You could do it in a pl/sql block using dynamic sql — Koen Lostrie, Jun 22 '21 at 08:06
@KoenLostrie [**substitution** variable](https://docs.oracle.com/en/database/oracle/oracle-database/21/sqpug/using-substitution-variables-sqlplus.html#GUID-0BEEC1D7-876B-495C-9327-17037652D3D2) is an entity of SQL*Plus, it is not a **bind** variable. So syntax is correct — astentx, Jun 22 '21 at 10:30
@astentx thanks for the correction, I was under the impression until now that it was used for bind variables only. But there is a typo in the syntax. As the doc states, **&VARIABLE** will work, however the OP is using the syntax **&VARIABLE.** - note the "." at the end. — Koen Lostrie, Jun 22 '21 at 10:50
@KoenLostrie In *6.3.1 Where and How to Use Substitution Variables* section: *If you wish to append characters immediately after a substitution variable, use a period to separate the variable from the character. For example: `SELECT SALARY FROM EMP_DETAILS_VIEW WHERE EMPLOYEE_ID='&X.5'`* Because it is **substitution** variable, it just replaces part of the text and is processed by SQL*Plus, when bind variable is the data and is processed at parsing time. So in bind case it will be equivalent to `.` which is not a valid syntax — astentx, Jun 22 '21 at 11:28
@KoenLostrie Welcome! It's essential to distinguish them both — astentx, Jun 22 '21 at 11:57

score 1 · Answer 1 · answered Jun 22 '21 at 10:23

Well, if you have that script which contains bunch of tables you're going to create, maybe the simplest option is to open it in any decent text editor and perform search & replace, providing new values.

Otherwise, in my database, your way wouldn't work because user_tablespaces doesn't contain just one tablespace (so your select would fail with too_many_rows):

SQL> select tablespace_name from user_tablespaces;

TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS

Do you really see just one tablespace?

Anyway, for this demonstration, I'll use a where clause to select only one tablespace: users.

In SQL*Plus, you can use substitution variables (documentation is here). Here's how:

SQL> column tablespace_name new_value ts

tablespace_name won't get its value until you select it:

SQL> select tablespace_name from user_tablespaces
  2  where tablespace_name = 'USERS';

TABLESPACE_NAME
------------------------------
USERS

OK; let's create table, providing tablespace name via substitution variable:

SQL> create table test (id number) tablespace &ts;
old   1: create table test (id number) tablespace &ts
new   1: create table test (id number) tablespace USERS

Table created.

SQL>

Looks like it works.

How to pass a value to a subtitution variable using select script

1 Answers1