Help....Pass parameter to PL

[Mary Gomez]

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]

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]

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]

You will have to use Table to Varray data type.

[Mary Gomez]

Originally posted by Pradeep Bhat:
You will have to use Table to Varray data type.

Is for the driver ?

[Mary Gomez]

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]

To which datatype does the personalRed.getListAddress() parameter in the store procedure map to.
Can you paste the stored procedure signature

[Pradeep bhatt]

http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/jdbc20/STCollectionSample/Readme.html
http://otn.oracle.com/sample_code/tech/java/sqlj_jdbc/files/jdbc20/WTCollectionSample/Readme.html

[Sainudheen Mydeen]

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]

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]

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]

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