Batch generation of control files, automatic import of data from multiple tables with SQLLDR

Generating control files

Preparatory work:

sql>set colsep '; -Field output delimiter
Sql>set echo off; Displays each SQL command in the script started by the start, which defaults to on
sql> set echo on //Set whether the Run command is to display statements
sql> set feedback on; //settings display "xx row selected"
sql>set feedback off; //echo the number of record bars processed by this SQL command, default to on
sql>set heading off; Output field header, default to on
sql>set pagesize 0; Output number of rows per page, default is 24, in order to avoid paging, can be set to 0.
Sql>set Linesize 80; Output one line of characters, default is
Sql>set Numwidth 12; Output Number Type field length, default is ten
Sql>set termout off; Displays the execution result of the command in the script, which is on by default
Sql>set trimout on; Remove trailing spaces per line of standard output, default to OFF
Sql>set Trimspool on; Remove redirect (spool) output trailing spaces per line, default to OFF
sql>set serveroutput on;//set allow display output similar to Dbms_output

1, spool control.all;

Select Ctl_name from (

Select ' Spool ctl/' | | LOWER (table_name) | | '. CTL ' Ctl_name,table_name, 0 CID from User_tables a

UNION ALL

Select ' Select ' LOAD DATA TRUNCATE into table ' | | table_name,table_name,0.1 CID from User_tables a

UNION ALL

Select ' Fields TERMINATED by X ' | | Chr (39) | | 03 ' | | Chr (39) | | Optionally enclosed by ' | | Chr (39) | | CHR, TABLE_NAME, 0.2 cid from User_tables a

UNION ALL

Select ' TRAILING nullcols ', table_name,0.3 CID from User_tables a

UNION ALL

Select ' (', table_name,0.4 CID from User_tables a

UNION ALL

Select b.tab_column_ora,a.table_name,0.5 cid from User_tables a,tab_column_union b where a.table_name=b.table_name

UNION ALL

Select ') ' from dual; ', table_name,0.6 CID from User_tables a

UNION ALL

Select ' Spool off ', table_name,0.7 CID from User_tables a

) AA order by Table_name,cid;

Spool off;

-----------Generate a new table the Tab_column_union,tab_column_ora field handles the Col field of the time type and is processed with the gy_etl_download stored procedure. The CID pseudo-column controls the output order.

-----------Chr (39) is ' mean, the part between two has nothing to show.

-----------------------------------------

Note: Generate Tab_column_union stored procedures.

Note:

CREATE OR REPLACE PROCEDURE gy_etl_download as

N_count integer;

V_column_ora varchar2 (4000);

Begin

Update tab_column_union set tab_column_db2=null;

For C1 in (select Column_name,a.table_name,column_id,data_type from user_tables a,user_tab_columns b where a.table_name= B.table_name ORDER by a.table_name,column_id)

Loop

n_count:=0;

V_column_ora:=null;

Select COUNT (*) into N_count from TAB_COLUMN_EX where Table_name=c1.table_name and tab_column=c1.column_name;

If n_count> 0 Then

Select ' To_date ' | | ' (' | | c1.column_name| | ', ' | | Chr (39) | | YYYY-MM-DD-HH24.MI.SS ' | | Chr (39) | | ') ' into V_column_ora from dual;

Update Tab_column_union Set tab_column_ora=tab_column_ora| | v_column_ora| | ', ' where table_name=c1.table_name;

Else

If c1.data_type= ' DATE ' then

Select C1.column_name| | ' " To_date (: ' | | c1.column_name| | ', ' | | Chr (39) | | YYYYMMDD ' | | Chr (39) | | ') ' into V_column_ora from dual;

Update Tab_column_union Set tab_column_ora=tab_column_ora| | v_column_ora| | ', ' where table_name=c1.table_name;

Else

Update Tab_column_union Set tab_column_ora=tab_column_ora| | c1.column_name| | ', ' where table_name=c1.table_name;

End If;

End If;

END LOOP;

Update Tab_column_union t set Tab_column_db2=substr (T.tab_column_db2,1,length (T.TAB_COLUMN_DB2)-1);

Update Tab_column_union t set Tab_column_ora=substr (T.tab_column_ora,1,length (T.tab_column_ora)-1);

COMMIT;

End Gy_etl_download;

----------------------------------------

2. Run Control.all script in Sqlplus

Sql> @control. All

Batch generation of control files

3. Execute command script, save script as impdata.sh

#!/bin/sh

#获取当前目录

Currdir= ' DirName

CD $currDir

Currdir= ' pwd '

#数据库连接串

dbconnstr=joey/jy_123orcl11g

#数据文件保存目录

datadir= $currDir/data

#控制文件

controlfile= $currDir/ctl/

#设置环境变量

#ORACLE_HOME =/opt/oracle/product/10201

#PATH = $PATH: $ORACLE _home/bin

#日志根目录

baselogdir= $currDir/log

#插入失败数据的记录的目录

badlogdir= $baseLogDir/bad

#执行日志目录 (import log)

implogdir= $baseLogDir/imp

#命令执行日志目录 (Error logging)

cmdlogdir= $baseLogDir/cmd

#创建日志目录

Mkdir-p $badLogDir 2>/dev/null

Mkdir-p $impLogDir 2>/dev/null

Mkdir-p $cmdLogDir 2>/dev/null

For datafile in ' LS ${datadir}/*.del '

Do

logdatesuffix= ' Date ' +%y%m%d%h%m%s "'

Sqlldr $dbConnStr \

Silent=all errors=10000 \

Data= $dataFile \

log= $impLogDir/' basename ${datafile} '. $logDateSuffix. IMP \

bad= $badLogDir/' basename ${datafile} '. $logDateSuffix. Bad \

control= $controlFile/' basename ${datafile} '. CTL \

>/dev/null \

2>> $cmdLogDir/' basename ${datafile} '. ${logdatesuffix}.log

Done

Run Script $./impdata.sh

This article is from the DBA Sky blog, so be sure to keep this source http://joeyzhang.blog.51cto.com/7594056/1671852

Batch generation of control files, automatic import of data from multiple tables with SQLLDR