Announcement Announcement Module
Collapse
No announcement yet.
Problem with Oracle, SqlData, ARRAY and STRUCT using OPAQUE and XMLType classes Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Problem with Oracle, SqlData, ARRAY and STRUCT using OPAQUE and XMLType classes

    We're accessing a PL/SQL layer that uses STRUCT and ARRAY (as described here http://docs.spring.io/spring-data/jd...datatypes.html) and everything works fine as long as we do not try to pass an XML column in the STRUCT return parameter.

    Here's the code that would read the column into a Oracle native XMLType (proven several times via web search):
    Code:
     public void readSQL(SQLInput sqlInput, String string) throws SQLException {
              setFunctionId(sqlInput.readLong());
              OPAQUE opaque = (OPAQUE) sqlInput.readObject(); // this works, we get an OPAQUE back
              setFunctionSpec(XMLType.createXML(opaque).getStringVal()); // this fails with a NullPointerException inside Oracle JDBC extensions
              System.out.println(opaque.getConnection().isClosed()); // this return "true", so the connection behind is closed
           ....
    What we found out so far is that when XMLType (which is an Oracle specific class, we had to use all the cruel XML libraries from the JDBC and XML packages of our Oracle instance) tries to create an XMLType instance from the valid OPAQUE instance, it fails as it cannot connect to the JDBC connection to load the data for the OPAQUE representation. When we check if the connection behind the OPAQUE instance is closed, we get a "true", which indicates the following:
    • all data returned from the PL/SQL call is loaded by the JDBC driver at once, which is totally different to executing an SQL query, where you get a cursor back
    • the connection (at least the Oracle representation inside the JDBC driver) is closed even though the transaction is still open - I have no idea how to better explain this. The transaction border is OUTSIDE of the SqlData instance, but the connection cannot be used for reading anymore, obviously due to how the JDBC driver reads results from a PL/SQL call
    We use Oracle 11.2.0.3 (and its drivers), we also tried the 12.x drivers with hope that they handle this better - same result.

    Anyone an idea how we could get the content represented by the OPAQUE instance?

    PS: Our plan B is to convert the XML into a CLOB inside the PL/SQL code/query, this would work but we are seeking for a correct solution anyways.

  • #2
    Can't say I've ever tried using XML type and a STRUCT, so not sure what the issue is here. Do you have an example of the procedure declaration?

    Comment


    • #3
      Here's the stored procedure definition:

      Code:
      PROCEDURE LOAD(P_PARAM IN OUT T_DRG_MIG_FUNCTIONS_CL
      ,P_FILTER IN T_SCM_FILTER := T_SCM_FILTER()
      ,P_ORDERBY IN T_SCM_ORDERBY := T_SCM_ORDERBY()
      ,P_START_ROW IN NUMBER := 0
      ,P_ROW_COUNT IN NUMBER := 0
      ,P_TOTAL_FILTERED_ROWS OUT NUMBER) IS V_CMD CLOB := NULL;
      The collection/ARRAY is defined in such ways:
      Code:
      create or replace TYPE                    "T_DRG_MIG_FUNCTIONS_CL" IS TABLE OF O_DRG_MIG_FUNCTIONS_CL
      here's the STRUCT definition:
      Code:
      create or replace TYPE                    "O_DRG_MIG_FUNCTIONS_CL" AS OBJECT(FUNCTION_ID NUMBER
      ,FUNCTION_SPEC XMLTYPE
      ,FUNCTION_NAME VARCHAR2(35)
      ,FUNCTION_RESULT_TYPE VARCHAR2(25)
      ,FUNCTION_VERSION NUMBER
      ,FUNCTION_DATA XMLTYPE
      ,VIEW_HEADER VARCHAR2(4000)
      ,LMTS DATE
      ,FILENAME VARCHAR2(500)
      )

      Comment

      Working...
      X