Implementation code of shell script connecting Oracle Database

Time:2020-8-11

Recently, I studied how to use shell scripts to connect to a database and then execute queries. In this way, for scripts that need to be executed regularly, we can create a crontab to execute them regularly.

Let’s first introduce the installation of the client

Linux environment needs to use sqlplus client to connect to Oracle database. First of all, we need to confirm whether it is installed. If which sqlplus is not installed, you need to install it first. The installation steps are as follows:

Download from the official website of Oracle, and then install the following two modules:


rpm -ivh oracle-instantclient11.2-basic-11.2.0.3.0-1.x86_64.rpm 
rpm -ivh oracle-instantclient11.2-sqlplus-11.2.0.3.0-1.x86_64.rpm

Configure environment variables:
vim /etc/profile

After saving, execute: source / etc / profile

Where is Oracle ා view the Oracle client installation path

 

Enter the client directory

 

Add profile: touch tnsnames.ora
Edit configuration file: VIM tnsnames.ora


DATABASENAME =
 (DESCRIPTION =
 (ADDRESS_LIST =
  (ADDRESS = (PROTOCOL = TCP)(HOST = 127.0.0.1)(PORT = 1521))
 )
 (CONNECT_DATA =
  (SERVICE_NAME = servicename)
 )
 )

After editing and saving, you can use the command line to test whether the connection is successful


sqlplus username/[email protected]

If it is normal, you can connect to Oracle database smoothly.
Let’s start writing our own shell scripts:


#! /bin/bash
sqlplus username/[email protected] >tmp.txt << EOF
set heading off
set line 4000
set WRAP OFF
Select user_id,mobile From txlx_xxx_user_info Where rownum <20;
exit
EOF
sed -i 1,12d tmp.txt
sed -i -e '/selected/,$d' tmp.txt
sed -i -e '/Disconnected/,$d' tmp.txt
sed -i '$d' tmp.txt

Explain it line by line:

#! / bin / bash ා shell script starts, specifying which shell to use
Connect database statement, standard output to tmp.txt , get input from < < EOF
Remove the header
Set row width
Turn off wrap
Query statement
sign out
End of EOF input
Sed delete tmp.txt 1-12 lines of text generated by connecting to the database
Delete the selected text to the last line
Delete the disconnected text to the last line
Delete the last line of text

This article about shell script connection Oracle database implementation code to introduce this, more related shell connection Oracle content, please search the previous articles of developeppaer or continue to browse the related articles below, I hope you can support developeppaer more in the future!

Recommended Today

How to share queues with hypertools 2.5

Share queue with swote To realize asynchronous IO between processes, the general idea is to use redis queue. Based on the development of swote, the queue can also be realized through high-performance shared memory table. Copy the code from the HTTP tutorial on swoole’s official website, and configure four worker processes to simulate multiple producers […]