Non-interactive use of Sqlplus

• Sqlplus Problems in Interactive interface
• Two non-interactive ways to use Sqlplus
  • 1 via external command file
  • 2 via standard input
• Use the pipe to do the processing
• Places to watch
• A simple script

1 Sqlplus problems with interactive interface

Sqlplus is the most important official command line client software for Oracle database. is a must-have tool for DBAs to complete almost all management tasks. However. The Sqlplus interface is not very friendly and does not have a History command recording function when the command is entered. The default output is even more offensive.

In terms of ease of use, Sqlplus is indeed a lot worse than mysqlclient.

Since there are so many inconveniences in Sqlplus interactive mode. So instead of using it directly using non-interactive mode, use the Shell toolset provided by the operating system to help complete more complex tasks. Sqlplus itself is support for non-interactive use, and is well supported, in line with the general UNIX design philosophy: read commands from standard input. Write the results to the standard output .

Two non-interactive ways to use Sqlplus 2

Sqlplus in non-interactive mode, it can provide SQL commands from two places: the first is through the external file, but through the standard input.

2.1 via external command file

sqlplus username/[email protected]/侦听服务名 @命令文件名称

Here's an example.
The command file name is called 1.sql. The contents are as follows:

select count(*) from dba_objects;exit;

The Run command is:

sqlplus sys/***@172.16.2.190/xgdb.db001.xigang @1.sql

The result will be output when it is finished running.
The way this external command file is. There is a need to create separate physical files to store commands. For high-volume management tasks that are often used.

2.2 via standard input

Now that Sqlplus reads the command from the standard input, it is able to use the pipeline to send the command to it.

echo 命令 | sqlplus username/[email protected]/侦听服务名

Such a way does not need to include exit in the command, because the Sqlplus after running the active exit. Here is a sample example:

echo ‘select count(*) from dba_objects;‘ | sqlplus sys/***@172.16.2.190/xgdb.db001.xigang as sysdba

This results in output to the screen after running. Because the SQL statement is the parameter of the Bash command. The entire command line can be called repeatedly through Bash's convenient history. It is also convenient to use the up and down arrows to repeatedly use the running SQL statements.

3 using pipelines to do the processing

Now that sqlplus output the results to the standard output, you can use a pipeline to get the desired output using a word processing tool such as Sed,awk.

echo -e ‘set pagesize 0\nselect table_name,owner from dba_tables;‘ | sqlplus -S sys/***@172.16.2.190/xgdb.db001.xigang as sysdba | awk ‘{printf("%-10d%-30s%-20s\n",NR,$1,$2);}‘

4 Places to note

When using an external file, the commands in the file are exactly the same as the interactive use, without worrying about it.

• Avoid bash meta-characters when using echo + pipe input. Be sure to protect the SQL command with either a single or double-lead.

  When there is a single quote in the SQL command. You should choose to use double-lead protection. Such as

  echo  "insert into t1(name) values(‘奥巴马‘);" | sqlplus ...

• Because $ is also treated as a meta-character in a double-argument. So it needs to be escaped.

  echo "select * from v\$nls_valid_values where parameter=‘LANGUAGE‘;" | sqlplus ...

• In addition, Sqlplus does not agree to mix sqlplus directives with SQL commands on a single line.

  and the SQL command must end with; This must be turned on by using the ECHO-E option to interpret "\ n" as a newline character.

  Like what

  echo -e ‘set pagesize 0\nset linesize 100\nselect * from dba_objects;‘ | sqlplus ...

51 Simple Scripts

The above command-line mode, although it is convenient to call through the Bash history function of the SQL statements, but the command line in addition to the SQL statement has a lot of other content, it is cumbersome. So. Writing a small script (a file name called SQL) makes it convenient to call, such as the following:

#!/bin/bash-if[$# -ne 1] Then    Echo "Usage:  $ ' SQL statement '"ElseSql="Set pagesize 0\nset linesize 300\n"    Echo - e "$SQL"    Echo - e "$SQL"| Sqlplus-s sys/***@172.16.2.190/xgdb.db001.xigang as Sysdbafi

This is called as follows.

sql ‘select * from dba_objects;‘

It looks neat. It also does not record Oracle's account information in the history command, and is relatively secure.

Non-interactive use of Sqlplus