Skip to content

Instantly share code, notes, and snippets.

@danieldbower
Created October 29, 2013 15:20
Show Gist options
  • Save danieldbower/7216673 to your computer and use it in GitHub Desktop.
Save danieldbower/7216673 to your computer and use it in GitHub Desktop.
Extract underlying Oracle connection from Jboss Wrapped connection, and create Oracle Clob Parameter
package com.bowerstudios.myapp.dao;
import java.io.Writer;
import java.sql.Connection;
import java.sql.SQLException;
import oracle.jdbc.OracleConnection;
import oracle.sql.CLOB;
import org.jboss.resource.adapter.jdbc.WrappedConnection;
public class DbUtil {
public static OracleConnection oracleConnectionFromPoolConnection(Connection conn){
if(conn instanceof OracleConnection){
return (OracleConnection) conn;
}else if(conn instanceof WrappedConnection){
WrappedConnection wrappedConn = (WrappedConnection) conn;
try {
return (OracleConnection) wrappedConn.getUnderlyingConnection();
} catch (SQLException e) {
e.printStackTrace();
}
}
throw new RuntimeException("Cannot create OracleConnection from pooled datasource connection");
}
public static CLOB getCLOB(String xmlData, Connection conn) throws SQLException{
conn = oracleConnectionFromPoolConnection(conn);
CLOB tempClob = null;
try{
// If the temporary CLOB has not yet been created, create one
tempClob = CLOB.createTemporary(conn, true, CLOB.DURATION_SESSION);
// Open the temporary CLOB in readwrite mode, to enable writing
tempClob.open(CLOB.MODE_READWRITE);
// Get the output stream to write
Writer tempClobWriter = tempClob.getCharacterOutputStream();
// Write the data into the temporary CLOB
tempClobWriter.write(xmlData);
// Flush and close the stream
tempClobWriter.flush();
tempClobWriter.close();
// Close the temporary CLOB
tempClob.close();
} catch(Exception exp){
if(tempClob!=null){
tempClob.freeTemporary();
}
exp.printStackTrace();
}
return tempClob;
}
}
package com.bowerstudios.myapp.dao;
import java.math.BigDecimal;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Types;
import javax.sql.DataSource;
import org.apache.log4j.Logger;
/**
* FUNCTION: F_MY_FUNCTION
* ARGUMENTS: p_tracking_id: Tracking ID of document
* p_xml: XML to submit
* RETURN: The doc_id
*/
public class MyStoredProcedure {
private static final Logger logger = Logger.getLogger(MyStoredProcedure.class);
public static final String STORED_PROCEDURE_NAME = "{call ? := MYSCHEMA.PKG_MYPKG.F_MY_FUNCTION(?,XMLType(?))}";
private DataSource ds;
public MyStoredProcedure(DataSource ds){
this.ds = ds;
}
public Long myFunction(Long trackingId, String xmlDoc){
Long docId = null;
Connection conn = null;
try {
conn = ds.getConnection();
CallableStatement cs = conn.prepareCall(STORED_PROCEDURE_NAME);
cs.registerOutParameter(1, Types.NUMERIC);
if(trackingId==null){
cs.setNull(2, Types.NUMERIC);
}else{
cs.setBigDecimal(2, new BigDecimal(trackingId));
}
cs.setObject(3, DbUtil.getCLOB(xmlDoc, conn));
cs.execute();
docId = cs.getLong(1);
} catch (SQLException e) {
logger.error("Error calling My Stored Procedure", e);
}finally{
if(conn !=null){
try {
conn.close();
} catch (SQLException e) {
logger.error("Error closing db connection after My Stored Procedure", e);
}
}
}
return docId;
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment