If the data is too large to be assigned directly to a CLOB variable, then we can read and write to the CLOB variable in stages using the DBMS_LOB.read and DBMS_LOB.write methods. But at this point we should cache CLOB variables, as shown in the first sentence below. Here is an example program that can not only write big data but also read it by switching the order DBMS_LOB.read and DBMS_LOB.write.
dbms_lob.createtemporary(lob_loc => x_clob,
cache => TRUE);
PROCEDURE load_clob(p_clob_in IN CLOB,
x_clob IN OUT NOCOPY CLOB) IS
l_clob_len NUMBER := dbms_lob.getlength(p_clob_in);
l_data VARCHAR2(32756);
l_buf_len_std NUMBER := 4000;
l_buf_len_cur NUMBER;
l_seg_count NUMBER;
l_write_offset NUMBER;
BEGIN
IF p_clob_in IS NOT NULL THEN
l_seg_count := floor(l_clob_len/l_buf_len_std);
FOR i IN 0 .. l_seg_count
LOOP
IF i = l_seg_count THEN
l_buf_len_cur := l_clob_len - i * l_buf_len_std;
ELSE
l_buf_len_cur := l_buf_len_std;
END IF;
IF l_buf_len_cur > 0 THEN
dbms_lob.read(lob_loc => p_clob_in,
amount => l_buf_len_cur,
offset => i * l_buf_len_std + 1,
buffer => l_data);
l_write_offset := nvl(dbms_lob.getlength(lob_loc => x_clob),
0) + 1;
dbms_lob.write(lob_loc => x_clob,
amount => l_buf_len_cur,
offset => l_write_offset,
buffer => l_data);
END IF;
END LOOP;
END IF;
END load_clob;
Reproduced in: https://blog.51cto.com/snans/1353672
Read More:
- Floor() error injection
- Error of reading character c + +
- Ctfhub error injection
- Development board cannot be mounted, uboot has a lot of JFFS2: alert information
- Summary of database
- Rselenium packet capture chain home network (Part 2: data storage and fault tolerance management)
- close failed in file object destructor: IOError: [Error 10] No child processes
- 0xc0000005: solution to access conflict when writing to location 0x00000000
- Drawing a cube with OpenGL
- Error: cannot fetch last explain plan from plan_table
- Raise in Oracle_APPLICATION_Error Usage
- The Ethereum private chain is built, and the block information cannot be synchronized. The error is resolved: Node data write error err=”state node failed with all peers(1 tries, 1 peers)
- 13. R language: Error in match.names(clabs, names(xi)): The name is not relative to the original name
- OpenGL learning notes and other learning thinking
- There is an unhandled exception at: 0xc0000005: an access conflict occurred while reading location 0x00000000.
- OpenGL step pit record
- C / C + + rounding function ceil(), floor ()
- json.load (file) error
- Mysql cannot allocate memory for the buffer pool solution
- Error Code: 1055. Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated