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

Answer 1

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.