Today's Question:  What weekend projects have you created?        GIVE A SHOUT

Technical Article => Database =>  Oracle

Oracle database to execute multiple statements at command line

  sonic0002      2016-12-09 20:11:28      2,663    0

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.

ORACLE ORACLE DATABASE SQLPLUS

  SAVE AS PDF   MARK AS READ   MARK AS IMPORTANT

Share on Facebook  Share on Twitter  Share on Google+  Share on Weibo  Share on Reddit  Share on Digg  Share on Tumblr    Delicious

  RELATED


  0 COMMENT


No comment for this article.


  WRITE ARTICLE

What an embarrassment

By sonic0002