script

[santo]

hi,

I have created a script to create database.
I need to accept the timezone of the user
in the batch file. So Iam using the Accept
command.

The problem is when the script is running
it does not wait for the user input on
the timezone in the script script1.SQL.
(The Accept command is not working).

It throws an error and continues with the
next script Runsetup.sql.

The error is
------------
Please specify the Timezone (eg: GMT)
Time Zone: .
old 1: Alter database set time_zone = &sTimeZone
new 1: Alter database set time_zone = .
Alter database set time_zone = .
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE






The scripts are as follows:
----------------------------

createdbs.bat
--------------

@echo off


set ORACLE_BASE=e:\\\oracle
set ORACLE_HOME=%ORACLE_BASE%\\\ora92
set ORACLE_DATA=%ORACLE_BASE%\\\oradata
set ORACLE_SID= oradb1
set TMP_DIR=e:\\\Temp\\\sql\\\script

echo Create folder and subfolders for: %ORACLE_SID%
mkdir %ORACLE_BASE%\admin\%ORACLE_SID%
mkdir %ORACLE_BASE%\admin\%ORACLE_SID%\adhoc
mkdir %ORACLE_BASE%\admin\%ORACLE_SID%\bdump
mkdir %ORACLE_BASE%\admin\%ORACLE_SID%\cdump
mkdir %ORACLE_BASE%\admin\%ORACLE_SID%\create
mkdir %ORACLE_BASE%\admin\%ORACLE_SID%\exp
mkdir %ORACLE_BASE%\admin\%ORACLE_SID%\pfile
mkdir %ORACLE_BASE%\admin\%ORACLE_SID%\udump
mkdir %ORACLE_DATA%\%ORACLE_SID%


echo Install oracle Service
%ORACLE_HOME%\bin\oradim.exe -new -sid %ORACLE_SID% -startmode m
%ORACLE_HOME%\bin\oradim.exe -edit -sid %ORACLE_SID% -startmode a
%ORACLE_HOME%\bin\orapwd.exe file=%ORACLE_HOME%\database\PWD%ORACLE_SID%.ora password=change_on_install

echo Create Database: %ORACLE_SID%
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\CreateDB.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\CreateDBFiles.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\CreateDBCatalog.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\JServer.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\ordinst.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\interMedia.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\context.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\xdb_protocol.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\ultraSearch.sql
%ORACLE_HOME%\bin\sqlplus /nolog @%ORACLE_BASE%\admin\%ORACLE_SID%\create\postDBCreation.sql


echo Copy the scripts to the temp folder
xcopy sqlscript %TMP_DIR% /E /V /Q /Y /I

echo processing Script1.SQL script
echo . | %ORACLE_HOME%\bin\sqlplus system/manager @%TMP_DIR%\\\script1.SQL
echo processing Runsetup.sql script
echo . | %ORACLE_HOME%\bin\sqlplus system/manager @%TMP_DIR%\\\Runsetup.sql

echo Deleting all temporary files
del /S /F /Q %TMP_DIR%
rmdir /S /Q %TMP_DIR%
---


script1.SQL
-----------

PROMPT Please specify the Timezone
prompt
ACCEPT sTimeZone char PROMPT 'Time Zone: '
Alter database set time_zone = &sTimeZone;

------------------


thanx.

sam

[gandolf989]

Try using two && signs.

  1* SELECT '&&myvar' FROM dual
LIDXPNN - t31a=> /
old   1: SELECT '&&myvar' FROM dual
new   1: SELECT 'thisisit' FROM dual

'THISISI
--------
thisisit