Home » Developer & Programmer » JDeveloper, Java & XML » Returning Resultset from a proc - Oracle vs MS SQL
Returning Resultset from a proc - Oracle vs MS SQL [message #91008] Wed, 06 February 2002 08:42 Go to next message
Helmar Martens
Messages: 1
Registered: February 2002
Junior Member
Dear friend,

I would really appreciate if you could help me solve a small problem with resultset being returned from stored procedures.

I am working on a Java project, which is supposed to be able to connect to all three databases: Oracle, DB2, or MS SQL.

In an attempt to keep specific DB features out of the Java code, we have decided to work with Stored Procedures, This way, the only reference that the Java code makes to the Databases is in the Stored Procedures calls (in addition to the connection strings).
My first implementation was with MS SQL. Now I am trying to access the Oracle stored procedures (or functions, it does not matter) in the same way. I have researched some books as well as some sites (including yours) on line. I have found several different ways of returning a resultset from either a stored procedure or a function. The problem is that they are all different from what I have done for the first implementation. The difference resides in the fact that MS SQL does not return a cursor. Once I call a stored procedure, the resultset object is automatically populated with the result generated by the MS SQL stored procedure.

Here is an excerpt of both calls:

Oracle Function:
Function BST_V_SEL_BYPID_PROC2 ( v_p_id NUMBER )
RETURN TYPES.cursortype as

c TYPES.cursorType;

BEGIN
OPEN c
FOR SELECT bst_value.value_id,
bst_value.p_id,
bst_value.entry_id,
bst_entry.name,
bst_value.data
FROM bst_value, bst_entry
WHERE bst_value.entry_id = bst_entry.entry_id
AND bst_value.p_id = v_p_id
AND bst_value.is_deleted = 0;

RETURN c;

END BST_V_SEL_BYPID_PROC2;


**********************************************************

Calling the Oracle function:
String q = "{? = call BST_V_SEL_BYPID_PROC2(?)}";
stpCall = conn.prepareCall( q);
stpCall.registerOutParameter(1, OracleTypes.CURSOR);
stpCall.setLong( 2, p_Id );
stpCall.execute();
rs = (ResultSet)stpCall.getObject(1);
while( rs.next() )
{
long valueId = rs.getLong( 1 );
long p_id = rs.getLong( 2 );
long entryIdFk = rs.getLong( 3 );
String valueName = rs.getString( 4 );
String valueData = rs.getString( 5 );
// work with the variables…
}
***********************************************************
Calling the MS SQL procedure:
String q = "{call BST_V_SEL_BYPID_PROC2(?)}";
stpCall = connection.prepareCall(q);
stpCall.setLong( 1, p_Id );
stpCall.executeQuery();
rs = stpCall.getResultSet(); // The resultset value is assigned automatically without
// the need to access an output Parameter.

while( rs.next() )
{
long valueId = rs.getLong( 1 );
long p_id = rs.getLong( 2 );
long entryIdFk = rs.getLong( 3 );
String valueName = rs.getString( 4 );
String valueData = rs.getString( 5 );
// work with the variables…
}

So, here is my question. Is there any way that I can call either an Oracle procedure or a function in the same way that I did for MS SQL?

A million thanks in advance.

Helmar Martens

P.S. Please understand that I am not making any reference to which one is better or worseJ I just want to find a standard way to do this project.
Re: Returning Resultset from a proc - Oracle vs MS SQL [message #91201 is a reply to message #91008] Sat, 25 May 2002 01:10 Go to previous messageGo to next message
Sonali
Messages: 3
Registered: May 2002
Junior Member
I want to know the diffrence between oracle vs sql
Re: Returning Resultset from a proc - Oracle vs MS SQL [message #91485 is a reply to message #91201] Sun, 06 October 2002 20:56 Go to previous messageGo to next message
Muhammad Muddassar Ali
Messages: 1
Registered: October 2002
Junior Member
Yes Oracle cannot return recordset as MSSQL returns.
To return records from oracle. The only solution is to Create a permanent table(that will be temporary for you).Now write Oracle procedure and then by that procedure insert your result in temporary table. Now from application simply fetch records from this table. To handle multi user problems it is good idea to assign a Transaction number for each transaction. U can use Sequence object for this purpose and then fetching record against that Transaction Number. It will serve your purpose. If U have any other problem U can contact with me.

Ok.

Thanx
Re: Returning Resultset from a proc - Oracle vs MS SQL [message #91642 is a reply to message #91485] Mon, 23 December 2002 08:39 Go to previous message
Bhavesh
Messages: 7
Registered: July 2001
Junior Member
i think we can return resultset through procedure and not only single resultset but also multiple resultset.
just try it out this.
try {
CallableStatement cs = con.prepareCall("{CALL sp_test('123','ABC')}");
cs.execute();
ResultSet rs;
String xxx;
do {
rs = cs.getResultSet();
while(rs.next())
{
// Do what you like with getString()
xxx = rs.getString("RETURN");
...
...
...
}
if(rs!=null)
rs.close();
}while(cs.getMoreResults());
}
catch(SQLException e)
{
// Do your Exception Handling Here
}
Previous Topic: Calling OS command using Java procedure in PL/SQL generates NON-EXISTANT PROCESS on NT
Next Topic: Problem in calling java stored proc from pl/sql
Goto Forum:
  


Current Time: Tue Jul 27 17:00:48 CDT 2021