1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87
| CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "test_xml" AS
// Imports used in the class.
import oracle.jdbc.OracleTypes;
import java.sql.Connection;
import java.sql.SQLException;
import java.sql.DriverManager;
import oracle.xdb.XMLType;
import oracle.jdbc.OracleCallableStatement;
/**
* This class demonstrates how to obtain the xmltype value within JDBC returned
* by a database function. The database function - getPurchaseOrder has a return
* type of XMLTYPE.
*
* First of all, JDBC callable statement is used to call the database function.
* Later, a parameter is registered using CallableStatement.registerOutParameter()
* method to get the return value of function which is of XMLTYPE. Below is the
* runnable code.
*
* Make sure to change the database connection parameters in the class
* constructor to connect to your database.
*/
public class test_xml {
static Connection conn = null;
static OracleCallableStatement stmt = null;
/*
* Default constructor that takes no arguments; sets up the database connection
* using the parameters given within DriverManager.getConnection().
*/
public test_xml() throws Exception {
// Load Oracle driver
DriverManager.registerDriver (new oracle.jdbc.OracleDriver());
if (conn == null) {
// Connect to the local database
conn = DriverManager.getConnection
("jdbc:oracle:thin:@hostname:1521:SID",
"user", "pass");
}
}
public static XMLType manipulateXml(XMLType vs_msg_in) throws Exception {
test_xml getXMLTypeVal = new test_xml();
XMLType xml = doCall(vs_msg_in);
return xml;
}
public static XMLType doCall (XMLType vs_msg_in) throws Exception {
// Variable to hold the XMLTYPE value returned from the database function.
XMLType xml = null;
try {
// Create a string that has the SQL statement that calls the function.
String SQLTEXT = "{? = call PACKAGE_NAME.f_test_xml(?)}";
System.out.println("SQL := " + SQLTEXT);
// Call the database procedure.
stmt = (OracleCallableStatement)conn.prepareCall(SQLTEXT);
// Register the parameter as OracleTypes.OPAQUE that corresponds to XMLTYPE.
stmt.registerOutParameter (1, OracleTypes.OPAQUE,"SYS.XMLTYPE");
stmt.setOPAQUE(2, vs_msg_in);
System.out.println("Avant exécution");
stmt.execute();
System.out.println("Après exécution");
// Get the XMLTYPE value returned from database function.
xml = XMLType.createXML(stmt.getOPAQUE(1));
} catch (SQLException SQLe) {
if (stmt != null) {
stmt.close();
throw SQLe;
}
}
return xml;
}
}
;
/ |
Partager