Access Oracle setenv parameters within sqlplus

Question

I'd like to write an SQL script to create a database. I'd like to parametrise it to be able to reuse it for future databases. As a base I'd like to use a script from Oracle documentation page:

CREATE DATABASE mynewdb
   USER SYS IDENTIFIED BY sys_password
   USER SYSTEM IDENTIFIED BY system_password
   LOGFILE GROUP 1 ('/u01/app/oracle/oradata/mynewdb/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/app/oracle/oradata/mynewdb/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/app/oracle/oradata/mynewdb/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/app/oracle/oradata/mynewdb/system01.dbf' SIZE 325M REUSE
   SYSAUX DATAFILE '/u01/app/oracle/oradata/mynewdb/sysaux01.dbf' SIZE 325M REUSE
   DEFAULT TABLESPACE users
      DATAFILE '/u01/app/oracle/oradata/mynewdb/users01.dbf'
      SIZE 500M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/app/oracle/oradata/mynewdb/temp01.dbf'
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs
      DATAFILE '/u01/app/oracle/oradata/mynewdb/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

To run Oracle-sqlplus one has to set some system variables, like ORACLE_SID.

How can I access this ORACLE_SID from within the script? Eg. I'd like to replace CREATE DATABASE mynewdb with CREATE DATABASE ORACLE_SID
In my case, '/u01/app/oracle/oradata/mynewdb/redo01.log' is equal to '/oradata/ORACLE_SID/redo01.log' -> how can I embed this variable in the statement?

I hope my question is clear enough. Any hints appreciated.

Do you have to get the values from the client environment, or could you prompt for them? You can get arbitrary variables, but it might be excessive. Depending on how you plan to call this you could also pass values on the command line, and use positional substitution variables. Can you narrow down your requirement? — Alex Poole
Thank you @AlexPoole. Prompting would be good enough, there aren't many values to pass. I didn't know there is such possibility, thank you for pointing out the right direction. — mmm

TenG TenG · Accepted Answer · 2015-06-06T22:41:30

Alex has given you the best practical help, but for anyone interested or for reference, see below.

If ever you need to reference shell environment variable in sqlplus, the method I use is to run a script that translates shell variables to sqlplus DEFINE statements, e.g.

cat shell2define.sh
set | grep '=' | sed 's/^/define /' > shell.sql

Then in sqlplus:

SQL> ! ./shell2define.sh
SQL> @shell.sql
SQL> define

Now you can refer to the shell variables as you would any sqlplus DEFINEd variable, e.g. &ORACLE_SID. The last "define" command just lists all the variables. Extend the scripts to remove/handle special variables like $_, and ones with quotes, or just use it to include variables you require. Don't forget also the use of $ORACLE_HOME/sqlplus/admin/glogin.sql to invoke this automatically should it be required every time.

Access Oracle setenv parameters within sqlplus

1 Answers