Inserting values to table using text file in Oracle SQL-CodePudding

I tried to do following task

First I created a table

CREATE TABLE my_table(
order_id Int,
id_number VARCHAR(50),
con_number VARCHAR(50),
action_code VARCHAR(50)
);

I have a text file called index.txt. The contents of the text file are as follows

[ 2022-01-24 00:38:12 ]|[ URL ]|"https:\/\/kafkawewy.dc.efwe.vb\/app.kyc\/28950294"
[ 2022-01-24 00:38:12 ]|[ LOG_PARAMS ]|{"order_id":28450294,"id_number":"466054543","con_number":"344354501983","action_code":"verifier_approved"}
[ 2022-01-24 00:38:12 ]|[ RESPONSE ]|"Success\n202"
[ 2022-01-24 01:15:32 ]|[ URL ]|"https:\/\/kafkawewy.dc.efwe.vb\/app.kyc\/28948509"
[ 2022-01-24 01:15:32 ]|[ LOG_PARAMS ]|{"order_id":28945509,"id_number":"46653156V","con_number":"117546476356","action_code":"verifier_approved"}
[ 2022-01-24 01:15:32 ]|[ RESPONSE ]|"Success\n202"
[ 2022-01-24 01:15:45 ]|[ URL ]|"https:\/\/kafkawewy.dc.efwe.vb\/app.kyc\/28951964"
[ 2022-01-24 01:15:45 ]|[ LOG_PARAMS ]|{"order_id":28948964,"id_number":"14896103756","con_number":"345434353024","action_code":"verifier_approved"}
[ 2022-01-24 01:15:45 ]|[ RESPONSE ]|"Success\n202"
[ 2022-01-24 01:16:02 ]|[ URL ]|"https:\/\/kafkawewy.dc.efwe.vb\/app.kyc\/28952134"
[ 2022-01-24 01:16:02 ]|[ LOG_PARAMS ]|{"order_id":289745834,"id_number":"1187402899","con_number":"45445946461","action_code":"verifier_rejected"}
[ 2022-01-24 01:16:02 ]|[ RESPONSE ]|"Success\n202"

I want to insert value to table (order_id ,id_number,con_number,action_code) using index.txt file.

Any one can help me

Thank you

CodePudding user response：

you can do this using unix: need to parse the unwanted text using grep and sed. after you parse it like this, you can load the output file to table using oracle sqlldr utility.

    grep "{" index.txt|cut -f2 -d'{'|cut -f1 -d'}'|sed 's/"order_id"://g'|sed 's/"id_number"://g'|sed 's/"con_number"://g'|sed 's/"action_code"://g' >> index_new.txt

    cat index_new.txt

CodePudding user response：

Using sqlldr with a control file like this will parse your text file:

LOAD DATA INFILE 'dat.data'
APPEND INTO TABLE my_table WHEN (27:36) = 'LOG_PARAMS'
fields terminated by ',' 
(
order_id char "substr(:order_id,instr(:order_id, ':', 40) 1)" ,
id_number char "substr(:id_number,instr(:id_number, ':', 9) 2)" ,
con_number char "substr(:con_number,instr(:con_number, ':', 9) 2)" ,
action_code char "substr(:action_code, instr(:action_code, ':', 9) 2)" 
)

However it does not get rid of trailing quote and enclosing curly bracket from the string columns. You could subsequently remove it via a post processing operation like this

update my_table set id_number = substr(id_number, 1, length(id_number) -1),
con_number = substr(con_number, 1, length(con_number) -1),
action_code = substr(action_code, 1, length(action_code) -2);