title: JDBC working with Oracle RECORD, TABLE and OBJECT types
body: <p>How to retrieve Oracle Collection Types in Jdbc. I searched a lot but not found any answer. Below is my stored procedure working fine :</p>
  
  <pre><code>create or replace PACKAGE SHOW_SUP AS
    TYPE SUP_COF_REC IS RECORD (
      SUPPLIER_NAME VARCHAR2(32),
      COFFEE_NAME VARCHAR(32)
    );
  TYPE_RECORD SUP_COF_REC;
  TYPE SUP_COF_TAB IS TABLE OF TYPE_RECORD%TYPE;
  PROCEDURE SHOW_SUPPLIERS (SUP_COF_DET OUT SUP_COF_TAB);
  END SHOW_SUP;
  
  create or replace PACKAGE BODY SHOW_SUP AS
  PROCEDURE SHOW_SUPPLIERS (SUP_COF_DET OUT SUP_COF_TAB) AS
   STR VARCHAR2(2000);
   BEGIN
    STR:='SELECT SUP_NAME, COF_NAME FROM SUPPLIERS S, COFFEES C WHERE
          S.SUP_ID=C.SUP_ID ORDER BY SUP_NAME';
    EXECUTE IMMEDIATE(STR) BULK COLLECT INTO SUP_COF_DET;
   END SHOW_SUPPLIERS;
  END SHOW_SUP;
  </code></pre>
  
  <p>Below is the Java Code to run stored proc:</p>
  
  <pre><code>String sql="{call SHOW_SUP.SHOW_SUPPLIERS(?)}";
  CallableStatement cs = con.prepareCall(sql);
  cs.registerOutParameter(1, java.sql.Types.ARRAY, "SUP_COF_TAB"); //LINE 3
  //cs.registerOutParameter(1, java.sql.Types.ARRAY, "SHOW_SUP.SUP_COF_TAB"); //LINE 4
  cs.execute();
  </code></pre>
  
  <p>Error I am receiving at LINE 3/4:</p>
  
  <pre><code>SQLException : invalid name pattern: SCOTT.SUP_COF_TAB
  java.sql.SQLException: invalid name pattern: SCOTT.SUP_COF_TAB
      at oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
      at oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:133)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:199)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:263)
      at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:271)
      at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:543)
      at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:462)
      at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1678)
      at oracle.sql.ArrayDescriptor.&lt;init&gt;(ArrayDescriptor.java:291)
      at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:206)
      at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:175)
      at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:158)
      at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:125)
      at oracle.jdbc.driver.NamedTypeAccessor.otypeFromName(NamedTypeAccessor.java:84)
      at oracle.jdbc.driver.TypeAccessor.initMetadata(TypeAccessor.java:89)
      at oracle.jdbc.driver.T4CCallableStatement.allocateAccessor(T4CCallableStatement.java:629)
      at oracle.jdbc.driver.OracleCallableStatement.registerOutParameterInternal(OracleCallableStatement.java:166)
      at oracle.jdbc.driver.OracleCallableStatement.registerOutParameter(OracleCallableStatement.java:207)
      at oracle.jdbc.driver.OracleCallableStatementWrapper.registerOutParameter(OracleCallableStatementWrapper.java:1584)
      at com.jdbc.callstatement.CallableStatementSample.main(CallableStatementSample.java:48)
  </code></pre>
  
  <p>I also tried to run a new stored procedure <em>without RECORD Type</em> but same error exists:</p>
  
  <pre><code>create or replace PACKAGE SHOW_COF AS
  TYPE COF_TAB IS TABLE OF COFFEES%ROWTYPE INDEX BY PLS_INTEGER;
  PROCEDURE SHOW_COFFEES (COF_DET OUT COF_TAB);
  END SHOW_COF;
  </code></pre>
  
  <p>I somewhere found that Array descriptor or struct descriptor can be used but how to used it in registerOutParameter, not any doc/site has explain. I got stuck here.</p>