aspose file tools*
The moose likes JDBC and the fly likes Help....Pass parameter to PL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Help....Pass parameter to PL" Watch "Help....Pass parameter to PL" New topic
Author

Help....Pass parameter to PL

Mary Gomez
Greenhorn

Joined: Oct 08, 2003
Posts: 9
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

Joined: Aug 18, 2003
Posts: 218
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

Joined: Aug 26, 2001
Posts: 7610
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.


SCJP 5, SCJD, SCBCD, SCWCD, SCDJWS, IBM XML
[Blog] [Blogroll] [My Reviews] My Linked In
Pradeep bhatt
Ranch Hand

Joined: Feb 27, 2002
Posts: 8919

You will have to use Table to Varray data type.


Groovy
Mary Gomez
Greenhorn

Joined: Oct 08, 2003
Posts: 9
Originally posted by Pradeep Bhat:
You will have to use Table to Varray data type.

Is for the driver ?
Mary Gomez
Greenhorn

Joined: Oct 08, 2003
Posts: 9
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

Joined: Feb 27, 2002
Posts: 8919

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

Joined: Feb 27, 2002
Posts: 8919

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
Ranch Hand

Joined: Aug 18, 2003
Posts: 218
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

Joined: Feb 27, 2002
Posts: 8919

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

Joined: Aug 18, 2003
Posts: 218
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

Joined: Oct 08, 2003
Posts: 9
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
 
jQuery in Action, 2nd edition
 
subject: Help....Pass parameter to PL