Command line SQL invalid credentials

Question

How can I make the USERNAME, PASSWORD and DATABASENAME INPUT reappear when I input invalid values or when the SQL did not connect?

After the above image, the COMMAND LINE will just close.

This is the batch(.bat) file.

@echo off

sqlplus /nolog @C:\Users\myuser\Desktop\conn.sql

ECHO About to exit.

timeout t/ 30

:pause

And this is the conn.sql file

conn &&username@&&DBNAME


SPOOL C:\Sample.log
SELECT 1 FROM DUAL /
SPOOL OFF

EXIT

Answer 1

According to Oracle 11g – SQLPlus Command Line Options and Starting SQLPlus it is possible to specify user name, password and database already on command line of sqlplus.exe.

I don't have sqlplus.exe to test it out, but something like below should work with appropriate adapting content of file conn.sql.

@echo off
setlocal
set "SqlUserName="
set "SqlPassword="
set "SqlDatabase="
:Credentials
set /P "SqlUserName=Enter username: "
set /P "SqlPassword=Enter password: "
set /P "SqlDatabase=Enter database: "
sqlplus.exe -L "%SqlUserName%/%SqlPassword%@%SqlDatabase%" /nolog @C:\Users\myuser\Desktop\conn.sql
if errorlevel 1 goto Credentials
echo About to exit.
timeout /t 30
endlocal

You can enhance this batch code further for example with code to check if batch user has really entered user name, password and database.

The values of the 3 environment variables can be re-used again on other batch lines inserted above command endlocal.

Edit: Added a simple loop in case of sqlplus.exe exits with any code greater or equal 1. The user of the batch code has the possibility to just hit key RETURN or ENTER to use the user name, password or database name entered already before. The final solution must be most likely better than what is posted here as entering the credentials once again should be only done on error accessing the database and not on other errors. So the batch files has to distinguish between the various exit codes returned by SQL*PLUS.

See also the answers on Stack Overflow questions Sql*plus always return exit code 0? and How do I capture a SQLPlus exit code within a shell script?

The only difference is that in a Unix shell script if [ $? != 0 ] is equivalent to if errorlevel 1 in a Windows batch file (if exit code is not negative which is usually true). See the Microsoft support articles Testing for a Specific Error Level in Batch Files and Correct Testing Precedence of Batch File ERRORLEVELs about evaluating exit codes with if errorlevel.

Answer 2

Batch File

@echo off
setlocal
set "SqlUserName="
set "SqlPassword="
set "SqlDatabase="
:Credentials
set /P "SqlUserName=Enter username: "
set /P "SqlPassword=Enter password: "
set /P "SqlDatabase=Enter database: "
@(
echo whenever sqlerror exit failure
echo connect %SqlUserName%/%SqlPassword%@%SqlDatabase%
echo select * from dual;
echo exit 
) | sqlplus.exe -s /nolog 
if errorlevel 1 goto Credentials
if errorlevel 0 sqlplus /nolog  @C:\Users\myuser\Desktop\conn.sql 
echo About to exit.
timeout /t 30
endlocal

Conn.sql

SPOOL C:\Sample.log
SELECT 1 FROM DUAL /
SPOOL OFF

EXIT

Thanks to Mofi and Alex: How do I prompt input for database on command line?