Migrate data with sqlldr-KDB to Oracle
. / kdb_to_oracle.sh user_name user_password
Eg:. / get_ddl.sh nda_202 NDA_202-- Export from kdb all objects under nda_202 are loaded into the oracle database through sqlldr
Kdb_to_oracle.sh script:
#! / bin/bash user_name=$ {1}
User_pass=$ {2}
Schema_name=$ {3} if [!-d log]
Then
Mkdir log
Fi if [!-d ctl_file]
Then
Mkdir ctl_file
Fi if [!-d sql]
Then
Mkdir sql
Fi if [!-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-fills%'{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
+
Done sed-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-Fairview'{print $1}'`
Do
Sqlplus-s ${user_name} / ${user_pass} 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 database for i in `cat tab.txt | grep TABLE | awk-Fairview'{print $1}'`
Do
File_name= `echo $I | awk-Falls.'{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
+
Done sed-I 's/machao > / / g' data/*.txt
Sed-I's Spoolmax SpoolUniverse 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.log for i in `cat tab.txt | grep TABLE | awk-awk-Fairview'{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
CREATE OR REPLACE PROCEDURE P_GENERATE_SQLLDR_NULL (p_table_name IN VARCHAR2) AS
L_curr_line LONG
L_table_name user_tables.table_name%TYPE
BEGIN
Select table_name
Into l_table_name
From user_tables
Where table_name = upper (p_table_name)
L_curr_line: ='
LOAD DATA
INFILE''| | upper (l_table_name) | | '.txt''
INTO TABLE'| | upper (l_table_name) | | chr (10) | |
'FIELDS TERMINATED BY',''| | chr (10) | | 'TRAILING NULLCOLS (')
For rec in (select table_name,column_name,column_id,data_type
From user_tab_columns
Where table_name = upper (p_table_name)
Order by column_id) loop
If rec.column_id = 1 THEN
IF rec.data_type=' DATE' or rec.data_type='TIMESTAMP (6) 'THEN
L_curr_line: = l_curr_line | |'
'| | rec.column_name | |''| | 'timestamp "yyyy-mm-dd hh34:mi:ss''
ELSE
L_curr_line: = l_curr_line | |'
'| | rec.column_name
END IF
ELSE
IF rec.data_type=' DATE' or rec.data_type='TIMESTAMP (6) 'THEN
L_curr_line: = l_curr_line | |'
,'| | rec.column_name | |''| | 'timestamp "yyyy-mm-dd hh34:mi:ss''
ELSE
L_curr_line: = l_curr_line | |'
,'| | rec.column_name
END IF
End if
End loop
L_curr_line: = l_curr_line | |')'
Dbms_output.put_line (l_curr_line)
END P_generate_sqlldr_null
/
