Operate Oracle clob and blob

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

/* In the following table, when the hcontent field in pf_help_content is of the clob type */<br/> // The help ID is generated by the sequencer <br/> map = query. getmap ("select to_char (seq_hid.nextval) hid from dual"); <br/> hid = string. valueof (map. get ("hid"); <br/> // insert a data record. For the clob field, insert an empty clob-type empty_clob (), then independently update the clob field <br/> SQL = "insert into pf_help_content (hid, hcontent) values (?, Empty_clob () "; <br/> try <br/> {<br/> // execute insert <br/> RTN = dbutils.exe cuteupdate (SQL, hid ); <br/>/* modify the content of the hcontent field */<br/> // get the database connection <br/> connection conn = dbutils. getconnection (); <br/> // manually submit <br/> Conn. setautocommit (false); <br/> // defines the resultset and clob variables <br/> resultset rs = NULL; <br/> oracle. SQL. clob = NULL; <br/> // update SQL <br/> string sqlclob = "select hcontent from pf_help_content Where hid =? For Update "; <br/> JAVA. SQL. preparedstatement pstmt = Conn. preparestatement (sqlclob); <br/> // hid is of the varchar2 type, so setstring is used. <br/> pstmt. setstring (1, hid); <br/> // execute the update statement <br/> rs = pstmt.exe cutequery (); <br/> If (RS. next () <br/>{< br/> // obtain the content of the hcontent, that is, the added empty_clob () <br/> clob = (Oracle. SQL. clob) rs. getclob (1); <br/>}< br/> // clob is required. getcharacteroutputstream () stream output <br/> writer write = clob. getcharacteroutputstream (); <br/> // write the specific content, helpform. gethcontent () stores the Help content <br/> write. write (helpform. gethcontent (); <br/> write. flush (); <br/> write. close (); <br/> Rs. close (); <br/> // submit <br/> Conn. commit (); <br/> Conn. close (); <br/>}< br/> catch (exception ex) <br/>{< br/> //......... <br/>}< br/>

 

2. Modify the clob field content

/* The modification is basically the same as the insertion, and is also implemented using for update */<br/> // if the length of the field before modification is greater than the length of the currently modified field, some content at the end still exists <br/> // Therefore, leave the pf_help_content blank before modifying the content <br/> SQL = "Update pf_help_content set hcontent = empty_clob () where hid =? "; <Br/> try <br/> {<br/> RTN = dbutils.exe cuteupdate (SQL, hid ); <br/> // The following operations are the same as when adding <br/> connection conn = dbutils. getconnection (); <br/> Conn. setautocommit (false); <br/> resultset rs = NULL; <br/> oracle. SQL. clob = NULL; <br/> string sqlclob = "select hcontent from pf_help_content Where hid =? For Update "; <br/> JAVA. SQL. preparedstatement pstmt = Conn. preparestatement (sqlclob); <br/> pstmt. setstring (1, hid); <br/> rs = pstmt.exe cutequery (); <br/> If (RS. next () <br/>{< br/> clob = (Oracle. SQL. clob) rs. getclob (1); <br/>}< br/> writer write = clob. getcharacteroutputstream (); <br/> write. write (helpform. gethcontent (); <br/> write. flush (); <br/> write. close (); <br/> Rs. close (); <br/> Conn. commit (); <br/> Conn. close (); <br/>}< br/> catch (exception ex) <br/>{< br/> //... <br/>}< br/>

 

3. Retrieve the text content of the clob Field
/* The previous parts are consistent */<br/> connection conn = dbutils. getconnection (); <br/> Conn. setautocommit (false); <br/> resultset rs = NULL; <br/> oracle. SQL. clob = NULL; <br/> string sqlclob = "select hcontent from pf_help_content Where hid =? "; <Br/> JAVA. SQL. preparedstatement pstmt = Conn. preparestatement (sqlclob); <br/> pstmt. setstring (1, hid); <br/> rs = pstmt.exe cutequery (); <br/> If (RS. next () <br/>{< br/> // Rs. in getclob (1), parameter 1 refers to the hcontent field index. The first field starts from 1 rather than from 0. <Br/> // You can also use the field name to retrieve Rs. getclob ("hcontent") <br/> clob = (Oracle. SQL. clob) rs. getclob (1); <br/>}< br/> If (clob = NULL | clob. length () = 0) <br/>{< br/> hcontent = ""; <br/>} else <br/>{< br/> // retrieves the clob field content as a string <br/> hcontent = clob. getsubstring (long) 1, (INT) clob. length (); <br/>}< br/> Rs. close (); <br/> Conn. close (); <br/> request. setattribute ("hcontent", hcontent); <br/>