CLOB, BLOB, and NLOB in Oracle

Source: Internet
Author: User
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 );

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.