CLOB, BLOB, and NLOB in Oracle

Ing of SQL CLOB in JavaTM programming language. SQLCLOB is a built-in type that stores CharacterLargeObject as a column in a row of a database table. By default, the driver uses SQLlocator (CLOB) to implement the Clob object, which means that the CLOB object contains a data pointing to SQLCLOB instead

Ing of SQL CLOB in Java TM programming language. SQL CLOB is a built-in type that stores Character Large Object as a column in a row of a database table. By default, the driver uses SQL locator (CLOB) to implement the Clob object, which means that the CLOB object contains a data pointing to SQL CLOB instead

SQL typeCLOBIng relationships in the JavaTM programming language. SQLCLOBIs a built-in type, which stores Character Large Object as a column value in a row of the database table. By default, the driver uses SQLlocator(CLOB)ImplementationClobObject, which meansCLOBThe object containsCLOBData is not the logical pointer of the data itself.ClobThe object is valid during the transaction processing it creates.

CLOBInterface provides some methods to obtain SQLCLOB(Character Large Object) value length, implemented on the clientCLOBValue and search for a substring orCLOBValueCLOBObject. InterfaceResultSet,CallableStatementAndPreparedStatement(For examplegetClobAndsetClob) Allow programmers to access SQLCLOBValue. In addition, this interface also has updatesCLOBValue method.

In Oracle, Varchar2 supports a maximum of 4 kb. Therefore, for processing long strings, we need to use CLOB fields. CLOB fields support a maximum of 4 GB.
There are several other types:
Blob: binary, if exe, zip
Clob: single-byte code, such as a common text file.
Nlob: Multi-byte code, such as UTF files.
The following describes how to operate the CLOG field. It is used in the help documentation section of our project.
1. Write Data first

Java code

1. /* In the following table, when the HCONTENT field in PF_HELP_CONTENT is of the CLOB type */
2. // Generate the help ID through the sequencer
3. Map map = Query. getMap ("Select TO_CHAR (SEQ_HID.nextval) hid from dual ");
4. Hid = String. valueOf (map. get ("HID "));
5. // Insert a data record. Note that the CLOB field requires an empty clob type empty_clob () first, and then update the clob field separately.
6. SQL = "Insert INTO PF_HELP_CONTENT (HID, HCONTENT) VALUES (?, Empty_clob ())";
7. Try
8. {
9. // Execute insert
10. Rtn = DbUtils.exe cuteUpdate (SQL, hid );
11. /* After successful insertion, modify the content of the HCONTENT field */
12. // Obtain the database connection
13. Connection conn = DbUtils. getConnection ();
14. // Manually submit
15. Conn. setAutoCommit (false );
16. // Define the ResultSet and Clob Variables
17. ResultSet rs = null;
18. Oracle. SQL. CLOB clob = null;
19. // Update SQL
20. String sqlclob = "Select hcontent from PF_HELP_CONTENT Where HID =? FOR Update ";
21. Java. SQL. PreparedStatement pstmt = conn. prepareStatement (sqlclob );
22. // Hid is of the varchar2 type, so setString is used.
23. Pstmt. setString (1, hid );
24. // Execute the update statement
25. Rs = pstmt.exe cuteQuery ();
26. If (rs. next ())
27. {
28. // Obtain the content of the preceding HCONTENT, that is, the added empty_clob ()
29. Clob = (oracle. SQL. CLOB) rs. getClob (1 );
30. }
31. // The output must be in clob. getCharacterOutputStream () stream mode.
32. Writer write = clob. getCharacterOutputStream ();
33. // Write the specific content. helpform. getHContent () stores the Help content
34. Write. write (helpform. getHContent ());
35. Write. flush ();
36. Write. close ();
37. Rs. close ();
38. // Submit
39. Conn. commit ();
40. Conn. close ();
41. }
42. Catch (Exception ex)
43. {
44. //.........
45. }

Java code

1. /* In the following table, when the HCONTENT field in PF_HELP_CONTENT is of the CLOB type */
2. // Generate the help ID through the sequencer
3. Map map = Query. getMap ("Select TO_CHAR (SEQ_HID.nextval) hid from dual ");
4. Hid = String. valueOf (map. get ("HID "));
5. // Insert a data record. Note that the CLOB field requires an empty clob type empty_clob () first, and then update the clob field separately.
6. SQL = "Insert INTO PF_HELP_CONTENT (HID, HCONTENT) VALUES (?, Empty_clob ())";
7. Try
8. {
9. // Execute insert
10. Rtn = DbUtils.exe cuteUpdate (SQL, hid );
11. /* After successful insertion, modify the content of the HCONTENT field */
12. // Obtain the database connection
13. Connection conn = DbUtils. getConnection ();
14. // Manually submit
15. Conn. setAutoCommit (false );
16. // Define the ResultSet and Clob Variables
17. ResultSet rs = null;
18. Oracle. SQL. CLOB clob = null;
19. // Update SQL
20. String sqlclob = "Select hcontent from PF_HELP_CONTENT Where HID =? FOR Update ";
21. Java. SQL. PreparedStatement pstmt = conn. prepareStatement (sqlclob );
22. // Hid is of the varchar2 type, so setString is used.
23. Pstmt. setString (1, hid );
24. // Execute the update statement
25. Rs = pstmt.exe cuteQuery ();
26. If (rs. next ())
27. {
28. // Obtain the content of the preceding HCONTENT, that is, the added empty_clob ()
29. Clob = (oracle. SQL. CLOB) rs. getClob (1 );
30. }
31. // The output must be in clob. getCharacterOutputStream () stream mode.
32. Writer write = clob. getCharacterOutputStream ();
33. // Write the specific content. helpform. getHContent () stores the Help content
34. Write. write (helpform. getHContent ());
35. Write. flush ();
36. Write. close ();
37. Rs. close ();
38. // Submit
39. Conn. commit ();
40. Conn. close ();
41. }
42. Catch (Exception ex)
43. {
44. //.........
45. }

2. Modify the CLOB field content

Java code

1. /* The modification is basically the same as the insert operation. It is also implemented using for update */
2. // If the length of the field before modification is greater than the length of the current modification, the content at the end will still exist
3. // Set the content of PF_HELP_CONTENT to null before modifying the content
4. SQL = "Update PF_HELP_CONTENT SET HCONTENT = empty_clob () Where HID =? ";
5. Try
6. {
7. Rtn = DbUtils.exe cuteUpdate (SQL, hid );
8. // The following operations are the same as when adding
9. Connection conn = DbUtils. getConnection ();
10. Conn. setAutoCommit (false );
11. ResultSet rs = null;
12. Oracle. SQL. CLOB clob = null;
13. String sqlclob = "Select hcontent from PF_HELP_CONTENT Where HID =? FOR Update ";
14. Java. SQL. PreparedStatement pstmt = conn. prepareStatement (sqlclob );
15. Pstmt. setString (1, hid );
16. Rs = pstmt.exe cuteQuery ();
17. If (rs. next ())
18. {
19. Clob = (oracle. SQL. CLOB) rs. getClob (1 );
20. }
21. Writer write = clob. getCharacterOutputStream ();
22. Write. write (helpform. getHContent ());
23. Write. flush ();
24. Write. close ();
25. Rs. close ();
26. Conn. commit ();
27. Conn. close ();
28. }
29. Catch (Exception ex)
30. {
31. //...
32. }

Java code

1. /* The modification is basically the same as the insert operation. It is also implemented using for update */
2. // If the length of the field before modification is greater than the length of the current modification, the content at the end will still exist
3. // Set the content of PF_HELP_CONTENT to null before modifying the content
4. SQL = "Update PF_HELP_CONTENT SET HCONTENT = empty_clob () Where HID =? ";
5. Try
6. {
7. Rtn = DbUtils.exe cuteUpdate (SQL, hid );
8. // The following operations are the same as when adding
9. Connection conn = DbUtils. getConnection ();
10. Conn. setAutoCommit (false );
11. ResultSet rs = null;
12. Oracle. SQL. CLOB clob = null;
13. String sqlclob = "Select hcontent from PF_HELP_CONTENT Where HID =? FOR Update ";
14. Java. SQL. PreparedStatement pstmt = conn. prepareStatement (sqlclob );
15. Pstmt. setString (1, hid );
16. Rs = pstmt.exe cuteQuery ();
17. If (rs. next ())
18. {
19. Clob = (oracle. SQL. CLOB) rs. getClob (1 );
20. }
21. Writer write = clob. getCharacterOutputStream ();
22. Write. write (helpform. getHContent ());
23. Write. flush ();
24. Write. close ();
25. Rs. close ();
26. Conn. commit ();
27. Conn. close ();
28. }
29. Catch (Exception ex)
30. {
31. //...
32. }

3. Retrieve the text content of the CLOB Field

Java code

1. /* The previous parts are consistent */
2. Connection conn = DbUtils. getConnection ();
3. Conn. setAutoCommit (false );
4. ResultSet rs = null;
5. Oracle. SQL. CLOB clob = null;
6. String sqlclob = "Select hcontent from PF_HELP_CONTENT Where HID =? ";
7. Java. SQL. PreparedStatement pstmt = conn. prepareStatement (sqlclob );
8. Pstmt. setString (1, hid );
9. Rs = pstmt.exe cuteQuery ();
10. If (rs. next ())
11. {
12. // Parameter 1 in rs. getClob (1) indicates the HCONTENT field index. The first field starts from 1 rather than from 0.
13. // You can also use the field name to obtain rs. getClob ("HCONTENT ")
14. Clob = (oracle. SQL. CLOB) rs. getClob (1 );
15. }
16. If (clob = null | clob. length () = 0)
17. {
18. Hcontent = "";
19. } Else
20. {
21. // Take the CLOB field content as a string
22. Hcontent = clob. getSubString (long) 1, (int) clob. length ());
23. }
24. Rs. close ();
25. Conn. close ();
26. Request. setAttribute ("HCONTENT", hcontent );