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

1. Specify the string type to be inserted as clob, and you can use procedures or stored procedures.

Example:

DECLARE

REALLYBIGTEXTSTRING CLOB: = 'massive string to be inserted'

BEGIN

INSERT INTO test_table VALUES ('test', REALLYBIGTEXTSTRING,' 0')

Commit

End

2. Convert blob field data to clob type

CREATE OR REPLACE FUNCTION blob_to_clob (blob_in IN BLOB) RETURN CLOB

AS

V_clob CLOB

V_varchar VARCHAR2 (32767)

V_start PLS_INTEGER: = 1

V_buffer PLS_INTEGER: = 32767

Tmp_num number

BEGIN

DBMS_LOB.CREATETEMPORARY (v_clob, TRUE)

Tmp_num: = CEIL (DBMS_LOB.GETLENGTH (blob_in) / v_buffer)

If tmp_num > 0 then-prevents an error from being raised when the incoming BLOB is NULL or the length is 0

FOR i IN 1..tmp_num

LOOP

V_varchar: = UTL_RAW.CAST_TO_VARCHAR2 (DBMS_LOB.SUBSTR (blob_in, v_buffer, v_start))

DBMS_LOB.WRITEAPPEND (v_clob, LENGTH (v_varchar), v_varchar)

V_start: = v_start + v_buffer

END LOOP

End if

RETURN v_clob

END blob_to_clob

two。 Call blob_to_clob to complete the conversion

Update gs_gift p set p.description1=blob_to_clob (p.description)

Commit

3. Import sqlload large fields (LOB type)

Https://www.open-open.com/pdf/aab245f2dcdb421f9c98ec00e695b8a6.html

Http://blog.itpub.net/31015730/viewspace-2147266

Https://blog.csdn.net/u011364306/article/details/50598321( operation reference article)

1) data is saved in a separate file

CREATE TABLE LOBTBL

(

FILEOWNER VARCHAR2 (30)

FILENAME VARCHAR2 (200)

FILESIZE NUMBER

FILEDATA CLOB

CREATE_DATE DATE

);

[oracle@cancer sqlldr] $cat ldr_case12_2.dat

2016-1-27 15:21 183 oracle/ home/oracle/sqlldr/ldr_case11_1.dat

2016-1-27 15:22 150 oracle/ home/oracle/sqlldr/ldr_case11_1.ctl

2016-1-27 15:22 1714 oracle/ home/oracle/sqlldr/ldr_case11_1.log

2016-1-27 16:05 166 oracle/ home/oracle/sqlldr/ldr_case11_2.ctl

2016-1-27 16:13 136 oracle/ home/oracle/sqlldr/ldr_case11_2.bad

2016-1-27 16:13 204 oracle/ home/oracle/sqlldr/ldr_case11_2.dat

2016-1-27 16:13 1696 oracle/ home/oracle/sqlldr/ldr_case11_2.log

2016-1-27 16:35 120 oracle/ home/oracle/sqlldr/ldr_case11_3.ctl

2016-1-27 16:55 188 oracle/ home/oracle/sqlldr/ldr_case11_3.dat

2016-1-27 16:55 1695 oracle/ home/oracle/sqlldr/ldr_case11_3.log

2016-1-27 20:15 183 oracle/ home/oracle/sqlldr/ldr_case11_4.dat

2016-1-27 20:33 126 oracle/ home/oracle/sqlldr/ldr_case11_4.ctl

2016-1-27 20:33 3 oracle/ home/oracle/sqlldr/ldr_case11_4.bad

2016-1-27 20:33 1829 oracle/ home/oracle/sqlldr/ldr_case11_4.log

-- Control file

-- Control file

[oracle@cancer sqlldr] $cat ldr_case12_2.ctl

LOAD DATA

INFILE ldr_case12_2.dat

TRUNCATE INTO TABLE LOBTBL

(

CREATE_DATE position (1:16) date 'yyyy-mm-dd hh34:mi'

FILESIZE position (* + 2:23) "to_number (: FILESIZE,'99999999')"

FILEOWNER position (* + 2:30)

FILENAME position (* + 2:68) "substr (: FILENAME,instr (: FILENAME,'/',-1) + 1)"

FILEDATA LOBFILE (FILENAME) TERMINATED BY EOF

Execute the sqlldr command and view the results

-- execute sqlldr command

[oracle@cancer sqlldr] $sqlldr scott/tiger control=ldr_case12_2.ctl

When the data to be loaded is a fixed-length string without a delimiter, it can be used to specify the start and end of the column through the position keyword in the control file of SQL*Loader. Position is usually written in the following three ways:

[SQL*Loader] SQL*Loader experiment (5)-- three methods of writing position in fixed-length string data processing

Http://blog.itpub.net/20335819/viewspace-698052/

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.