Be careful with date type column
Just like YK 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 Jan st
Insert into test (user_name user_birthday sex) values(Bob to_date(YYMMDDM);
Ok now lets check Bobs birthday:
Select to_char(birthdayYYYYMMDD) as birthday from test where user_name=Bob;
Birthday
Ok its st century now dont forget it
How to operate on Clob:
Clob type is to save singlebyte character data up to gigabytes
If you are a newer to use clob you must find its not so easy to operate on it! Ok take it easy from now on Ill 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(yyyymmdd) MEMPTY_CLOB());
PreparedStatement stmt = connprepareStatement(sql);
Stmtexecute();
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 = connprepareStatement(sql);
ResultSet rs = stmtexecuteQuery();
Please remembered the for update clause its very important This particular clause means you told the database to prepare the biography of Bob for update
oraclesqlCLOB clob = (oraclesqlCLOB)((oraclejdbcOracleResultSet)rs)getCLOB(biography);
javaioOutputStream clobWriter = clobgetAsciiOutputStream();
byte[] temp = currentBiographygetBytes();
(supposed currentBiography is the value to be update to database)
clobWriterwrite(temp);
clobWriterflush();
clobWriterclose();
rsclose();
stmtclose();
mit();
Ok the biography has been updated
→ When you retrieve clob from database remember to use InputStream
oraclesqlCLOB clob = (oraclesqlCLOB)rsgetClob(biography);
javaioInputStream asciiStream = clobgetAsciiStream();
byte[] tempBody = new byte[]; //set to K;
int readLen = asciiStreamread(tempBody);
String tempString = (new String(tempBody))substring(readLen);
Be careful with PreparedStatement
When you use PreparedStatement to precompile and execute SQL String you must close it after transaction ending Otherwise youll got an exception named ORA : maximum cursors exceeded!
Im not sure about cursor there is a parameter in the initora 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 exception
So only prepare statement once for particular sql strings set Use set XXX(int index XXX value) method to set column values
