Q

Windows variables not interpreted in SQL script

I am trying to create a temporary user and tablespace for a conversion process. I am using a script where I reference a Windows environmental variable to specify the conversion directory where the tablespace is to be created. The Windows variables are:
 set CONV_DRIVE=H:
 set CONV_DIR=%CONV_DRIVE%4.6_Conversion
Within 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 9.2.0.1.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_file
In 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 last published in May 2005

PRO+

Content

Find more PRO+ content and other member only offers, here.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close