Unix shell_oracle ERP and other systems interface data transfer via Shell control (case) (schema)

2014-06-26 Baoxinjian

I. Summary

When different system data are exchanged, there can be many ways, such as Mq,dblink

When it comes to some projects with more secure information, many companies use Unix shells to throw files directly.

When using a Unix shell to throw a file, it is necessary to distinguish which files have been read, which files are not read, how the files are processed after a successful read, and how the files after the failed read are handled

A previous insurance project, which handles all interface data data between different systems, is controlled by the Unix shell, and its security is higher

Step1. Through a Unix shell to read a folder interface all the file names,

Step2. Insert it into a contorl table, before inserting it to determine whether the file name exists, and no longer insert if it exists, avoid duplicate records

Step3. Data file not processed in Contorl table is read by utl_file

Step4. After the file is read successfully, transfer the file to another folder success

Step5. When the file fails to read, transfer the file to another folder failed

II. implementation of the case

Case: Throwing interface dat files from another system and storing them in/home/applvis/bts/interface, controlling file reads via UNIX shell

Step1. Establish control table to test the Unix shell to store the unread file name in the table

1 Create TableBXJ_INTERFACE_CONTROL_TB (2batch_id Number,3File_seq Number,4 file_name varchar2( -),5Record_count Number,6Success_record Number,7Failure_record Number,8Process_statusvarchar2( -),9request_id Number,Ten user_id  Number, One user_name varchar2( -), A creation_date Date - ) -  the CreateSequence BXJ_BATCH_ID_SQ Start with  +

Step2. Confirm that there are 5 files under the Interface Dat profile folder, so when the Unix shell is processed, the 5 files must be stored in the Contorl table

Step3. Confirm that there are three interface store dat data, log to store shell logs, SQL to hold SQL execution file

Step4. The most critical step to build a Unix Shell, I am not a DBA born, although learned the bash grammar, but not the province proficient, everyone live to see

1#/bin/Bash2# +--------------------------------------------+3# + An example of calling host concurrent +4# + Author:Gavin.Bao +5# +--------------------------------------------+6 #  7# ---------------------------------8 # Define Variable9# ---------------------------------TenExport ora_user_pass=$1 OneExport ora_user_id=$2 AExport ora_login_name=$3 -Export ora_request_id=$4 -  theExport folder_path="/home/applvis/bts/interface/" -Export interface_path="/home/applvis/bts/interface" -Export sql_path="/home/applvis/bts/sql" -Export log_path="/home/applvis/bts/log" +  -# ------------------------------------------- +# Loop thefilename and store it into array A# ------------------------------------------- atx=0         -  forFileNameinch${folder_path}*; -  Do    -array[$x]= 'basename$filename ' -Let"x+=1" -  Done in  -  forInterface_nameinch${array[*]} to  Do +   Echo$interface _name -  Done the  *# --------------------------------- $ # Prepare Plsql scriptPanax Notoginseng# --------------------------------- - Echo "set serveroutput on size 1000000"> $SQL _path/Bxjinterface.sql the Echo "Set Feed off">> $SQL _path/Bxjinterface.sql + Echo "Declare">> $SQL _path/Bxjinterface.sql A Echo "n_count number;">> $SQL _path/Bxjinterface.sql the Echo "n_batch_id Number: = &1;">> $SQL _path/Bxjinterface.sql + Echo "v_file_name varchar2 (+): = ' &2 ';">> $SQL _path/Bxjinterface.sql - Echo "begin">> $SQL _path/Bxjinterface.sql $ Echo "Select COUNT (*) into N_count">> $SQL _path/Bxjinterface.sql $ Echo "From bxj_interface_control_tb where file_name = V_file_name;">> $SQL _path/Bxjinterface.sql - Echo "if (n_count = 0) Then">> $SQL _path/Bxjinterface.sql - Echo "INSERT INTO BXJ_INTERFACE_CONTROL_TB">> $SQL _path/Bxjinterface.sql the Echo "values (bxj_batch_id_sq.nextval,n_batch_id,v_file_name,0,0,0, ' n ', $ORA _request_id, $ORA _user_id, ' $ORA _login_ NAME ', sysdate);">> $SQL _path/Bxjinterface.sql - Echo "End If;">> $SQL _path/Bxjinterface.sqlWuyi Echo "commit;">> $SQL _path/Bxjinterface.sql the Echo "dbms_output.put_line (' excuted sucessfully. ');">> $SQL _path/Bxjinterface.sql - Echo "exception when others then">> $SQL _path/Bxjinterface.sql Wu Echo "rollback;">> $SQL _path/Bxjinterface.sql - Echo "dbms_output.put_line (' Executed Failed ');">> $SQL _path/Bxjinterface.sql About Echo "dbms_output.put_line (' sqlcode= ' | | SQLCODE);">> $SQL _path/Bxjinterface.sql $ Echo "dbms_output.put_line (' sqlerrm= ' | | SQLERRM);">> $SQL _path/Bxjinterface.sql - Echo "end;">> $SQL _path/Bxjinterface.sql - Echo "/">> $SQL _path/Bxjinterface.sql -  A# ------------------------------------------- + # Execute Plsql script and generate the log the# ------------------------------------------- - if[-S $SQL _path/bxjinterface.sql]; Then $   Echo-E"Running SQL script to find out Bdump directory ... \ n"    theLength=${#array [@]} the   Echo$length the    for((i=0;i< $length; i++)) the    Do -$ORACLE _home/bin/sqlplus-s apps/apps >> $LOG _path/bxjinterface.log <<EOF in@ $SQL _path/bxjinterface.sql $i'${array[$i]}' the EOF the  Done About fi the  the# -------------------------------- the# Show Logfile +# -------------------------------- - Echo "Check the Reslut" the Echo "------------------------"Bayi Cat$LOG _path/Bxjinterface.log the  the Echo "bxj Shell End"

Third, case test

Test1. Call the created Shell file, read the file name

Test2. When the Unix shell executes, the resulting log file

Test2. When the Unix shell executes, the resulting SQL file

Test3. See if there are 5 filenames in the Contorl table

Abalone New ********************