DevOps, CI/CD and Automation

Hi,

Please bear with me if this is a trivial question. I'm brand new to Java, JDBC, and XML DB. I've been spinning my wheels on this for a few hours now.

I have a table:
XMLDB_USER@xmldb64> desc my_test_table
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(9)
 DOC                                                SYS.XMLTYPE(XMLSchema "http:
                                                    //www.myproquest.com/Global_
                                                    Schema_v3.0.xsd" Element "RE
                                                    CORD") STORAGE BINARY

I've written a trivially simple PL/SQL function to retrieve the DOC column, based on the ID passed to it.

create or replace function get_doc(goid in number) return xmltype is
  doc_content xmltype;
begin
  select doc into doc_content from my_test_table where id = goid;
  return doc_content;
end;
/

Up to here, everything works fine. I wrote a small anonymous PL/SQL block, and proved that I can call the PL/SQL function successfully.

Now, I want to implement in Java. This is where I get confused.

Currently my code looks like this:

import java.sql.*;
import oracle.jdbc.*;
import oracle.jdbc.OracleTypes;
import oracle.jdbc.pool.OracleDataSource;
import oracle.xdb.*;

import java.io.*;
import java.lang.StringBuilder;
import java.sql.Clob;

class MyJava
{
  public static void main(String args[]) throws SQLException, IOException
  {
    Clob xml = null;
    System.out.print("Connecting to the database...");
    System.out.flush();
    OracleDataSource ods = new OracleDataSource();
    String URL = "jdbc:oracle:thin:@//pqhdb201.aa1.pqe:1522/xmldb64";
    ods.setURL(URL);
    ods.setUser("xmldb_user");
    ods.setPassword("xmldb_user");
    Connection conn = ods.getConnection();
    System.out.println("connected.");
    System.out.println("GOID to be retrieved is " + args[0]);
    CallableStatement cs1 = null;
 try {
    cs1 = conn.prepareCall( "{? = call get_doc (?)}" );
    cs1.registerOutParameter(1,OracleTypes.OPAQUE,"XMLType");
    cs1.setString(2,args[0]);
    cs1.execute();
//    xml = XMLType.createXML(cs1.getOPAQUE(1));
    xml = (Clob) cs1.getObject(1);
//    System.out.println(((XMLType)xml).getStringVal());
//    System.out.println(xml.getClobVal());
    System.out.println(xml.getCharacterStream());
   }
  finally {
     if(cs1!=null) cs1.close();
     if(conn!=null) conn.close();
          }

  }
}

This compiles, but when I run, I get:

pqhdb201:[xmldb64]:(/home/oracle/xmldb_scripts):$java MyJava 91455713
Connecting to the database...connected.
GOID to be retrieved is 91455713
Exception in thread "main" java.sql.SQLException: invalid name pattern: XMLDB_USER.XMLType
        at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:553)
        at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:469)
        at oracle.sql.OpaqueDescriptor.initPickler(OpaqueDescriptor.java:237)
        at oracle.sql.OpaqueDescriptor.<init>(OpaqueDescriptor.java:104)
        at oracle.sql.OpaqueDescriptor.createDescriptor(OpaqueDescriptor.java:220)
        at oracle.sql.OpaqueDescriptor.createDescriptor(OpaqueDescriptor.java:181)
        at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:83)
        at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:89)
        at oracle.jdbc.driver.T4CCallableStatement.allocateAccessor(T4CCallableStatement.java:689)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:157)
        at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:202)
        at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1356)
        at MyJava.main(MyJava.java:29)

As you can see from the commented lines, I've tried several different approaches, based on various examples from Oracle documentation and various samples found on the net. My problem, I think, is that I have a fundamental disconnect somewhere. Objects? Types? Different methods? getClobVal? getStringVal? getCharacterStream? I'm still trying to get up to speed on all the lingo and terminology, and, as a result, I suspect I'm asking the wrong questions, and getting wrong results.

Based on the simple example above, my table and PL/SQL function definitions, can someone help me fill in the gaps of what I'm missing in terms of the Java code?

Thanks!

-Mark