Ask the Expert

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.

    Requires Free Membership to View

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 first published in May 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
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
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: