Variable Substitution

oracletoad

I am working in TOAD for Oracle and am trying to use variable substitution in my coding.

I have been able to get variable substitution to work in the code body pressing F5:

Define Value = '1.5'
Create table XX_DELETEMEEE as Select 
percent * To_Number('&&Value') Test
from Source_Table;

I have been unable to make the variable work in the same way for text as table names or column names:

Define PROJECT = 'P747'
Create table XX_DELETEMEEE as Select 
percent (To_CHAR('&&Project'))
from Source_Table;

The error for this code has 'percent' as an invalid identifier.

Define PROJECT = 'P747';
Create table &&Project||_MU as Select 
percent
from Source_Table;

The error for this code has table name as an invalid option.

Any ideas on how to make this work while not straying to far in methodology?

Best Answer

define/& is a simple text substitution mechanism that does not know anything about sql. And sql and the Oracle Database server do not know anything about the text processing mechanism. So the text that results from the 'define/&'substitutuin must be valid sql statement. Otherwise you get an error. 

 CREATE TABLE P747||_MU as Select percent from Source_Table

is not a valid sql statement.

 CREATE TABLE P747_MU as Select percent from Source_Table

is the valid statement, an so

Define PROJECT = 'P747';
Create table &&Project._MU as Select 
percent
from Source_Table;

Avariable name like &&Project must be followed by a whitespace or and end of file or by a dot (.). The dot is removed after variable substitution.

Define PROJECT = 'P747';
Create table &&Project_MU as Select
percent
from Source_Table;

will not work, because the variable is &&Project_MU and not &&Project.

you mix up simple text substituion in the Toad editor ( 'define/&) with sql string functions (to_char, ||). That are different things.

Related Question