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
• Using pipelines for follow-up treatment
• Places to be aware of
• A simple script

1 Sqlplus problems with interactive interface

Sqlplus is the most important official command-line client software for Oracle databases and is an essential tool for DBAs to accomplish almost all of the administrative tasks. However, the interactive interface of Sqlplus is not very friendly, and the input command does not have the history command record function; The default output is more unsightly. In terms of ease of use, sqlplus is really a lot worse than the MySQL client.

Since there is so much inconvenience in the sqlplus interaction pattern, it is better to use it directly in non-interactive mode, and to help with more complex tasks with the Shell toolset provided by the operating system. Sqlplus itself is support for noninteractive use, and is very well supported, in line with the general UNIX design philosophy: read commands from standard input and write the results to standard output .

Two non-interactive ways to use Sqlplus 2

Sqlplus in non-interactive mode, SQL commands are available from two locations: one is through the external file, but through the standard input.

2.1 via external command file

sqlplus 用户名/密码@服务器IP/侦听服务名 @命令文件名

Let's give an example.
The command file name is 1.sql and the contents are as follows:

select count(*) from dba_objects;exit;

The execution commands are:

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

The result is output when the execution is complete.
This way of external command files requires the creation of separate physical files to store commands that are suitable for frequently used high-volume management tasks.

2.2 via standard input

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

echo 命令 | sqlplus 用户名/密码@服务器IP/侦听服务名

This method does not need to include exit in the command, because Sqlplus automatically exits after execution. Here's an example:

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

This executes the result output to the screen, because the SQL statement is a parameter to the Bash command, and the entire command line can be repeated by bash's convenient history. It is also convenient to use the up and down arrows to reuse executed SQL statements.

3 using pipelines for follow-up treatment

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 be aware of

When using an external file, the commands in the file are identical to the interactive use, without fear.

• Avoid bash meta-characters when using echo + pipe input. Be sure to protect your SQL commands with single or double quotes. When you have single quotes in a SQL command, you should choose to use double-quote protection. Such as

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

• Because $ is also treated as a meta character in double quotes, it needs to be escaped

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

• In addition, Sqlplus does not allow sqlplus directives and SQL commands to be mixed 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. For example

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

51 Simple Scripts

While the above command-line approach makes it easy to invoke executed SQL statements through Bash's history function, there is a lot more to the command line than SQL statements. So, writing a small script (with a file name of SQL) makes the call very convenient, as follows:

#!/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;‘

This is very neat, and will not write down Oracle's account information in the history command, it is relatively safe.

Non-interactive use of Sqlplus