Be careful with date type column.
Just like Y2K, when you try to give a value to a column which type is date, you should determine the year area with YYYY not YY.
Suppose there is a user, Named Bob, Male, borned on 1977,Jan, 1st.
Insert into test (user_name, user_birthday, sex) values(‘Bob’, to_date(‘770109’,’YYMMDD’,’M’);
Ok, now let’s check Bob’s birthday:
Select to_char(birthday,’YYYYMMDD’) as birthday from test where user_name=’Bob’;
Birthday
20770109
Ok, its 21st century now, don’t forget it.
How to operate on Clob:
Clob type is to save single-byte character data up to 4 gigabytes.
If you are a newer to use clob, you must find it’s not so easy to operate on it! Ok, take it easy, from now on I’ll give an example to insert, update, retrieve clob type values from test.
→ Insert new value for clob type column:
Because it is now allowed to insert string directly into Clob type column, we must insert an Empty_clob into the table.
Suppose we have established a connection to database, and the reference of the Connection object it conn.
String sql = “insert into test (user_name, user_birthday, sex, biography) values(‘Bob’, to_date(‘19770109’,’yyyymmdd’), ‘M’,EMPTY_CLOB())”;
PreparedStatement stmt = conn.prepareStatement(sql);
Stmt.execute();
Now an Empty_Clob object has be signed to biography.
To give real value of biography column, use the following code fragment:
sql = “select biography from test where user_name=’Bob’ for update”;
stmt = conn.prepareStatement(sql);
ResultSet rs = stmt.executeQuery();
Please remembered the “for update” clause, it’s very important. This particular clause means you told the database to prepare the biography of Bob for update.
Oracle.sql.CLOB clob = (oracle.sql.CLOB)((oracle.jdbc.OracleResultSet)rs).getCLOB("biography");
Java.io.OutputStream clobWriter = clob.getAsciiOutputStream();
byte[] temp = currentBiography.getBytes();
(supposed currentBiography is the value to be update to database)
clobWriter.write(temp);
clobWriter.flush();
clobWriter.close();
rs.close();
stmt.close();
conn.commit();
Ok, the biography has been updated.
→ When you retrieve clob from database, remember to use InputStream.
oracle.sql.CLOB clob = (oracle.sql.CLOB)rs.getClob("biography");
java.io.InputStream asciiStream = clob.getAsciiStream();
byte[] tempBody = new byte[1024]; //set to 1K;
int readLen = asciiStream.read(tempBody,0,1024);
String tempString = (new String(tempBody)).substring(0,readLen);
Be careful with PreparedStatement
When you use PreparedStatement to pre-compile and execute SQL String, you must close it after transaction ending. Otherwise you’ll got an exception named ORA 0001: maximum cursors exceeded!
I’m not sure about cursor, there is a parameter in the init.ora file which will used by oracle database. In this file, max cursors is defined.
Actually when try to prepare statement and execute in a loop block. You may also cause ORA 0001 exception.
So only prepare statement once for particular sql strings set. Use set XXX(int index, XXX value) method to set column values.