Win a copy of Programmer's Guide to Java SE 8 Oracle Certified Associate (OCA) this week in the OCAJP forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Call to Oracle SP with Oracle object type parameter

 
Kavita Bopardikar
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi!
Following is java code & also the oracle procedure. I am using JDBC thin client.
I am passing an IN OUT parameter which is registered. Since it is of type oracle object, i am registering as Types.STRUCT. But, it fails:
I am getting error - catch : java.lang.ClassCastException: oracle.sql.STRUCT
Java code:
import java.io.*;
import java.sql.*;
public class PassObject
{
String TaskName;
int TaskID;
PassObject(int id, String nm)
{
TaskID = id;
TaskName = nm;
}
PassObject()
{
TaskID = -1;
TaskName = null;
}
public static void main(String args[])
{
PassObject po = new PassObject(1,"Kav");
po = po.setDetails();
System.out.println("Task ID : " + po.TaskID);
System.out.println("Task Name : " + po.TaskName);
}

public PassObject setDetails()
{
Connection conn = null;
ConnectionPool mypool = null;
ResultSet rs = null;
Statement st = null;
String query = null;
ResultSetMetaData rsmd = null;
PassObject nw = new PassObject();
CallableStatement cs = null;
nw = this;
try
{
mypool = ConnectionPool.CreatePool("jdbc racle:thin:@nims:1521:nimd07",
"nims",
"sminful",
"",
1,
1);
conn = mypool.getConnection();
//CallableStatement cs = conn.prepareCall("{call kb_pr(?)}");
System.out.println("Here");
//cs.setObject(1,nw,Types.STRUCT);
System.out.println("Here 2");
cs = conn.prepareCall("BEGIN NIMS.kb_pr(?); END;");
cs.registerOutParameter(1, Types.STRUCT, "KB_COMP");
cs.executeQuery();
Struct p = (Struct) cs.getObject(1);
nw = (PassObject) p;
//System.out.println("Return Value = " + p);
System.out.println("Return Value = " + nw.TaskID);
cs.close();
}
catch(Exception e)
{
System.out.println("catch : " + e);
}
finally
{
mypool.returnConnection(conn);
return nw;
}
}
}

STORED PROCEDURE:
procedure kb_pr(fn_comp out kb_comp)
is
BEGIN
fn_comp := kb_comp('Changed',10);
end;
ORACLE OBJECT STRUCTURE
type kb_comp as object
(Name VARCHAR2(30),
ID NUMBER)

Please Help...
Thanks.
 
sanjay yermalkar
Greenhorn
Posts: 23
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi kavita,
I don't know why this casting should work:
nw = (PassObject) p;

I tried using methods in the interface Struct and it worked.
<code>
cs.executeQuery();
Struct p = (Struct) cs.getObject(1);
//nw = (PassObject) p;
Object[] array = p.getAttributes();
System.out.println("first:" + array[0] + "\n second : " + array[1] );
</code>
You may write a constructor(or method) for the class PassObject
which takes "Struct" and fills up your instance variables.
Please, let me know if you find something different.

[This message has been edited by sanjay yermalkar (edited October 22, 2001).]
 
Kavita Bopardikar
Greenhorn
Posts: 13
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Sanjay
Its working now. I had another option of doing it using jpub - Jpublisher an Oracle utility which will create a java source & class file of the Oracle object.
But, this thing you mentioned about getting an object array & its attributes is just right.
Thanks a lot.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic