Shell script Operations Oracle Database __python

data is extracted to a temporary table (stored procedure test (NUM) performs this operation), writes data to TXT file in the table, and finally packages txt zip

I used to work with code, and everything I did was about using code, so I wrote the code from the start:

1: Read the configuration profile (which has written the number to be manipulated) Excel

Emptyimport JXL.
Cell; Import JXL.
Sheet; Import JXL.

workbook;
Import Java.io.File;
Import java.util.ArrayList;

Import java.util.List;
    Dmuser public class Load_excl {List lis = new ArrayList ();
            Public List findlist (String str) {try {workbook book = Workbook.getworkbook (new File (str));
            Get first Worksheet object Sheet Sheet = book.getsheet (0);
            Gets the cell System.out.println of the first row of the first column ("Data add to List Begin");
                for (int i = 0; i < i++) {int stat = 3;
               Cell cell1 = Sheet.getcell (0, I);

                Cell cell2 = Sheet.getcell (3, i + 2);
               String result = Cell1.getcontents ();

                String result2 = cell2.getcontents (); if (result = NULL | | result = = "" | |).
                Equals (Result)) {break;
            } lis.add (Result);
            SYSTEM.OUT.PRINTLN ("Add Data to List End");
 Book.close ();       catch (Exception e) {System.out.println (e);

    return LIS;
             public static void Main (string[] args) {load_excl a=new load_excl ();
             List abc= a.findlist ("D://batchno.xls");

        for (String s:abc) {System.out.println (s);
 }




    }
}

2: Execute the stored procedure

Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;

Import java.sql.SQLException; /** * @Author: Smile [smile_dip@163.com] * @Description: * @Date; 2017/9/9 14:54 */public class oracle125 {public void Add (String li) {string driver = "Oracle.jdbc.driver.Oracl

    Edriver ";

    String url = "Jdbc:oracle:thin: @ip: 1521:ods";

    String user = "user name";

    String pwd = "password";

    Connection conn = null;


    CallableStatement cs = null;

        try {class.forname (driver);
        conn = drivermanager.getconnection (URL, user, pwd);

        p_119 (): Stored Procedure cs = Conn.preparecall ("{Call p_119 (?)}");
        Cs.setstring (1, Li);

    Cs.execute ();

    catch (SQLException e) {e.printstacktrace ();

    catch (Exception e) {e.printstacktrace ();

            Finally {try {if (cs!= null) {cs.close ();

             } if (conn!= null) {   Conn.close ();
 The catch (SQLException e) {}}}}

3: Execute the shell script

Emptyimport ch.ethz.ssh2.ChannelCondition;
Import ch.ethz.ssh2.Connection;
Import ch.ethz.ssh2.Session;
Import Ch.ethz.ssh2.StreamGobbler;

Import Org.apache.commons.io.IOUtils;
Import java.io.IOException;
Import Java.io.InputStream;

Import Java.nio.charset.Charset; /** * @Author: Smile [smile_dip@163.com] * @Description: * @Date;
      2017/9/9 11:52 * * public class Remoteshellexecutor {private Connection conn;
      /** Remote Machine IP */private String IP;
  /** User name */private String osusername;
    /** Password */private String password;

      Private String charset = Charset.defaultcharset (). toString ();

    private static final int time_out = 1000 * 5 * 60; /** * constructor * @param IP * @param usr * @param pasword/Public Remoteshellexe
               Cutor (string ip, String usr, string pasword) {this.ip = IP;
                This.osusername = usr;
             This.password = Pasword;
 }


              /**     * Login * @return * @throws IOException/Private Boolean login () throws IOException {
                  conn = new Connection (IP);
                  Conn.connect ();
             Return Conn.authenticatewithpassword (osusername, password); /** * Execute Script * * @param cmds * @return * @throws Exception * * PU
               Blic int exec (String cmds) throws Exception {inputstream = null;
                InputStream STDERR = null;
                 String outstr = "";
                 String outerr = "";
                  int ret =-1;
                            try {if (login ()) {//Open a new {@link session} in this connection
                              Session session = Conn.opensession ();
                              Execute a command on the remote machine.

                  Session.execcommand (CMDS);          StdOut = new Streamgobbler (Session.getstdout ());

                             outstr = Processstream (StdOut, CharSet);
                             STDERR = new Streamgobbler (Session.getstderr ());

                             Outerr = Processstream (STDERR, CharSet);

                             Session.waitforcondition (Channelcondition.exit_status, time_out);
                             System.out.println ("outstr=" + outstr);

                              System.out.println ("outerr=" + outerr);
                        ret = Session.getexitstatus ();
                      else {throw new Exception ("Logon remote machine failed" + IP);//Custom exception class implementation slightly}
                              finally {if (conn!= null) {conn.close ();
                         } ioutils.closequietly (StdOut);
                  ioutils.closequietly (STDERR);   return ret; }/** * @param in * @param charset * @return * @throws IOException * @th Rows Unsupportedencodingexception */private string Processstream (InputStream in, string charset) throw
               S Exception {byte[] buf = new byte[1024];
                 StringBuilder sb = new StringBuilder ();
                      while (In.read (BUF)!=-1) {Sb.append (new String (BUF, CharSet));
             return sb.tostring ();
 }

         

}

4: The whole call loop

Import Java.io.BufferedReader;
Import Java.io.InputStreamReader;
Import java.sql.CallableStatement;
Import java.sql.Connection;
Import Java.sql.DriverManager;
Import java.util.ArrayList;

Import java.util.List; /** * @Author: Smile [smile_dip@163.com] * @Description: * @Date; 2017/9/9 14:15 */public class Shelltest {public static void main (string[] args) throws Exception {//Company
        Receive Oracle//Oracleconnect con=new oracleconnect ();
        Write your code here//reads the batch number in Oracle LOAD_EXCL excl =new LOAD_EXCL ();
        oracle125 orcl125 =new oracle125 ();
        List lis=new ArrayList ();
        Lis=excl.findlist ("D://batchno.xls");
            Circular batch number for (String li:lis) {//Set execution state System.out.println ("Current lot number:" +li);
            Connect Oracle, execute stored procedure System.out.println ("Data warehousing");
            Orcl125.add (LI);
            SYSTEM.OUT.PRINTLN ("Data End");
Execute shell Script Thread.Sleep (10*1000);
            Remoteshellexecutor executor = new Remoteshellexecutor ("IP", "username", "password");
            The execution mytest.sh parameter is the Java Know dummy String abc= "/picclife/tools/ciitc/119.sh" +LI;

            SYSTEM.OUT.PRINTLN (Executor.exec (ABC));

       System.out.println (li+ "Batch Complete");
                /* Try {String shpath= "/home/felven/word2vec/demo-classes.sh";
                Process PS = Runtime.getruntime (). exec (Shpath);
                int exitvalue= ps.waitfor ();
                    if (0!=exitvalue) {isok= "no";
                    System.err.println ("Call Shell Failed.errorcodeis:" +exitvalue);
                Stop looping execution of the break;
                BufferedReader br = new BufferedReader (New InputStreamReader (Ps.getinputstream ()));
                StringBuffer sb = new StringBuffer ();
                String Line; while (line = Br.readline ())!= null) {Sb.append (line). Append("\ n");
                String result = Sb.tostring ();
            SYSTEM.OUT.PRINTLN (result);
            catch (Exception e) {e.printstacktrace ();
 }
            */
        }

    }

}

in fact, all the operation of the data does not take into account the space, time and other factors can be stored procedures +shell script completed

The following is an introduction to using shell+oracle stored procedures in conjunction with completion requirements

Oracle: Cursors (similar to list), loops (while,for), triggers (interceptors), stored procedures (main in Class), functions (functions), judging (if) = = "Java language"

All the data operations are completed in the stored procedure, packaging and writing txt file with Shell to complete

The scenarios we might use:

1: Loop through the script

2:shell script calls stored procedures

3:shell Script Calls Packager

4:shell script verifies that the number of data files is correct

5: Verify that the table exists

6:shell script Step Write person log file

7: How to determine if the stored procedure is performing correctly

8: Verify that the file size is overrun

9: Pass parameters to the shell script

################# #shell Circular Text data if the data equals the end of ABC ######################


While Read var1
Todo


If [$var 1-eq ' abc ']
Then
Break
Fi


Done < Batchno.txt


####################### #shell执行存储过程 ############################


Sqlplus-s User/Password @//ip: Port/service_name <<eof
Set echo off;

Set serveroutput on;

Set timing on;

Test () is the stored procedure name
EXEC test (1);

Commit

Exit

Eof




####################### #shell读取oracle中的数据存到BATC, do variable #################

Batc= ' Sqlplus-s Circ_user/user_circ <<eof
Set heading off;
SELECT T.BATC from Intfuser.abc T WHERE ROW
NUM = 1;
Exit
EOF '

########### #获取多变量 #####

ftp_v= ' Sqlplus-s prip_user/prip_user<<eof
Set Heading off
SELECT T.sftp_ip,
T.sftp_user,
T.sftp_password
From Prip_intf_info T;
Exit
Eof
`
Ftp_ip= ' echo $ftp _v | Awk-f ' ' {print '} '
Ftp_user= ' echo $ftp _v | Awk-f ' ' {print $} '
Ftp_passwd= ' echo $ftp _v | Awk-f ' ' {print $} '

### #如果使用的时候直接使用字符串

You can also concatenate SQL statement variables, such as when uploading using FTP

V_ftp= ' Sqlplus-s prip_user/prip_user<<eof
Set Heading off
SELECT
t.sftp_user| | ', ' | |
T.sftp_password | | ' sftp://' | |
T.sftp_ip
From Prip_intf_info T;
Exit
Eof

' Result: v_ftp:rbsx000100,eqsu3w sftp://10.110.41.150

Lftp-u $v _ftp<<eof
LCD $LOCAL _path--storage location of uploaded files
Put $FTILE _name--The name of the file
Bye--command to end FTP
Eof

####################### #调用打包程序 #######################################
### #调用另一个脚本 #####
./test2.sh

### #打包 #######

#!/bin/sh
#进入路径
Cd/picclife/tools/cross
#删除要存放文件夹下的全部文件
rm-rf/picclife/tools/cross/circ/*

######### #数据从oracle临时表到文件 ######################


/oraexpdata/sqluldr2_linux64_10204.bin username/Password query= "SELECT * FROM tablename" Charset=utf8 file= "/picclife/tools/ Cross/f_ "$BATC" _00%b.txt "rows=
10000000 Batch=yes field=0x7c


########### #数据文件打包 #######################


# #文件名 # #
File= "H_" $BATCHNO "_001.zip"
# #要打包到 #####
Cd/picclife/tools/cross/bak
######### #打包 ######################
7za a-tzip-r $pici/picclife/tools/cross/circ/000100




################### #shell脚本压缩文件txt文件个数 (the number of files may be a_123_1.txt a_123_2.txt file as 1), write log ############


# # Awk-f_ Each row of the results by _ Split {print$3}: Get a third field Uniq: Go to Heavy WC: Row number of bytes (WC written in front + file location/name, WC written after file to open or read files such as more test.txt WC) awk By Space awk-f: '/root/'/etc/passwd search/etc/passwd all lines with root keyword output to log_10g.out file




Echo ' Unzip-v/picclife/tools/ciitc/bak/h_$batchno_001.zip| grep ". txt" |awk-f_ ' {print$3} ' |uniq|wc|awk ' {print $} ' >>/picclife/tools/ciitc/log_10g.out


###################### #验证表是否存在 ###################################


tab= ' Sqlplus-s querydep/querydeptwo <<eof
Set heading off;
Set feedback off;
Set pagesize 0;
Set verify off;
Set echo off;
Select COUNT (*) from user_tables where table_name = ' bs_batchno ';
Exit
EOF '
# #不存在则创建表 ################
If [$tab-eq 0]
Then
echo "Not EXISTS"
Sqlplus-s Querydep/querydeptwo <<eof
Set heading off;
Set feedback off;
Set pagesize 0;
Set verify off;
Set echo off;
CREATE TABLE Bs_batchno (
Batchno VARCHAR2 is not NULL,
Transactionno varchar2 () NOT NULL
);
Exit
EOF;
Fi
################ #创建表也可以创建相同的表结构, does not contain data ################


CREATE TABLE ABC as SELECT * from B1 where 1=0


############### #验证文件的大小是否超限 more than 10G or less than 0G (-size-0g), ############################


find/picclife/tools/cross/bak/-size +10g-exec ls-ld {} \; >>/picclife/tools/ciitc/log_10g.out




########## #如何判断存储过程是否执行正确 #########################################


In fact, stored procedures can be written to the table data, if the stored procedure errors, insert an error log into the table

Query error data in the shell more than 0, terminate, break out of the loop, you can end the continue operation

#也可以 terminate the execution exit of the script when the script goes wrong, stop the exit in the loop and if both

[Oracle@odsdevdb09 ~]$ more./test1.sh

#!/bin/sh

While read VA

Todo

if [$VA = 100]

Then

Exit

Fi

Echo $va

Done < ABC

[Oracle@odsdevdb09 ~]$./test1.sh

Abc

Tt

[Oracle@odsdevdb09 ~]$ cat-n ABC

1 ABC

2 TT

3 100

4 ABCD

5 Zxy

Shell inside the process with the code in the process, the first loop read the text need to process the number, find the corresponding stored procedures, the data into TXT, finally packaged, and verify correctness

##################### #对shell脚本传参数 #############################

[Oracle@odsdevdb09 ~]$ Cat test2.sh

#!/bin/bash

echo "I:" $

echo "Second:" $

[Oracle@odsdevdb09 ~]$./test2.sh

A:

Second

[oracle@odsdevdb09 ~]$./TEST2.SH ABC 123

First:abc

Second:123

########################## #shell的文本替换 ###################################

Sed is an online editor that handles a single line of content at a time. When processing, the currently processed rows are stored in a temporary buffer, then the contents of the buffer are processed with the SED command, and the contents of the buffer are sent to the screen after processing is completed. The next line is processed so that it repeats until the end of the file. The contents of the file have not changed unless you use the redirected storage output

sed ' s//g ' File ( actually the text wood has changed ) Replace All

[Oracle@odsdevdb09 ~]$ Cat ABC
Bc
DD Hello Heolhello
Hellofdhellogdayhello
Zxyizxyasdwuzxaszxysqswqzxy
Am DDDDDDDDDDDDDDDDABDFEA
a BB aa cc
Hello Word

[oracle@odsdevdb09 ~]$ sed ' s/hello/world/g ' abc
Bc
DD World Heolworld
Worldfdworldgdayworld
Zxyizxyasdwuzxaszxysqswqzxy
Am DDDDDDDDDDDDDDDDABDFEA
a BB aa cc
World Word

[Oracle@odsdevdb09 ~]$ Cat ABC
Bc
dd Hello Heol Hello
Hellofdhellogdayhello
Zxyizxyasdwuzxaszxysqswqzxy
Am DDDDDDDDDDDDDDDDABDFEA
a BB aa cc
Hello Word

#################################################

vim:s: Text replacement command, need to use VIM to open the file online, doing processing

VIM ABC
Bc
DD Jjllo Jjol Jjllo
Bc
DD Jjllo Jjol Jjllo
Hellofdhellogdayhello
Ttittasdwuzxasttsqswqtt
Am DDDDDDDDDDDDDDDDABDFEA
A SS-AA cc
Hello word

: g/tt/s//hello/g//Replace All

Bc

DD Jjllo Jjol Jjllo
Bc
DD Jjllo Jjol Jjllo
Hellofdhellogdayhello
Helloihelloasdwuzxashellosqswqhello
Am DDDDDDDDDDDDDDDDABDFEA
A SS-AA cc
Hello Word

: 1, $ s/hello/2017/g//Replace All

Bc
DD Jjllo Jjol Jjllo
Bc
DD Jjllo Jjol Jjllo
2017fd2017gday2017
2017i2017asdwuzxas2017sqswq2017
Am DDDDDDDDDDDDDDDDABDFEA
A SS-AA cc
2017 Word

find/a/b/c-name ' *.txt ' | Xargs cat| Wc-l//Get the total number of TXT files under Directory folder C

Xargs: Gets the parameters of the pipe, passing it to the next parameter in the form of a stream