Clob field processing in oracle database
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/
