How to deal with VARRAYs (Oracle 9i) in JDBC ?

Question

Hi,

I am trying to write a small program which has to store and retrieve values from database. The database is object relational.

Here're the DDL statements :

create or replace type Guy as object (name varchar(80), address varchar(80), dateOfBirth date)

create or replace type KnownLanguages as varray(10) of varchar(80)

create table Coders (person_ Guy, description clob, knownLanguages_ KnownLanguages)

And this is my Java code :

package adbms;

import java.sql.Clob;
import java.sql.Connection;
import java.sql.Date;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Scanner;

import oracle.jdbc.OracleDriver;
import oracle.jdbc.driver.OraclePreparedStatement;
import oracle.jdbc.driver.OracleResultSet;
import oracle.sql.ARRAY;
import oracle.sql.ArrayDescriptor;

public class SimpleJdbcApplication {
    public static final String DRIVER_TYPE = "thin";
    public static final String HOST_NAME = "localhost";
    public static final int PORT = 1521;
    public static final String DATABASE_SID = "ralphdb";
    public static final String USER_NAME = "scott";
    public static final String PASSWORD = "tiger";
    public static final String URL = String.format("jdbc:oracle:%s:@%s:%d:%s", DRIVER_TYPE, HOST_NAME, PORT, DATABASE_SID);

    private static Connection connection;
    private static Scanner read;
    private static PreparedStatement insertStatement;
    private static PreparedStatement deleteStatement;
    private static PreparedStatement updateStatement;
    private static PreparedStatement displayStatement;

    static {
     try {
      System.out.println(URL);
      read = new Scanner(System.in);
      DriverManager.registerDriver(new OracleDriver());
      connection = DriverManager.getConnection(URL, USER_NAME, PASSWORD);
      insertStatement = connection.prepareStatement("insert into Coders values( Guy(?,?,?) , ? , ?)");
      deleteStatement = connection.prepareStatement("delete from Coders where person_.name=?");
      updateStatement = connection.prepareStatement("update Coders set person_.name=? where person_.name=?");
      displayStatement = connection.prepareStatement("select * from Coders");
     } catch (Exception x) {
      x.printStackTrace();
     }
    }

    public static void main(String... args) throws SQLException {
     while (true) {
      System.out.println("Enter your choice.");
      System.out.println("1. Insert");
      System.out.println("2. Delete");
      System.out.println("2. Update");
      System.out.println("4. Display");
      System.out.println("5. Enter a direct query");
      System.out.println("6. Exit");
      switch (read.nextInt()) {
       case 1:
        insertCoder();
        break;
       case 2:
        removeCoder();
        break;
       case 3:
        updateCoder();
        break;
       case 4:
        displayAllCoders();
        break;
       case 5:
        processDirectQuerry();
        break;
       default:
        System.exit(0);
      }
     }
    }

    private static void processDirectQuerry() throws SQLException {
     System.out.println("Enter the query.");
     String query = read.nextLine();
     connection.createStatement().execute(query);
    }

    private static void displayAllCoders() throws SQLException {
     ResultSet resultSet = displayStatement.executeQuery();
     System.out.println("Name\tAddress\tDate of birth\tDescription\tKnown Languages");
     while (resultSet.next()) {
      System.out.print(resultSet.getString("person_.name")+"\t");
      System.out.print(resultSet.getString("person_.address")+"\t");
      System.out.print(resultSet.getDate("person_.dateOfBirth")+"\t");
      Clob description = resultSet.getClob("description");
      System.out.print(description.getSubString(1L, (int) description.length()));
      ARRAY oraArray = ((OracleResultSet) resultSet).getARRAY("knownLanguages_");
      String[] languagesArray = (String[]) oraArray.getArray();
      for (String language : languagesArray) {
       System.out.print(language + " ");
      }
      System.out.println();
     }
    }

    private static void updateCoder() throws SQLException {
     System.out.println("Enter the name of the coder whose name is to be updated.");
     String originalName = read.nextLine();
     System.out.println("ENter the new name.");
     String newName = read.nextLine();
     updateStatement.setString(1, newName);
     updateStatement.setString(2, originalName);
     updateStatement.executeUpdate();
    }

    private static void removeCoder() throws SQLException {
     System.out.println("Enter the name of coder to be removed.");
     String name = read.nextLine();
     deleteStatement.setString(1, name);
     deleteStatement.executeUpdate();
    }

    private static void insertCoder() throws SQLException {
     read = new Scanner(System.in);
     System.out.println("Enter the name, address and date of birth.");
     String name = read.nextLine();
     String address = read.nextLine();
     String dateOfBirth = read.nextLine();
     System.out.println("Enter a brief description.");
     String description = read.nextLine();
     List<String> languagesList = new ArrayList<String>();
     System.out.println("Enter names of known languages. (At most 10.) Type 'done' when you're done.");
     String token = null;
     int i = 0;
     while (i < 10 && !(token = read.nextLine()).equalsIgnoreCase("done")) {
      languagesList.add(token);
                    i++;
     }
     insertStatement.setString(1, name);
     insertStatement.setString(2, address);
     insertStatement.setDate(3, Date.valueOf(dateOfBirth));
     insertStatement.setString(4, description);
     //String[] languagesArray = (String[]) languagesList.toArray();
     Object[] languagesArray = languagesList.toArray();
     ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("KnownLanguages", connection);
     ARRAY oraArray = new ARRAY(arrayDescriptor, connection, languagesArray);
     ((OraclePreparedStatement) insertStatement).setARRAY(5, oraArray);
     insertStatement.executeUpdate();
    }
}

The code compiles fine. But when I try to insert values in database, it gives the following error

F:\adb>java SimpleJdbcApplication jdbc:oracle:thin:@127.0.0.1:1521:ralphdb Enter your choice. 1. Insert 2. Delete 2. Update 4. Display 5. Enter a direct query 6. Exit 1 Enter the name, address and date of birth. rahul mumbai 1989-12-22 Enter a brief description. happy Enter names of known languages. (At most 10.) Type 'done' when you're done. c++ c java done Exception in thread "main" java.sql.SQLException: invalid name pattern: SYSTEM.K nownLanguages at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :112) at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java :146) at oracle.jdbc.oracore.OracleTypeADT.initMetadata(OracleTypeADT.java:463 ) at oracle.jdbc.oracore.OracleTypeADT.init(OracleTypeADT.java:362) at oracle.sql.ArrayDescriptor.initPickler(ArrayDescriptor.java:1756) at oracle.sql.ArrayDescriptor.(ArrayDescriptor.java:272) at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:196) at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:165) at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:150) at oracle.sql.ArrayDescriptor.createDescriptor(ArrayDescriptor.java:115) at SimpleJdbcApplication.insertCoder(SimpleJdbcApplication.java:143) at SimpleJdbcApplication.main(SimpleJdbcApplication.java:62) F:\adb>

I think there's something wrong with the way I have handled the VARRAY. Please help me...

Thank you! :-)

Answer 1

It appears to be this line throwing the exception:

ArrayDescriptor arrayDescriptor = ArrayDescriptor.createDescriptor("KnownLanguages", connection);

I think the issue is that it is not resolving the type name in the database. I believe I found in the past that the type lookup is case-sensitive, and by default in Oracle the type name is all upper-case. So try using "KNOWNLANGUAGES" in the above line.

Also, it appears to be looking in the SYSTEM schema, although I don't know why. If the type is not owned by SYSTEM, you might need to explicitly specify that in the the descriptor call as well (e.g. "SCOTT.KNOWNLANGUAGES").