title: Oracle PL/SQL how to reassign the result of a substitution variable to a new variable?
body: <p>I am having a small issue trying to use substitution variables for table names.  When I run the below code with a <code>QTR_END</code> date of <code>31-DEC-2009</code>, I get an error because instead of <code>QUARTER = 4</code>, and <code>ACCYEAR = 2009</code>, it tries to create a table with the name of:</p>
  
  <p><code>"ZZ_PLANNING_DATA_TO_NUMBER(TO_CHAR(TO_DATE('&amp;&amp;QTR_END','DD-MM-YYYY'), 'Q'))QTO_NUMBER(EXTRACT (YEAR FROM TO_DATE('&amp;&amp;QTR_END','DD-MM-YYYY')))"</code></p>
  
  <p>Of course I am trying to create a table <code>"ZZ_PLANNING_DATA_4Q2009"</code></p>
  
  <p>I don't know how to force the substitution variables to substitute their executed value, instead of just substituting the code. ?</p>
  
  <pre><code>ACCEPT QTR_END DATE PROMPT 'Enter the last day of the period you wish to run (i.e.: 30-JUN-2014).';
  
  DEFINE QUARTER = TO_NUMBER(TO_CHAR(TO_DATE('&amp;&amp;QTR_END','DD-MM-YYYY'), 'Q'));
  DEFINE ACCYEAR = TO_NUMBER(EXTRACT (YEAR FROM TO_DATE('&amp;&amp;QTR_END','DD-MM-YYYY')));
  
  DEFINE BACKUP_TABLE_NAME = "ZZ_PLANNING_DATA_&amp;&amp;QUARTER.Q&amp;&amp;ACCYEAR";
  CREATE TABLE &amp;&amp;BACKUP_TABLE_NAME COMPRESS NOLOGGING
  AS SELECT * FROM ZZ_PLANNING_DATA;
  </code></pre>