Help coderanch get a
new server
by contributing to the fundraiser
  • 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 Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Help....Pass parameter to PL

 
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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;
}
 
Ranch Hand
Posts: 218
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Author & Gold Digger
Posts: 7617
6
IntelliJ IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
Ranch Hand
Posts: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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: 8945
Firefox Browser Spring Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
When it is used for evil, then watch out! When it is used for good, then things are much nicer. Like this tiny ad:
We need your help - Coderanch server fundraiser
https://coderanch.com/t/782867/Coderanch-server-fundraiser
reply
    Bookmark Topic Watch Topic
  • New Topic