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

Source: Internet
Author: User

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

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.