How to execute sqlplus commands on cmd.exe?

Question

My sqlplus command doesnt finish , it wait on password line,it dont give me error.

This code doesnt problem when i open new cmd window and paste this code ! everything is ok, but i cant run on c# using process();

My sqlplus command text:

Set ORACLE_SID=prod
Set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
sqlplus -s 
sys as sysdba
password
shutdown immediate
startup mount
recover standby database until cancel;
cancel
alter database open read only;
exit;
exit;

I try this :

C:\cmd.cmd
*********************************************
Set ORACLE_SID=prod
Set ORACLE_HOME=C:\oracle\product\10.2.0\db_1
sqlplus -s 
sys as sysdba
manager
select * from dual;
exit;
*********************************************

Process process = new Process();
ProcessStartInfo startInfo = new ProcessStartInfo();
startInfo.FileName = @"C:\cmd.cmd";
startInfo.RedirectStandardInput = true;
startInfo.RedirectStandardOutput = true;
startInfo.CreateNoWindow = true;
startInfo.UseShellExecute = false;

process = Process.Start(startInfo);

//output
string BatProcessResult = process.StandardOutput.ReadToEnd();

Output:

D:\AppPath\bin\Debug>Set ORACLE_SID=prod

D:\AppPath\bin\Debug>Set ORACLE_HOME=C:\oracle\product\10.2.0\db_1

D:\AppPath\bin\Debug>sqlplus -s

D:\AppPath\bin\Debug>sys as sysdba;

Solution 1

Try

sqlplus -L username/pw@db @ fileWithCommands.sql

or

set ORACLE_SID=...
sqlplus -L / as sysdba

This will not wait for a username and password but log you in with the given credentials. If they are wrong, sqlplus will exit immediately with an error. If the credentials are correct, sqlplus will run all the commands in the given file, one after the other.

Solution 2

You forgot to append the user role to your command. Try to use this one

sqlplus <username>/<password>@<host>:<port>/<sid> <db_role> @<script>

for example:

sqlplus sys/oracle@localhost:1524/ORCL AS SYSDBA @myscript.sql

Comments

[Ed Gibbs]

I don't remember exactly, but it's something like this in script: sqlplus -s "sys@wherever/password as sysdba". The double quotes are needed because of the spaces.

[Mennan]

thx i found solution , syntax username/pw@db

[Ed Gibbs]

Excellent - I'm glad you found it! The double quotes may be a unix/linux thing. Note that Oracle will accept username/pw@db or username@db/pw.