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?

Sample

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

What output from `ECHO About to exit %errorlevel%` ? Let's say `1017` if an _invalid credential_ error occurs? Then `if %errorlevel% EQU 1017 goto :back` could help — JosefZ, Jan 28 '15 at 07:23
I cannot ECHO because I am trying to connect through a .SQL file. — ladiesman1792, Jan 28 '15 at 08:37
Im working on something that looks like this: `@echo off sqlplus &&user@&&db /nolog @C:\Users\njediaz\Desktop\conn.sql ECHO About to exit. timeout t/ 30 :pause` — ladiesman1792, Jan 28 '15 at 08:37
Once more: replace `ECHO About to exit` line with `ECHO About to exit %errorlevel%` in your batch script. Run the changed script, put erroneous username or password and then tell us what you can see as output. There should be a number — JosefZ, Jan 28 '15 at 09:19
What about if changing to `EXIT 12345` in your `conn.sql` file? Run batch, put _right_ credentials. What output now? — JosefZ, Jan 28 '15 at 09:32

score 2 · Answer 1 · edited May 23 '17 at 11:43

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.

It's not working for me. The command line still closes after I entered invalid credentials. — ladiesman1792, Jan 28 '15 at 07:24
@ladiesman1792 - the `-L` flag stops SQL*Plus prompting again, but that's probably what you want since you want to get the three elements separately, so you'd need a loop in the batch script to prompt and run repeatedly. However, putting the credentials on the command line isn't as secure as they're visible to other processes. — Alex Poole, Jan 28 '15 at 07:57
It's not returning to ':Credentials'. Still closes when I input invalid credentials. — ladiesman1792, Jan 30 '15 at 03:24

score 0 · Accepted Answer · edited May 23 '17 at 12:12

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?

Command line SQL invalid credentials

2 Answers2

Linked