Win a copy of Hello World this week in the Python forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
  • Campbell Ritchie
  • Paul Clapham
  • Bear Bibeault
  • Liutauras Vilda
  • Devaka Cooray
  • Knute Snortum
  • Junilu Lacar
  • Henry Wong
Saloon Keepers:
  • Ron McLeod
  • Stephan van Hulst
  • Tim Moores
  • Carey Brown
  • Tim Holloway
  • salvin francis
  • Frits Walraven
  • Piet Souris

Error with user defined object datatypes

Ranch Hand
Posts: 152
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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);}
Posts: 9612
Mac OS X Linux Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
Remember to always leap before you look. But always take the time to smell the tiny ads:
Java file APIs (DOC, XLS, PDF, and many more)
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!