Shulou(Shulou.com)06/01 Report--

. / 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

/

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.