Shulou(Shulou.com)06/01 Report--

This article describes how to judge the results of sql statements executed by sqlplus.

Environmental preparation

Create a docker-based demo environment using a stripped-down version of Oracle. For more information, please see:

Https://www.jb51.net/article/153533.htm

common problem

When you execute a sql statement in sqlplus, you will encounter two problems if you call it directly from the command line:

Question 1: need for interactive input problem 2: the judgment of the result cannot be confirmed by the return value.

Solution method

In script calls, the solution is as follows

Problem 1 can be solved through the Here Document mentioned earlier. Question 2, you can judge the output, because the error code of Oracle basically starts with ORA-, so you can basically confirm whether there is an error according to whether the output contains ORA-,.

Execution result judgment example

Here, the command line is used for verification, and the script can be used to integrate with sqlplus by copying it directly into the script.

Oracle@e871d42341c0:~$ sqlplus system/liumiao123@XE delete from student; > select * from student; > insert into student values (1001, 'liumiaocn'); > insert into student values (1001,' liumiao'); > insert into student values (1003, 'michael'); > commit; > select * from student; > EOFSQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 22 05:18:51 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL > Name Null? Type-STUID NOT NULL NUMBER (4) STUNAME VARCHAR2 (50) SQL > 3 rows deleted.SQL > no rows selectedSQL > 1 row created.SQL > insert into student values (1001 'liumiao') * ERROR at line 1:ORA-00001: unique constraint (SYSTEM.SYS_C007024) violatedSQL > 1 row created.SQL > Commit complete.SQL > STUID STUNAME- 1001 liumiaocn 1003 michaelSQL > Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0 -64bit Productionoracle@e871d42341c0:~$ echo $? 0oraclefue871d42341c0virtual $

As can be seen from the above, an error occurred in the sql statement of the three-line insert due to the repetition of the primary key in the second line, but the final result could not be judged by using the command line method. This is a common scenario in the console mode, such as sftp or ftp. The commonly used corresponding method cannot be judged by the returned value, but can only be judged by output.

Output information

The output is divided into standard output and standard error, and the FD of input and output are:

Standard input: 0 standard output: 1 standard error: 2

Next, let's take a look at which of the above information is standard output and which is standard error:

Oracle@e871d42341c0:~$ sqlplus system/abcd1234@XE output.info > desc student > delete from student; > select * from student; > insert into student values (1001, 'liumiaocn'); > insert into student values (1001,' liumiao'); > insert into student values (1003, 'michael'); > commit; > select * from student > EOForacle@e871d42341c0:~$ oracle@e871d42341c0:~$ cat output.errororacle@e871d42341c0:~$ oracle@e871d42341c0:~$ cat output.infoSQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 22 05:24:44 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL > Name Null? Type-STUID NOT NULL NUMBER (4) STUNAME VARCHAR2 (50) SQL > 2 rows deleted.SQL > no rows selectedSQL > 1 row created.SQL > insert into student values (1001 'liumiao') * ERROR at line 1:ORA-00001: unique constraint (SYSTEM.SYS_C007024) violatedSQL > 1 row created.SQL > Commit complete.SQL > STUID STUNAME- 1001 liumiaocn 1003 michaelSQL > Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit Productionoracle@e871d42341c0:~$

You can see that the error information is all in the standard output, and there is no information in the standard error.

Redirect standard output and error judgment

Although you can see that there is no information in the standard error in the above information, the solution given here is the error control corresponding to the common console mode. In order to ensure that the standard error information is not left out, you need to redirect the standard error and redirection to the standard output, as shown in bshell:

> output file name 2 > & 1

Combined with the examples in this article, the usage is as follows:

Oracle@e871d42341c0:~$ sqlplus system/abcd1234@XE-1 > desc student > delete from student; > select * from student; > insert into student values (1001, 'liumiaocn'); > insert into student values (1001,' liumiao'); > insert into student values (1003, 'michael'); > commit; > select * from student; > EOForacle@e871d42341c0:~$ oracle@e871d42341c0:~$ cat output.infoSQL*Plus: Release 11.2.0.2.0 Production on Mon Oct 22 05:29:31 2018Copyright (c) 1982, 2011, Oracle. All rights reserved.Connected to:Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit ProductionSQL > Name Null? Type-STUID NOT NULL NUMBER (4) STUNAME VARCHAR2 (50) SQL > 2 rows deleted.SQL > no rows selectedSQL > 1 row created.SQL > insert into student values (1001 'liumiao') * ERROR at line 1:ORA-00001: unique constraint (SYSTEM.SYS_C007024) violatedSQL > 1 row created.SQL > Commit complete.SQL > STUID STUNAME- 1001 liumiaocn 1003 michaelSQL > Disconnected from Oracle Database 11g Express Edition Release 11.2.0.2.0-64bit Productionoracle@e871d42341c0:~$

Result judgment

Use grep to confirm the existence of ORA--related information

Oracle@e871d42341c0:~$ grep ORA- output.info

ORA-00001: unique constraint (SYSTEM.SYS_C007024) violated

Oracle@e871d42341c0:~$ echo $?

0

Oracle@e871d42341c0:~$

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support. If you want to know more about it, please see the relevant links below.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.