./kdb_to_oracle.shuser_nameuser_password
eg:./get_ddl.shnda_202NDA_202--从kdb导出nda_202下的所有对象通过sqlldr加载到oracle数据库中

kdb_to_oracle.sh 脚本:
#!/bin/bashuser_name=${1}
user_pass=${2}
schema_name=${3}if [ ! -d log ]
then
mkdir log
fiif [ ! -d ctl_file ]
then
mkdir ctl_file
fiif [ ! -d sql ]
then
mkdir sql
fiif [ ! -d data ]
then
mkdir data
fi#get object type and name, T1:TABLE I1:INDEX
kdsql ${user_name}/${user_pass} << + >tab.txt.tmp
set pagesize 0
select 'TAB_NAME% '||object_name||':'||object_type from user_objects;
+egrep TAB_NAME tab.txt.tmp |awk -F'%' '{print $NF}' > tab.txt
rm -rf tab.txt.tmp
#get ddl
for i in `cat tab.txt`
do
obj_type=`echo ${i}|awk -F ':' '{print $2}'`
obj_name=`echo ${i}|awk -F ':' '{print $1}'`
kdsql ${user_name}/${user_pass} << + >>tab.ddl
set pagesize 0
set long 999999
set linesize 20000
set heading off;
set feedback off;
spool table_ddl.txt append
SELECT DBMS_METADATA.GET_DDL('${obj_type}','${obj_name}') from dual;
spool off
+
donesed -i '/machao>/d' table_ddl.txt
sed -i '/SQL>/d' table_ddl.txt
#get control file for sqlldr from oracle database
for i in `cat tab.txt |grep TABLE|awk -F':' '{print $1}'`
do
sqlplus -s ${user_name}/${user_pass} </app/metadata/ctl_file/${i}.ctl
set heading off
set serveroutput on
set linesize 1000
exec P_generate_sqlldr_null('${i}');
EOF
done
sed -i '/PL\/SQL procedure successfully completed/d' /app/metadata/ctl_file/*.ctl
#generate sql to export plain text from kdsqlfor i in `ls ctl_file/`
do
file_name=`echo ${i}|awk -F'.' '{print $1}'`
sed -n '/TRAILING NULLCOLS/,$p' ctl_file/$i > sql/${file_name}.sql
sed -i "s/TRAILING NULLCOLS (/select/g" sql/${file_name}.sql
sed -i 's/timestamp "yyyy-mm-dd hh34:mi:ss"//g' sql/${file_name}.sql
sed -i "s/^,/||','||/g" sql/${file_name}.sql
sed -i "s/)/ from ${file_name};/g" sql/${file_name}.sql
done#export data to *.txt from inspure databasefor i in `cat tab.txt |grep TABLE|awk -F':' '{print $1}'`
do
file_name=`echo $i|awk -F'.' '{print $1}'`
kdsql ${user_name}/${user_pass} << + >/dev/null
set colsep ',';
set echo off;
set feedback off;
set heading off;
set pagesize 0;
set termout off;
set trimout on;
set trimspool on;
set linesize 30000;
spool data/${i}.txt
@sql/${i}.sql
spool off
+
donesed -i 's/machao>//g' data/*.txt
sed -i 's/spool//g' data/*.txt
sed -i '/spool off/d' data/*.txt
sed -i '/@sql\//d' data/*.txt#load plain text to oracle database with sqlldr
#sqlldr ${user_name}/${user_pass} control=ctl_file/DA_MD_503506.ctl data=data/DA_MD_503506.txt log=log/DA_MD_503506.logfor i in `cat tab.txt |grep TABLE|awk -F':' '{print $1}'`
do
sqlldr ${user_name}/${user_pass} control=ctl_file/${i}.ctl data=data/${i}.txt log=log/${i}.log >/dev/null
done#timestamp "yyyy-mm-dd hh34:mi:ss"


#SP of P_generate_sqlldr_null
CREATEORREPLACEPROCEDUREP_GENERATE_SQLLDR_NULL(p_table_nameINVARCHAR2)AS
l_curr_lineLONG;
l_table_nameuser_tables.table_name%TYPE;
BEGIN
selecttable_name
intol_table_name
fromuser_tables
wheretable_name=upper(p_table_name);
l_curr_line:='
LOADDATA
INFILE'''||upper(l_table_name)||'.txt''
INTOTABLE'||upper(l_table_name)||chr(10)||
'FIELDSTERMINATEDBY'','''||chr(10)||'TRAILINGNULLCOLS(';

forrecin(selecttable_name,column_name,column_id,data_type
fromuser_tab_columns
wheretable_name=upper(p_table_name)
orderbycolumn_id)loop
ifrec.column_id=1THEN
IFrec.data_type='DATE'orrec.data_type='TIMESTAMP(6)'THEN
l_curr_line:=l_curr_line||'
'||rec.column_name||''||'timestamp"yyyy-mm-ddhh34:mi:ss"';
ELSE
l_curr_line:=l_curr_line||'
'||rec.column_name;
ENDIF;
ELSE
IFrec.data_type='DATE'orrec.data_type='TIMESTAMP(6)'THEN
l_curr_line:=l_curr_line||'
,'||rec.column_name||''||'timestamp"yyyy-mm-ddhh34:mi:ss"';
ELSE
l_curr_line:=l_curr_line||'
,'||rec.column_name;
ENDIF;

endif;

endloop;
l_curr_line:=l_curr_line||')';
dbms_output.put_line(l_curr_line);
ENDP_generate_sqlldr_null;
/