title: How to pass a value to a subtitution variable using select script
body: <p>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.</p>
  <p>An example:</p>
  <pre><code>define VISchema   = FCFVI0
  
   CREATE TABLE &quot;&amp;VISchema.&quot;.&quot;FSV_LIST_INFO&quot; 
  (&quot;LIST_KEY&quot; NUMBER GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE, 
      &quot;LIST_REFERENCE&quot; VARCHAR2(255 CHAR), 
      &quot;LIST_SOURCE&quot; VARCHAR2(255 CHAR), 
      &quot;LAST_SCREENED&quot; DATE, 
      &quot;LAST_UPDATE&quot; 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 &quot;&amp;tablespace_nm.&quot;   NO INMEMORY ;
  </code></pre>
  <p>I want to assign a value to the tablespace_nm substitution variable using the output from:</p>
  <pre><code>select tablespace_name from user_tablespaces;
  </code></pre>
  <p>But I have not figured out how.</p>
  <p>If doing:</p>
  <pre><code>variable tablespace_nm_1 CHAR;
  exec select tablespace_name into :tablespace_nm from user_tablespaces;
  </code></pre>
  <p>and I try to reference that variable as <code>:tablespace_nm</code> it says the tablespace does not exist.</p>
  <p>Any help is appreciated</p>