set CONV_DRIVE=H: set CONV_DIR=%CONV_DRIVE%4.6_ConversionWithin my SQL script:
DEFINE CONV_USER = CGVTS45 DEFINE CONV_TS = CGVTS45_TSP CREATE TABLESPACE &&CONV_TS DATAFILE '%CONV_DIR%data%CONV_TS.dbf' SIZE 100 M DEFAULT STORAGE ( INITIAL 500000 NEXT 50000 MINEXTENTS 1 MAXEXTENTS 121 PCTINCREASE 1 ) /When I run the script, it is obvious that the variables are not being interpreted. I am new to Oracle on Windows and am not sure how to proceed. I am using Oracle 126.96.36.199.0 on Windows 2000 5.00.2195 SP3.
Instead of defining variables as you have, use parameters to your SQL script. For instance, assume your script is called CREATE_TS.SQL. You can call the script when envoking SQL*Plus as follows:
sqlplus system/manager @create_ts.sql my_ts my_dir my_fileIn this case, "my_ts" is the first parameter to the script and "my_dir" is the second parameter to the script. You can then use the &1 and &2 variables in your script to correspond to the first and second script parameters respectively. How to handle the third parameter should be apparent. So your script might look like this:
CREATE TABLESPACE &1 DATAFILE '&2\&3' SIZE 100M;The SQL*Plus User's Guide and Reference contains information on how to handle processing of scripts in SQL*Plus. You can find this document here.
This was first published in May 2005