My Oracle Support Banner

JDBC Thick Versus Thin Driver Diffrent Results When Working With User Defined Types (Doc ID 1538163.1)

Last updated on MARCH 22, 2013

Applies to:

JDBC - Version 11.2.0.3.0 and later
Information in this document applies to any platform.

Symptoms

On : 11.2.0.3.0 version, JDBC OCI for Java

When using the thick driver in the following sample code, the error message: "ORA-04043: object "SILOA_TAR_APP"."REC1_T" does not exist" is thrown.
However when the exact same sample code is run with the JDBC Thin driver it works just fine.

ERROR
-----------------------

java.sql.SQLSyntaxErrorException: ORA-04043: object "SILOA_TAR_APP"."REC1_T" does not exist

  at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:765)
  at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:662)
  at oracle.jdbc.driver.T2CConnection.getForm(T2CConnection.java:840)
  at oracle.jdbc.oracore.OracleTypeADT.getNextTypeObject(OracleTypeADT.java:1149)
  at oracle.jdbc.oracore.OracleTypeADT.parseTDSrec(OracleTypeADT.java:741)
  at oracle.jdbc.oracore.OracleTypeADT.parseTDS(OracleTypeADT.java:685)
  at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:490)
  at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:471)
  at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:324)
  at oracle.sql.StructDescriptor.(StructDescriptor.java:254)
  at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:135)
  at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:103)
  at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:72)
  at TestTar.main(TestTar.java:86)

 

STEPS

The issue can be reproduced at will with the following steps:

1] Create the users as follows:

CREATE ROLE SILOA_TAR_OWN_EXEC_ROLE NOT IDENTIFIED
/

CREATE ROLE SILOA_TAR_OWN_READ_ROLE NOT IDENTIFIED
/

CREATE ROLE SILOA_TAR_OWN_ADMIN_ROLE NOT IDENTIFIED
/

GRANT SILOA_TAR_OWN_READ_ROLE TO SILOA_TAR_OWN_ADMIN_ROLE
/

GRANT SILOA_TAR_OWN_EXEC_ROLE TO SILOA_TAR_OWN_ADMIN_ROLE
/

CREATE USER SILOA_TAR_OWN
 IDENTIFIED BY SILOA_TAR_OWN
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK
/
GRANT CREATE TABLE TO SILOA_TAR_OWN
/
GRANT CREATE TRIGGER TO SILOA_TAR_OWN
/
GRANT CREATE ANY SYNONYM TO SILOA_TAR_OWN
/
GRANT CREATE SESSION TO SILOA_TAR_OWN
/
GRANT DROP ANY SYNONYM TO SILOA_TAR_OWN
/
GRANT CREATE PROCEDURE TO SILOA_TAR_OWN
/
GRANT CREATE TYPE TO SILOA_TAR_OWN
/
GRANT CREATE SEQUENCE TO SILOA_TAR_OWN
/

CREATE USER SILOA_TAR_APP
 IDENTIFIED BY SILOA_TAR_APP
 DEFAULT TABLESPACE USERS
 TEMPORARY TABLESPACE TEMP
 PROFILE DEFAULT
 ACCOUNT UNLOCK
/
GRANT SILOA_TAR_OWN_ADMIN_ROLE TO SILOA_TAR_APP
/
GRANT SILOA_TAR_OWN_EXEC_ROLE TO SILOA_TAR_APP
/
ALTER USER SILOA_TAR_APP DEFAULT ROLE ALL
/
GRANT CREATE SESSION TO SILOA_TAR_APP
/
GRANT CREATE SYNONYM TO SILOA_TAR_APP
/

 
2] Create the database objects under the siloa_tar_own user as follows:

conn siloa_tar_own/siloa_tar_own

CREATE OR REPLACE TYPE REC1_T
AS OBJECT ( COL1 VARCHAR2(50), COL2 CLOB );
/
GRANT EXECUTE ON REC1_T TO SILOA_TAR_OWN_EXEC_ROLE
/
CREATE OR REPLACE TYPE REC2_T
AS OBJECT ( COL1 VARCHAR2(50), COL2 VARCHAR2(50), COL3 VARCHAR2(50) );
/
GRANT EXECUTE ON REC2_T TO SILOA_TAR_OWN_EXEC_ROLE
/
CREATE OR REPLACE TYPE TAB_REC1_T AS TABLE OF REC1_T;
/
GRANT EXECUTE ON TAB_REC1_T TO SILOA_TAR_OWN_EXEC_ROLE
/
CREATE OR REPLACE TYPE TAB_REC2_T AS TABLE OF REC2_T;
/
GRANT EXECUTE ON TAB_REC2_T TO SILOA_TAR_OWN_EXEC_ROLE
/
CREATE OR REPLACE PROCEDURE PROC1(
 TAB_REC1_IN IN TAB_REC1_T
,TAB_REC2_IN IN TAB_REC2_T)
AS
BEGIN
 NULL;
END;
/
GRANT EXECUTE ON PROC1 TO SILOA_TAR_OWN_EXEC_ROLE
/

 
3] Create the private synonyms under the siloa_tar_app user as follows:

conn siloa_tar_app/siloa_tar_app

CREATE OR REPLACE SYNONYM REC1_T FOR SILOA_TAR_OWN.REC1_T
/
CREATE OR REPLACE SYNONYM REC2_T FOR SILOA_TAR_OWN.REC2_T
/
CREATE OR REPLACE SYNONYM TAB_REC1_T FOR SILOA_TAR_OWN.TAB_REC1_T
/
CREATE OR REPLACE SYNONYM TAB_REC2_T FOR SILOA_TAR_OWN.TAB_REC2_T
/
CREATE OR REPLACE SYNONYM PROC1 FOR SILOA_TAR_OWN.PROC1
/

 
4] Setup the environemt to run the program:

[hostname]$ export LD_LIBRARY_PATH=/u01/app/oracle/product/db/11.2.0.3.0/lib/
[hostname]$ export CLASSPATH=.:./ojdbc6.jar
[hostname]$ export PATH=/usr/java/jdk1.6.0_32_64/bin:$PATH

 
Use the oci driver with the following line in the Testtar.java file:

String connectionString = "jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mhernand-pc2.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=soa.us.oracle.com)))";

 

Testtar.java

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.util.List;
import java.util.Vector;

import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;
import oracle.sql.CLOB;
import oracle.sql.STRUCT;
import oracle.sql.StructDescriptor;

public class TestTar {

    /**
     * @param args
     */
    public static void main(String[] args) {
        // TODO Auto-generated method stub
        String driverName = "oracle.jdbc.driver.OracleDriver";
        //String connectionString =
        String connectionString = "jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=mhernand-pc2.us.oracle.com)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=soa.us.oracle.com)))";

        String userName = "SILOA_TAR_APP";
        String passWord = "SILOA_TAR_APP";
        Connection conn = null;
        CallableStatement cs = null;
        Object[] obj1 = null;
        Object[] obj2 = null;
        Object[] structObject = null;
        Object[] structObject2 = null;
        try {
            Class.forName(driverName);
        } catch (ClassNotFoundException e) {
            System.err.println("Exception during class.forname"
                    + e.getMessage());
            e.printStackTrace();
        }
        try {
            conn = DriverManager.getConnection(connectionString, userName,
                    passWord);
            DatabaseMetaData meta = conn.getMetaData();
            System.out.println("JDBC driver version is "
                    + meta.getDriverVersion());
        } catch (SQLException e) {
            System.err.println("Not able to establish connection ..."
                    + e.getMessage());
            e.printStackTrace();
        }

        List<Rec1VO> listRec1 = new Vector();
        Rec1VO record1 = new Rec1VO();
        record1.setCol1("1:Varchar2");
        record1.setCol2("1:Clob|Clob");
        listRec1.add(record1);
        record1 = new Rec1VO();
        record1.setCol1("2:Varchar2");
        record1.setCol2("2:Clob|Clob");
        listRec1.add(record1);
        record1 = new Rec1VO();
        record1.setCol1("3:Varchar2");
        record1.setCol2("3:Clob|Clob");
        listRec1.add(record1);

        List<Rec2VO> listRec2 = new Vector();
        Rec2VO record2 = new Rec2VO();
        record2.setCol1("1:Varchar2");
        record2.setCol2("1:Varchar2");
        record2.setCol3("1:Varchar2");
        listRec2.add(record2);
        record2 = new Rec2VO();
        record2.setCol1("2:Varchar2");
        record2.setCol2("2:Varchar2");
        record2.setCol3("2:Varchar2");
        listRec2.add(record2);
        record2 = new Rec2VO();
        record2.setCol1("3:Varchar2");
        record2.setCol2("3:Varchar2");
        record2.setCol3("3:Varchar2");
        listRec2.add(record2);
        try {
            ArrayDescriptor ad1 = ArrayDescriptor.createDescriptor(
                    "TAB_REC1_T", conn);
            StructDescriptor sd1 = StructDescriptor.createDescriptor("REC1_T",
                    conn);

            structObject = new Object[listRec1.size()];

            for (int j = 0; j < listRec1.size(); j++) {
                Rec1VO rec1 = listRec1.get(j);
                CLOB clobValue = CLOB.createTemporary(conn, true,
                        CLOB.DURATION_SESSION);
                clobValue.setString(1, rec1.getCol2());
                obj1 = new Object[2];
                obj1[0] = rec1.getCol1();
                obj1[1] = clobValue;
                structObject[j] = new STRUCT(sd1, conn, obj1);
            }

            ArrayDescriptor ad2 = ArrayDescriptor.createDescriptor(
                    "TAB_REC2_T", conn);
            StructDescriptor sd2 = StructDescriptor.createDescriptor("REC2_T",
                    conn);
            structObject2 = new Object[listRec2.size()];
            for (int j = 0; j < listRec2.size(); j++) {
                Rec2VO rec2 = listRec2.get(j);
                obj2 = new Object[3];
                obj2[0] = rec2.getCol1();
                obj2[1] = rec2.getCol2();
                obj2[2] = rec2.getCol3();
                structObject2[j] = new STRUCT(sd2, conn, obj2);
            }

            ARRAY arr1 = new ARRAY(ad1, conn, structObject);

            ARRAY arr2 = new ARRAY(ad2, conn, structObject2);

            cs = conn.prepareCall("{ call proc1(?,?)}");

            cs.setArray(1, arr1);
            cs.setArray(2, arr2);
            cs.execute();

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            if (cs != null) {
                try {
                    cs.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    // TODO Auto-generated catch block
                    e.printStackTrace();
                }
            }
        }
    }
}

 

Compile and run it giving the next error:

[oracle@mhernand-pc2 ReedElsevierGroupPlc]$ java TestTar
JDBC driver version is 11.2.0.3.0
java.sql.SQLSyntaxErrorException: ORA-04043: object "SILOA_TAR_APP"."REC1_T" does not exist

  at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:765)
  at oracle.jdbc.driver.T2CConnection.checkError(T2CConnection.java:662)
  at oracle.jdbc.driver.T2CConnection.getForm(T2CConnection.java:840)
  at oracle.jdbc.oracore.OracleTypeADT.getNextTypeObject(OracleTypeADT.java:1149)
  at oracle.jdbc.oracore.OracleTypeADT.parseTDSrec(OracleTypeADT.java:741)
  at oracle.jdbc.oracore.OracleTypeADT.parseTDS(OracleTypeADT.java:685)
  at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:490)
  at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:471)
  at oracle.sql.StructDescriptor.initPickler(StructDescriptor.java:324)
  at oracle.sql.StructDescriptor.(StructDescriptor.java:254)
  at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:135)
  at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:103)
  at oracle.sql.StructDescriptor.createDescriptor(StructDescriptor.java:72)
  at TestTar.main(TestTar.java:86)

 
Using the Thin driver does not give error:

[oracle@mhernand-pc2 ReedElsevierGroupPlc]$ java TestTar
JDBC driver version is 11.2.0.3.0

 

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Cause
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.