Oracle sqlplus Login Failure Handling in Shell Script

 Product: Oracle RDBS

Command: sqlplus, shell script

Overview

I would like to document how to create a shell script that will report whether the username credential that stores in a file can successfully login to Oracle DB, or filed.

Without specifying any sqlplus parameter, sqlplus will always return exit code of 0 to the shell script.  I have seen people uses text processing utility to search for the login failure command to detect login failure, but they should use parameter "/l" to do that.

Moreover, in a shell script, I will suggest to turn off the login banner to make the output less verbose by using parameter "/s"

Instruction

Suggested command is "sqlplus /s /l username@TNS"

To automate the password, my suggested command is "echo [password] | sqlplus /s /l username@TNS"

Later, the shell script can uses the OS return code's build-in parameter $? with following meaning:

• 0 - login successful
• 1 - login failed