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
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.
-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.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?
ECHO About to exit %errorlevel%? Let's say1017if an invalid credential error occurs? Thenif %errorlevel% EQU 1017 goto :backcould help@echo off sqlplus &&user@&&db /nolog @C:\Users\njediaz\Desktop\conn.sql ECHO About to exit. timeout t/ 30 :pauseECHO About to exitline withECHO 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 numberEXIT 12345in yourconn.sqlfile? Run batch, put right credentials. What output now?