How to obtain an XMLTYPE value returned by a database function using JDBC

Date: 21-May-2003

Objective

After reading this document, you should be able to:

• Obtain an XMLTYPE value returned by a database function using JDBC and,
  
• Run the sample java class using the instructions provided.

Software Requirements

• JDK1.3.x or above. This can be downloaded from here.
• Oracle9i Database Release 2 or later. This can be downloaded from here.
  

Note: The sample java class given along with this document will use JDBC driver classes12.jar, Oracle XML DB jar: xdb.jar and xmlparserv2.jar to compile and run. All of these are available with Oracle Database installation. If you have a database installation, you need not download them separately; else they have to be downloaded from OTN. xdb.jar and xmlparserv2.jar are available with Oracle XDK for Java download.

This sample is tested and certified against Linux Red Hat Advanced Server 2.1.

Description

Suppose there is a database function which has an XMLTYPE as its return value. To obtain the value of this XMLTYPE from JDBC,

• Call a database function, CallableStatement is used.
• A bind parameter has to be used to obtain the return value from a database function. Use the method CallableStatement.registerOutParameter() to bind the value to the parameter, with OracleTypes.OPAQUE as the datatype for the XMLTYPE out parameter from the database function.
• Use the oracle.sql.XMLTYPE class to create the XMLTYPE object and get the return value using the method: getOPAQUE().

-- An example table of XMLTYPE
CREATE TABLE PURCHASEORDER OF XMLTYPE;

-- Insert a record to the XMLTYPE table.

INSERT INTO purchaseorder VALUES(XMLTYPE('
 <PurchaseOrder>
  <Reference>ADAMS-20011127121040988PST</Reference>
  <Actions>
   <Action>
   <User>SCOTT</User>
   <Date>2002-03-31</Date>
  </Action>
 </Actions>
 </PurchaseOrder>')); 

-- Create a database function that returns the inserted record as XMLTYPE.
CREATE OR  REPLACE FUNCTION getPurchaseOrder(reference VARCHAR2)
  RETURN XMLTYPE AS
    xml XMLTYPE;
  BEGIN
    SELECT VALUE(p)
    INTO xml
    FROM PURCHASEORDER p
    WHERE EXTRACTVALUE(VALUE(p),'/PurchaseOrder/Reference') = reference;
    RETURN xml;
  END;
/

commit;

We shall now look into the way of calling this database function from JDBC using callable statement and obtain the XMLTYPE. Below is the code snippet for the same:

Code within JDBC class to get XMLTYPE returned from database function:

    .............
    .............
    import oracle.jdbc.OracleTypes;
    ..........
    import oracle.xdb.XMLType;
    ...........
    import oracle.jdbc.OracleCallableStatement;
    ..........

    // Variable to hold the XMLTYPE value returned from the database function.


    XMLType xml = null;
    ............
    .............
    // Create a string that has the SQL statement that calls the function.
    String SQLTEXT = "{? = call getPurchaseOrder('ADAMS-20011127121040988PST')}";
    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.execute();

    // Get the XMLTYPE value returned from database function.
    xml = XMLType.createXML(stmt.getOPAQUE(1));
    System.out.println(((XMLType)xml).getStringVal());
    .............
    .............

Running the Java class

• Copy the full source code to a directory and save it as GetXMLTypeVal.java
  
• Run the SQL script listed above to create the table and the database function as SCOTT user in the database.
• Edit GetXMLTypeVal.java and use your own database parameters in the class constructor.
  // Connect to the database.
  conn = DriverManager.getConnection
  ("jdbc:oracle:thin:@<hostname>:<port>:<sid>",
  "scott", "tiger");
  
• From a command prompt in the copied directory, set the classpath to include:
  • The Oracle JDBC driver class: classes12.zip or classes12.jar obtainable from Oracle database installation, under <ORACLE_HOME>/jdbc/lib directory.
    Note:<ORACLE_HOME> is the directory where Oracle database is installed.
    Example: D:\oracle on windows, /opt/oracle on Linux.
    
  • The Oracle XML DB jar: xdb.jar obtainable from Oracle database installation, under <ORACLE_HOME>/rdbms/jlib directory.
    
  • xmlparserv2.jar obtainable from Oracle database installation, under <ORACLE_HOME>/lib directory.
    
• Compile the GetXMLTypeVal class.
  Example:
  javac -d . GetXMLTypeVal.java
• Run the class by giving values to the parameters.
  java GetXMLTypeVal
  
• Check to see if the output is same as the purchase XML document in the database by running the following query:
  SQL> SELECT VALUE(t) FROM purchaseorder t;

Resources

• Oracle9i XML Database Developer's Guide - Oracle XML DB Release 2 (9.2)
  

Summary

This document has tried to explain and illustrate how to obtain an XMLTYPE value returned by a database function within JDBC.