To get LOB length
1. Connection
*. To open a connection using thin driver :
DriverManager.getConnection("jdbc:oracle:thin:@<mc-name>:<port-no>:<sid>",
"scott", "tiger");
OR
DriverManager.getConnection("jdbc:oracle:thin:@(description=(address=(host=<mc-name>)(protocol=tcp)(port=<port-no>))(connect_data=(sid=<sid>)))",
"scott","tiger");
*. To open a connection using OCI driver.
To use the default connection.
DriverManager.getConnection("jdbc:oracle:oci8:@", "scott","tiger");
Or
DriverManager.getConnection("jdbc:oracle:oci8:@(description=(address=(host=<mc-name>)(protocol=tcp)(port=<port-no>))(connect_data=(sid=<sid>)))",
"scott","tiger");
Or
DriverManager.getConnection("jdbc:oracle:oci8:@<tns-name>", "scott","tiger");
*. Closing a connection.
conn.close();
*. To set auto commit on.
conn.setAutoCommit(true)
*. To set the batch size to 100
((OracleConnection)conn).setDefaultExecuteBatch (100);
2. Statements
*. To create a statement
Statement stmt = conn.createStatement();
*. To create a prepared Statement
PreparedStatement pstmt =
conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (?, ?)");
*. To Create a callable statement.
CallableStatement procnone = conn.prepareCall ("begin procnone; end;"
*. To execute a SQL that returns a QUERY
ResultSet rset = stmt.executeQuery ("select ENAME from EMP");
*. To execute a DML that returns the no of rows affected
PreparedStatement pstmt =
conn.prepareStatement ("insert into EMP (EMPNO, ENAME) values (123, 'John')");
int rows = pstmt.executeUpdate ();
Ps. execute and executeQuery can also be called on a PreparedStatement.
They return a boolean and resultSet respectively.
PreparedStatement pstmt =
conn.prepareStatement ("select ENAME from EMP where EMPNO = ?");
pstmt.setInt(1,123);
ResultSet rset = pstmt.executeQuery();
*. To execute a DDL.
boolean status = stmt.execute("create table temp(col1 int)");
*. To Bind an IN variable.
PreparedStatement pstmt =
conn.prepareStatement ("select ENAME from EMP where EMPNO = ?");
pstmt.setInt(1,123);
ResultSet rset = pstmt.executeQuery();
* To Bind an OUT variable
CallableStatement funcin = conn.prepareCall ("begin ? := funcout (?); end;");
funcout.registerOutParameter (1, Types.CHAR);
funcout.registerOutParameter (2, Types.CHAR);
Where funcout is,
create or replace function funcout (y out char)
return char is
begin
y := 'tested';
return 'returned';
end;
* To set the batch size to 100
((OracleStatement)stmt).setRowPrefetch (100);
* To explicitly send the row to the server in batch mode.
int rows = ((OracleStatement)stmt).sendBatch();
3. ResultSets
* Fetch the next row
rset.next();
* Retrieve data from column i based on column no
rset.getInt(i);
* Based on Column Name
rset.getInt("EMPNO");
* Close the resultset
rset.close();
4. Streams: Long columns in JDBC are streamed.
* To set a long (Stream) column
pstmt.setAsciiStream (1, <input-stream>, <input-stream-length>);
If the string data is in Unicode format, then use setUnicodeStream.
pstmt.setUnicodeStream (1, <input-stream>, <input-stream-length>);
For long raw columns, use setBinaryStream
pstmt.setBinaryStream (1, <input-stream>, <input-stream-length>);
create table streamexample (data long)
PreparedStatement pstmt =
conn.prepareStatement ("insert into streamexample values (?)");
InputStream is = new FileInputStream ("notes.txt");
File file = new File ("notes.txt");
pstmt.setAsciiStream (1, is, (int)file.length ());
* To retrieve a long column
ResultSet rset =
stmt.executeQuery ("select * from streamexample");
InputStream ascii_data = rset.getAsciiStream (1);
// Loop, reading from the gif stream and writing to the file
int c;
while ((c = ascii_data.read ()) != -1)
Systemm.out.print(c);
5. Objects
* How to create a STRUCT type descriptor
StructDescriptor type_descriptor =
StructDescriptor.createDescriptor (<type name>, connection);
* How to create a STRUCT object
Object[] attributes = { ... };
STRUCT obj = new STRUCT (<type descriptor>, connection, attributes };
* How to create a SQL to Java type map entry.
map.put (<SQL Type Name>, <Java class which implements SQLData interface>);
* How to retrieve a STRUCT column.
1. As a SQLData object.
For example, to map SQL type "PERSON" to Java class "PersonObj.java"
which implements oracle.jdbc2.SQLData interface:
java.util.Dictionary map = new Hashtable (10);
map.put ("PERSON", Class.forName ("PersonObj"));
PersonObj obj = (PersonObj) rset.getObject (1, map);
2. As a CustomDatum object
CustomDatum obj = rset.getCustomDatum (1, <CustomDatum factory>);
3. As a oracle.sql.STRUCT object
STRUCT obj = (STRUCT) rset.getObject (1);
* How to retrieve STRUCT attributes.
1. With Connection default type map.
STRUCT struct = ...
Object[] attributes = struct.getAttributes ();
2. With the requested type map.
java.util.Dictionary map = ...
STRUCT struct = ...
Object[] attributes = struct.getAttributes (map);
3. Without map, return as oracle Datums.
Datum[] attributes = struct.getOracleAttributes ();
* How to create a ARRAY type descriptor
ArrayDescriptor type_descriptor =
ArrayDescriptor.createDescriptor (<type name>, connection);
* How to create a ARRAY object
Object[] elements = { ... };
ARRAY obj = new ARRAY (<type descriptor>, connection, elements };
* How to retrieve a ARRAY column.
ARRAY obj = ((OracleResultSet) rset).getARRAY (1);
* How to retrieve ARRAY elements.
ARRAY array = (ARRAY) rset.getObject (1);
Object[] elements = array.getArray ();
or
Object[] elements = array.getArray (<type map for elements>);
or
Object[] elements = array.getArray (<begin index>, <count>);
or
Object[] elements = array.getArray (<begin index>, <count>, <type map>);
* How to retrieve ARRAY elements as a ResultSet which holding the elements.
ARRAY array = (ARRAY) rset.getObject (1);
ResultSet array_rset = array.getResultSet ();
or
ResultSet array_rset = array.getResultSet (<type map for elements>);
or
ResultSet array_rset = array.getResultSet (<begin index>, <count>);
or
ResultSet array_rset = array.getResultSet (<begin index>, <count>, <type map>);
* How to retrieve a REF column.
REF obj = ((OracleResultSet) rset).getREF (1);
* To access the value which a REF points to.
REF ref = ((OracleResultSet)rset).getREF (1);
Object value = ref.getValue (); // use connection default map
or
REF ref = ((OracleResultSet) rset).getREF (1);
Object value = ref.getValue (<type map>); // use specified map
* To change the value which a REF points to.
REF ref = ((OracleResultSet) rset).getREF (1);
Object newValue = ...;
ref.setValue (newValue);
6. LOBS
* To read a piece of a LOB.
BLOB blob = ((OracleResultSet) rset).getBLOB (1);
byte[] bytes = blob.getBytes (<begin index>, <length>);
CLOB clob = ((OracleResultSet) rset).getCLOB (2);
String str = clob.getSubString (<begin index>, <length>);
BFILE bfile = ((OracleResultSet) rset).getBFILE (3);
byte[] bytes = bfile.getBytes (<begin index>, <length>);
* To read the LOB content as a stream.
BLOB blob = ((OracleResultSet) rset).getBLOB (1);
InputStream input_stream = blob.getBinaryStream ();
input_stream.read (...);
CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
Reader char_stream = Clob.getCharacterStream ();
char_stream.read (...);
CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
InputStream input_stream = Clob.getAsciiStream ();
input_stream.read (...);
BFILE bfile = ((OracleResultSet) rset).getBFILE (1);
InputStream input_stream = bfile.getBinaryStream ();
input_stream.read (...);
* To write a specified amount of data into a LOB.
BLOB blob = ((OracleResultSet) rset).getBLOB (1);
byte[] data = ...
int amount_written = blob.putBytes (<begin index>, data);
CLOB clob = ((OracleResultSet) rset).getCLOB (1);
String data = ...
int amount_written = clob.putString (<begin index>, data);
* To replace the LOB content from a stream.
BLOB blob = ((OracleResultSet) rset).getBLOB (1);
OutputStream output_stream = blob.getBinaryOutputStream ();
output_stream.write (...);
CLOB clob = ((OracleResultSet) rset).getCLOB (1);
Writer char_stream = Clob.getCharacterOutputStream ();
char_stream.write (...);
CLOB Clob = ((OracleResultSet) rset).getCLOB (1);
OutputStream output_stream = Clob.getAsciiOutputStream ();
output_stream.write (...);
* To get LOB length.
long length = blob.length ();
long length = clob.length ();
long length = bfile.length ();
Bookmark