StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POHow to deal with VARRAYs (Oracle 9i) in JDBC?
text
Body
copied!<p>I am trying to write a small program which has to store and retrieve values from database. The database is object relational.</p> <p>Here're the DDL statements :</p> <pre><code>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) </code></pre> <p>And this is my Java code :</p> <pre><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(); } } </code></pre> <p>The code compiles fine. But when I try to insert values in database, it gives the following error</p> <blockquote> <p>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></p> </blockquote> <p>I think there's something wrong with the way I have handled the VARRAY. Please help me...</p> <p>Thank you! :-)</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload