Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Help....Pass parameter to PL

 
Mary Gomez
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I need to pass parameter to a Stored Procedure PL. that is a Vector of
a Class created by my. Using SQLData.
Plataform Oracle 8i , weblogic 5.1.
Code:
OracleCallableStatement callstm = null;
try
{
callstm = (weblogic.jdbc.common.OracleCallableStatement)conexion.prepareCall("{call ECU.PROC_ALTA_PERSONAL_RED(?,?,?,?)}");
callstm.setString(1,personalRed.getEmpleado_id());
callstm.setString(2,personalRed.getName);
//is a Vector of Address Class
callstm.setObject(3,personalRed.getListAddress());
callstm.registerOutParameter(4,java.sql.Types.INTEGER);
callstm.execute();
error = callstm.getInt(4);
}
catch(SQLException ex)
{
ex.printStackTrace();
error = 1;
}
 
Sainudheen Mydeen
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Originally posted by Mary Gomez:
//is a Vector of Address Class
callstm.setObject(3,personalRed.getListAddress());

You mean to say getListAddress() returns a Vector? What is the datatype of third 'IN' parameter in your Procedure?
-Sainudheen
 
Valentin Crettaz
Gold Digger
Sheriff
Posts: 7610
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm moving this to the JDBC forum. Please continue this discussion there. Thank you
Note: Mary, please do not crosspost the same question in multiple forums. Thank you.
 
Pradeep bhatt
Ranch Hand
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You will have to use Table to Varray data type.
 
Mary Gomez
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Pradeep Bhat:
You will have to use Table to Varray data type.

Is for the driver ?
 
Mary Gomez
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Sainudheen Mydeen:
Hi

You mean to say getListAddress() returns a Vector? What is the datatype of third 'IN' parameter in your Procedure?
-Sainudheen

Yes, getListAddress() return a Vector and the third parameter is IN
 
Pradeep bhatt
Ranch Hand
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
To which datatype does the personalRed.getListAddress() parameter in the store procedure map to.
Can you paste the stored procedure signature
 
Sainudheen Mydeen
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi
Since PL/SQL cannot work with Vector stuff, you need to pass the third IN parameter as different type. One possible way is pass a as String (street, city, country - all together as a single String separated by comma) and accept it as VARCHAR2 in your procedure. Your procedure can iterate through this and separate them. In this case you have to modify your java program as well as procedure. But we may find a better solution if you post at least a portion of your procedure which deal with the third IN parameter.
-Sainudheen
 
Pradeep bhatt
Ranch Hand
Posts: 8927
Firefox Browser Java Spring
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
One possible way is pass a as String (street, city, country - all together as a single String separated by comma) and accept it as VARCHAR2 in your procedure.

Better use nestedtables or varray datatype.
 
Sainudheen Mydeen
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Pradeep Bhat:

Better use nestedtables or varray datatype.

I agree with you. I said it as a possible way for the person to understand the approach.
Thanks
-Sainudheen
 
Mary Gomez
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Originally posted by Sainudheen Mydeen:

I agree with you. I said it as a possible way for the person to understand the approach.
Thanks
-Sainudheen

The problem is my driver because is weblogic.jdbc.common.OracleCallableStatement, but with other driver , it is posible, for example jdbc2 for oracle
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic