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

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

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.

0 comments

Oldest 

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:

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close