Oracle database is now a mature and popular RDBMS which is used by lots of enterprises. With its evolution, many utilities and applications are developed around it as well. Now Oracle database also provides cloud support. As a DBA, managing Oracle database is daily routine work and some tools may be used frequently. One of them is sqlplus, the command line tool for executing SQL command.
sqlplus is installed while the Oracle database is installed. To start sqlplus, one just needs to open a terminal and then type sqlplus. Then it will connect to the default schema and will prompt you to enter the username and password. Post that, SQL command can be executed.
This is easy to test or verify something as you can easily and interactively get feedback. Sometimes some applications may be developed to automate this kind of process. Apparently developers can use the database drivers provided by the vendor for different programming languages to interact with the database. However, system administrators may only know shell scripting and they want to automate the process using sqlplus only. This is also feasible.
To execute a SQL command using sqlplus at command line, below statement can be executed.
echo ALTER SESSION SET CURRENT_SCHEMA = TESTER; | sqlplus / as sysdba
How about executing multiple statements at command line? People will think about putting the statements in a script and then execute the script. Yes, you can do that.
Assume there is a select_query.sql which contains following statements.
ALTER SESSION SET CURRENT_SCHEMA = TESTER; SELECT * FROM DEPT; EXIT;
Then execute the script with below command(/ as sysdba is to authenticate as sysdba which has privileges)
sqlplus -S / as sysdba @select_query.sql
In addition to this frequently used method, there is another way to execute multiple statements at one line.
(echo ALTER SESSION SET CURRENT_SCHEMA = TESTER; & echo SELECT * FROM DEPT;) | sqlplus -S / as sysdba
This method is better used to execute two or three statements at one line. If there are too many statements to be executed at once, a script would be a better choice.
