| PL/SQL Stored Procedures returning ResultSets [message #2613] |
Wed, 31 July 2002 04:31  |
maleev
Messages: 7
Registered: July 2002
|
Junior Member |
|
|
I'm a java programmer, not completely a newbie in the PL/SQL world. My question is how do you define a PL/SQL stored procedure which returns ResultSets. The JDBC call should look like:
CallableStatement cs = conn.prepareCall("{? = call proc(?, ?)}");
cs.registerOutParameter(1, java.sql.Types.CHAR);
cs.registerOutParameter(2, java.sql.Types.CHAR);
cs.setString(2, "A");
cs.setString(3, "B");
cs.execute();
String param1 = cs.getString(2);
ResultSet rs = null;
do {
/* this is what I'm interested in */
rs = cs.getResultSet();
} while (cs.hasMoreResults())
Can anyone post an example?
|
|
|
|
|
|
| Re: PL/SQL Stored Procedures returning ResultSets [message #2618 is a reply to message #2613] |
Wed, 31 July 2002 05:54  |
maleev
Messages: 7
Registered: July 2002
|
Junior Member |
|
|
What does your example have to do with ResultSets?
Anyway thanks for the answer, I got my question answered - Oracle has a different approach to stored procedures and does not provide this functionality defined in JDBC (If I have to be precise it does not provide the functionality in the way the java.sql API requires it, which does not necessarily mean that it does not provide the functionality at all). I consulted the OracleDriver javadoc for the implementor of java.sql.Statement (if you're interested look at the comment for the getMoreResults() method) and it states that there could be only one result set and there is no way to determine if such exists.
Regards, Anton
|
|
|
|
|
|
| Re: PL/SQL Stored Procedures returning ResultSets [message #2627 is a reply to message #2621] |
Wed, 31 July 2002 22:34 |
maleev
Messages: 7
Registered: July 2002
|
Junior Member |
|
|
Hi, thanks for the URL. Anyway it does not address neither the issue I originally posted nor my second post. The example at this URL shows a PL/SQL function which has a ResultSet as a RETURN PARAMETER, which I knew would work. Anyway with this strategy you cannot return multiple ResultSets unless you invent some weird data structure holding references to the cursors and you have to "unmarshal" this structure in the client code. This would not be a portable solution, since it relies on Oracle specific data types and call interfaces and not on these defined in JDBC. If you take a look at the code in my first post on the topic, you'll see that I want to get the resultSet with:
not with cs.getObject(int parameterIndex) boolean cs.getMoreResults()
What I tried to explain in my second post was that Oracle's approach to stored procedures is different from that considered by JDBC. PL/SQL procedures are extremely flexible in the syntax and PL/SQL allows you to use a rich set of datatypes. Thus they don't need the paradigm of a procedure returning a result set (like a normal SELECT). What bothers me is that Oracle implemented in their JDBC driver only the methods their product easily can and didn't at all take care of the ones which differ from their approach to the problem. This means that I have to write one client code to call an oracle stored procedure and another for a DB2 or Sybase one, even when I don't use non-standard functionality and they do the same. And this really compromises the idea behind JDBC.
P.S. Other JDBC Drivers also have their problems, this is not a complaint against Oracle!
|
|
|
|
| Re: PL/SQL Stored Procedures returning ResultSets [message #2632 is a reply to message #2621] |
Thu, 01 August 2002 08:44 |
Todd Barry
Messages: 4819
Registered: August 2001
|
Senior Member |
|
|
|
Look, your original question was "how do you define a PL/SQL stored procedure which returns ResultSets." The URL I gave you is the definitive answer to your question and even includes the sample JDBC calls. If you don't like the JDBC syntax/usage, you'll have to discuss that in a JDBC-specific forum.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
