CallableStatement statement = null;
ResultSet resultSet = null;
try {
MemTreeBuilder builder = context.getDocumentBuilder();
int iRow = 0;
statement = connection.prepareCall(plSql);
if(haveReturnCode)
{
statement.registerOutParameter(1, Types.NUMERIC);
}
if(resultSetPos != 0)
{
statement.registerOutParameter(resultSetPos, OracleTypes.CURSOR);
}
if(!args[2].isEmpty())
{
setParametersOnPreparedStatement(statement, parameters);
}
statement.execute();
if(haveReturnCode) {
int returnCode = statement.getInt(1);
if(returnCode != plSqlSuccess) {
LOG.error(plSql + " failed [" + returnCode + "]");
return( Sequence.EMPTY_SEQUENCE );
}
}
if(resultSetPos != 0) {
// iterate through the result set building an XML document
builder.startDocument();
builder.startElement(new QName("result", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
builder.addAttribute(new QName("count", null, null), String.valueOf(-1 ));
resultSet = (ResultSet)statement.getObject(resultSetPos);
ResultSetMetaData rsmd = resultSet.getMetaData();
int iColumns = rsmd.getColumnCount();
while (resultSet.next())
{
builder.startElement(new QName("row", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
builder.addAttribute(new QName("index", null, null), String.valueOf(resultSet.getRow()));
// get each tuple in the row
for(int i = 0; i < iColumns; i++)
{
String columnName = rsmd.getColumnLabel(i + 1);
if(columnName != null)
{
String colValue = resultSet.getString(i + 1);
String colElement = "field";
if(((BooleanValue)args[4].itemAt(0)).effectiveBooleanValue() && columnName.length() > 0)
{
// use column names as the XML node
/**
* Spaces in column names are replaced with
* underscore's
*/
colElement = SQLUtils.escapeXmlAttr(columnName.replace(' ', '_'));
}
builder.startElement(new QName(colElement, OracleModule.NAMESPACE_URI, OracleModule.PREFIX ), null);
if(!((BooleanValue)args[4].itemAt(0)).effectiveBooleanValue() || columnName.length() <= 0)
{
String name;
if(columnName.length() > 0) {
name = SQLUtils.escapeXmlAttr(columnName);
} else {
name = "Column: " + String.valueOf(i + 1);
}
builder.addAttribute(new QName("name", null, null), name);
}
builder.addAttribute(new QName("type", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), rsmd.getColumnTypeName(i + 1));
builder.addAttribute(new QName("type", Namespaces.SCHEMA_NS, "xs"), Type.getTypeName(SQLUtils.sqlTypeToXMLType(rsmd.getColumnType(i + 1))));
if(resultSet.wasNull())
{
// Add a null indicator attribute if the value was SQL Null
builder.addAttribute(new QName("null", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), "true");
}
if(colValue != null)
{
builder.characters(SQLUtils.escapeXmlText(colValue));
}
builder.endElement();
}
}
builder.endElement();
iRow++;
}
builder.endElement();
// Change the root element count attribute to have the correct value
NodeValue node = (NodeValue)builder.getDocument().getDocumentElement();
Node count = node.getNode().getAttributes().getNamedItem("count");
if(count != null)
{
count.setNodeValue(String.valueOf(iRow));
}
builder.endDocument();
// return the XML result set
return(node);
}
else
{
// there was no result set so just return an empty sequence
return( Sequence.EMPTY_SEQUENCE );
}
}
catch(SQLException sqle) {
LOG.error("oracle:execute() Caught SQLException \"" + sqle.getMessage() + "\" for PL/SQL: \"" + plSql + "\"", sqle);
//return details about the SQLException
MemTreeBuilder builder = context.getDocumentBuilder();
builder.startDocument();
builder.startElement(new QName("exception", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
boolean recoverable = false;
if(sqle instanceof SQLRecoverableException)
{
recoverable = true;
}
builder.addAttribute(new QName("recoverable", null, null), String.valueOf(recoverable));
builder.startElement(new QName("state", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
String sqlState = sqle.getSQLState();
if(sqlState != null) {
builder.characters(sqle.getSQLState());
}
else {
builder.characters("null");
}
builder.endElement();
builder.startElement(new QName("message", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
builder.characters(sqle.getMessage());
builder.endElement();
builder.startElement(new QName("stack-trace", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
ByteArrayOutputStream bufStackTrace = new ByteArrayOutputStream();
sqle.printStackTrace(new PrintStream(bufStackTrace));
builder.characters(new String(bufStackTrace.toByteArray()));
builder.endElement();
builder.startElement(new QName("oracle", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
builder.characters(SQLUtils.escapeXmlText(plSql));
builder.endElement();
int line = getLine();
int column = getColumn();
builder.startElement(new QName("xquery", OracleModule.NAMESPACE_URI, OracleModule.PREFIX), null);
builder.addAttribute(new QName("line", null, null), String.valueOf(line));
builder.addAttribute(new QName("column", null, null), String.valueOf(column));
builder.endElement();
builder.endElement();
builder.endDocument();
return (NodeValue)builder.getDocument().getDocumentElement();
}
finally {
release(connection, statement, resultSet);
}
}