aspose file tools*
The moose likes JDBC 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

Win a copy of Spring in Action this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC
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: 8927

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.

"blabbing like a narcissistic fool with a superiority complex" ~ N.A.
[How To Ask Questions On JavaRanch]
I agree. Here's the link:
subject: Error with user defined object datatypes