File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes Error with user defined object datatypes Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "Error with user defined object datatypes" Watch "Error with user defined object datatypes" New topic

Error with user defined object datatypes

clyde melly
Ranch Hand

Joined: Sep 04, 2003
Posts: 152
Trying to use user defined Object datatypes in oracle.Then using java
to insert values.I get an error
java.sql.SQLEXception : dataUnknown SQL type for
PreparedStatement.setObject(SQL type=1111)
Code :
import java.sql.*;
public class Person implements SQLData,Serializable
private int person_id;
private String last_name;
private String first_name;
private java.sql.Date birth_date;
private String gender;
public Person()
{ }
//SqlData interface
public String getSQLTypeName() throws SQLException
public void readSQL(SQLInput stream,String type) throws SQLException
person_id = stream.readInt();
last_name = stream.readString();
first_name = stream.readString();
birth_date = stream.readDate();
gender = stream.readString();

public void writeSQL(SQLOutput stream) throws SQLException
public int getPersonId()
return person_id;
public String getLastName()
return last_name;
public String getFirstName()
return first_name;
public java.sql.Date getBirthDate()
return birth_date;
public String getGender()
return gender;

public void setPersonId(int person_id)
this.person_id = person_id;
public void setLastName(String last_name)
this.last_name = last_name;
public void setFirstName(String first_name)
this.first_name = first_name;
public void setBirthDate(java.sql.Date birth_date)
this.birth_date = birth_date;
public void setGender(String gender)
this.gender = gender;

public static void main(String g[])

Statement stmt=null;
PreparedStatement pstmt =null;
con=DriverManager.getConnection("jdbc dbc:calvin","scott","tiger");

stmt = con.createStatement ();

stmt.execute ("CREATE TYPE o AS OBJECT(pno number(4),lname
varchar2(10),fname varchar2(10),birth_date date,sex varchar2(1))");

stmt.execute ("CREATE TABLE PERSOBJECT(pobj o)");

// Create a new instance for person class
Person p = new Person();
// Insert into an object table
pstmt=con.prepareStatement("insert into PERS_OBJECT values(?)");


int rows = pstmt.executeUpdate();

System.out.println("Rows : " + rows);

pstmt = null;
catch(Exception e){System.out.println("ex : " +e);}
Joe Ess

Joined: Oct 29, 2001
Posts: 9189

My experience with Oracle objects is scant, but it looks like you are playing fast and loose with the names you are using. Your Java type returns "SCOTT.PERSON_TYPE" for its sql type, you create a database type called "o", create a table named "PERSOBJECT", then try to insert to a table named "PERS_OBJECT". I'm pretty sure all these names should be the identical.
The example that I have seen also makes a Hashmap to relate the Java type's SQL name to the Java type's class:
hmap.put("SOMEDBNAME.SOMETYPENAME", SomeClass.class);
This map is used to tell the database know how to relate its database type to the Java class (called right after obtaining the Connection):
There's some great documentation at the Oracle Technology Network. I'm sure they have a tutorial there.

[How To Ask Questions On JavaRanch]
I agree. Here's the link:
subject: Error with user defined object datatypes
It's not a secret anymore!