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 220.127.116.11.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...
By submitting your personal information, you agree that TechTarget and its partners may contact you regarding relevant content, products and special offers.
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.
Dig Deeper on Oracle stored procedures
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.