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

In the previous four articles, we introduced how to use SQL*Loader and use cases. This article uses SQL*Loader to import CLOB and BLOB data.

Oracle SQL*Loader Use Case 1

Oracle SQL*Loader Use Case II

Oracle SQL*Loader Use Case III

Oracle SQL*Loader Use Cases (IV)

Importing blob or blob data into a database using SQL*Loader is always a bit of a headache. This article records in detail the use of Oracle SQL*Loader to achieve this feature of an experimental case, hoping to help everyone.

Start by creating a test sheet for your experiment

SCOTT@seiang11g>create table tb_clob(new_id number(20),new_to varchar2(100),new_subject varchar2(100),new_date date,new_content clob);

Table created.

Here's what's in the data file:

[oracle@wjq SQL*Loader]$ vim wjq_clob.csv

1,wjq123@qq.com,"Greeting from Mars",2017-11-02 10:59:43,\/u01\/app\/oracle\/SQL*Loader\/new_clob001.dat

2,seiang@126.com,"Special discount",2017-11-02 11:28:55,\/u01\/app\/oracle\/SQL*Loader\/new_clob002.dat

(Special note: be sure to pay attention to the format of the data file, there can be no extra spaces, the author planted a lot of pits here, otherwise the import will report errors, so it is best to check the log information after the import)

new_content is the message body, represented by the clob data type. This field holds only the clob file name of the message body for each record in the data file. new_clob001.dat,, new_clob002.dat file holds the real content of new_content.

Contents of new_clob001.dat file:

[oracle@wjq SQL*Loader]$ cat new_clob001.dat

China's top judicial authorities presented long-anticipated reports on Wednesday to national legislators on their progress in upholding the law and preventing wrongful convictions in the wake of important judicial reforms in 2013.

The Supreme People's Court and Supreme People's Procuratorate both submitted reports to the bimonthly session of the Standing Committee of the National People's Congress on Wednesday.The top court said it had overturned 37 wrongful convictions since November 2012, including in the high-profile case of Nie Shubin, who was exonerated on Dec 2, 2016, more than two decades after he was wrongly executed for rape and murder.

Contents of new_clob001.dat file:

[oracle@wjq SQL*Loader]$ cat new_clob002.dat

Thanks to these efforts, courts acquitted 4,032 defendants in accordance with the law between 2013 and September this year, the report said.

Courts have also been ordered to strictly exclude evidence obtained illegally, including evidence gained by torture, "and not to force anyone to plead guilty", Zhou said.In Shanghai, for example, between July 2016 and September this year, the city's courts received 24 applications from defense attorneys to strike evidence suspected to have been obtained illegally, leading to 15 reviews on the legality of evidence, he said.

To import the above data, you need to write the following control file:

[oracle@wjq SQL*Loader]$ cat wjq_clob.ctl

LOAD DATA

INFILE '/u01/app/oracle/SQL*Loader/wjq_clob.csv'

TRUNCATE INTO TABLE tb_clob

FIELDS TERMINATED BY ','

( new_id CHAR(20),

new_to CHAR(100),

new_subject CHAR(100),

new_date DATE "YYYY-MM-DD HH24:MI:SS" ":new_date",

clob_filename FILLER CHAR(1000000),

new_content LOBFILE(clob_filename) TERMINATED BY EOF

)

The key to the above control file is to define a pseudo-field for clob_filename (filler in SQL*Loader) to get the clob file name, followed by lobfile to import the message body from that file. If you need to import blob type data, the method is exactly the same.

Here's how to start the import:

[oracle@wjq ~]$ sqlldr scott/tiger control=/u01/app/oracle/SQL*Loader/wjq_clob.ctl log=/u01/app/oracle/SQL*Loader/wjq_clob.log

SQL*Loader: Release 11.2.0.4.0 - Production on Thu Nov 2 11:43:54 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

Commit point reached - logical record count 1

Commit point reached - logical record count 2

Check the log below:

Table TB_CLOB:

2 Rows successfully loaded.

0 Rows not loaded due to data errors.

0 Rows not loaded because all WHEN clauses were failed.

0 Rows not loaded because all fields were null.

Space allocated for bind array: 1000488 bytes(1 rows)

Read buffer bytes: 1048576

Total logical records skipped: 0

Total logical records read: 2

Total logical records rejected: 0

Total logical records discarded: 0

Run began on Thu Nov 02 11:43:54 2017

Run ended on Thu Nov 02 11:43:54 2017

Elapsed time was: 00:00:00.05

CPU time was: 00:00:00.00

You can see from the log that the import was successful. Verify by querying the contents of the table below.

Author: SEian.G (practice seventy-two changes, laugh at eighty-one difficulties)

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.