title: Get oracle Java Stored Procedure to return a table
body: <p>I am trying to write an oracle Java stored procedure to return a data structure that can be used as a table to so selects against.  The final outcome must look something like this:</p>
  
  <pre><code>select * from TABLE(get_table('astring','anotherstring')); 
  </code></pre>
  
  <p>I have defined the oracle types like this:</p>
  
  <pre><code>CREATE OR REPLACE TYPE  "TEST_JAVA_TYPE" IS OBJECT ( 
    name                  VARCHAR2(255),
    value                 VARCHAR2(255)
  )
  CREATE OR REPLACE TYPE "TEST_JAVA_TYPE_TABLE" AS TABLE OF TEST_JAVA_TYPE;
  </code></pre>
  
  <p>My java code looks like this:</p>
  
  <pre><code>import java.util.ArrayList;
  import java.util.List;
  
  public class TableTester {
  
  public static ARRAY getTable() {
      List&lt;TEST_JAVA_TYPE&gt; list = new ArrayList&lt;TEST_JAVA_TYPE&gt;();
      TEST_JAVA_TYPE t1 = new TEST_JAVA_TYPE();
      TEST_JAVA_TYPE t2 = new TEST_JAVA_TYPE();
      t1.name = "Joe";
      t1.value = "A";
      t2.name = "Jim";
      t2.value = "B"; 
      list.add(t1);
      list.add(t2);
      Connection conn = DriverManager.getConnection("jdbc:default:connection:");   
      ArrayDescriptor dsc = ArrayDescriptor.createDescriptor("TEST_JAVA_TYPE_TABLE", conn);  
  
      return new ARRAY(dsc, conn, list.toArray()); 
  }
  
  static class TEST_JAVA_TYPE {
      public String name;
      public String value;
  }
  
  }
  </code></pre>
  
  <p>And I call the java code like this:</p>
  
  <pre><code>FUNCTION get_table(conn_type VARCHAR2, cube_name VARCHAR2) RETURN                
  TEST_JAVA_TYPE_TABLE AS LANGUAGE JAVA
  NAME 'TableTester.getTable() return oracle.sql.ARRAY';
  </code></pre>
  
  <p>When I run this I get </p>
  
  <pre><code>ORA-00932: inconsistent datatypes: expected a Java type at argument position 2 to which some Oracle value can be converted got something else
  </code></pre>
  
  <p>How do i convert/map  this properly?</p>