aspose file tools*
The moose likes JDBC and the fly likes Closing cursor in a stored procedure when invoked from a callable statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Closing cursor in a stored procedure when invoked from a callable statement" Watch "Closing cursor in a stored procedure when invoked from a callable statement" New topic
Author

Closing cursor in a stored procedure when invoked from a callable statement

A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Hi,

I have a java program that calls a procedure ..

part of the operation is such it returns a resultset...for which i am using...cursor..

but my doubt is where am i closing the cursor..or where shopuld i close the cursor..??

Java program snippet:

*********************************************************************************************************
CallableStatement cst=conn.prepareCall("{ call MY_TEST_PROC(?,?,?) }");
cst.registerOutParameter(1,OracleTypes.CURSOR);
cst.setString(2,"XX");
cst.setString(3,"YY");

cst.execute();
ResultSet rs=(ResultSet) cst.getObject(1);


if(rs!=null){
while(rs.next()){
String rowdata=rs.getString(1);
//operations
}
}
******************************************************************************************************


Stored procedure snippet:

PROCEDURE MY_TEST_PROC (
dataCursor OUT myTypes.RefCursor,
operationType IN varchar,
typeData IN varchar
)
AS
BEGIN

OPEN dataCursor FOR
select * from pzn_mkt;



END MY_TEST_PROC ;

*****************************************************************************************************
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by A Kumar:

Stored procedure snippet:

PROCEDURE MY_TEST_PROC (
dataCursor OUT myTypes.RefCursor,
operationType IN varchar,
typeData IN varchar
)
AS
BEGIN

OPEN dataCursor FOR
select * from pzn_mkt;



END MY_TEST_PROC ;

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

CURSOR c1
IS
Select * from pzn_mkt; -- you shouldn't do select splat, be specific

BEGIN

open c1;
fetch c1 into dataCursor ;
close c1;

RETURN dataCursor ;

END;
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
when i return datacursor is it not a opened ???

And is it fine if i close the resultset in java code...for the cursor to be closed..since it is object that i get from the result of the query...
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by A Kumar:
when i return datacursor is it not a opened ???

And is it fine if i close the resultset in java code...for the cursor to be closed..since it is object that i get from the result of the query...


It isn't opened until you fetch your cursor... which occurs in your program (the part after the Begin)... maybe a more familiar way to put this is that the cursor you have described in your cursor declaration is not instantiated until you open the cursor.

I'm not an expert on JDBC... but there is not data base garbage collector... it is up to you to close your cursor in the data base... if you don't you will begin to see performance impacts.
[ December 10, 2007: Message edited by: Paul Campbell ]
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Thanks Paul..but what i found is ....

When I close the cursor..

and execute the Java program it says cursor closed..and exception is thrown..

Also...


It isn't opened until you fetch your cursor... which occurs in your program (the part after the Begin)... maybe a more familiar way to put this is that the cursor you have described in your cursor declaration is not instantiated until you open the cursor.


If the
return dataCursor

returns a cursor to the java program...and i use it..then at what point shall i close it...

Regards
[ December 11, 2007: Message edited by: A Kumar ]
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Using the code..



I get the below exception...

Oracle Error :: ORA-06504

PL/SQL: Return types of Result Set variables or query do not match
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Two things jump out... the select is part of your cursor declaration (and you are returning all rows... cursors should just name the columns you want... and if you declare your types must match what you say your types are (I don't know what your table contains).

I have to run to work... I'll check back here in an hour.
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
This is my modified code...for which i get the above post's exception..



PROCEDURE MY_PROC_GET(
dataCursor OUT RpTypes.RefCursor,
dataType IN varchar,
operationType IN varchar
)
AS

get_cursor RpTypes.RefCursor;

BEGIN

if(operationType = 'COUNT') THEN

open dataCursor FOR
select count(*) from table1 where field1 =dataType and
status = '1';
fetch get_cursor into dataCursor ;
close get_cursor;


elsif (operationType = 'GET') THEN

open dataCursor FOR
select field1 from table1 where
field2 = dataType order by field2;

fetch get_cursor into dataCursor ;
close get_cursor;


end if;

END MY_PROC_GET;


And calling code is...


cst=conn.prepareCall("{ call MY_PROC_GET(?,?,?) }");
cst.registerOutParameter(1,OracleTypes.CURSOR);
cst.setString(2,strDataType);
cst.setString(3,"GET");
cst.execute();

ResultSet rs=(ResultSet) cst.getObject(1);

Regards
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Hey Kumar,

A few questions... is this pl/sql?

What are you expecting to return in your cursor?

A number, a row, a column?

In a procedure cursors are declared in your spec (the select statement cursor a as select col1 from table1 where...)... the spec is the part before the BEGIN.

-- explicit cursor example
DECLARE
my_sal employees.salary%TYPE;
my_job employees.job_id%TYPE;
factor INTEGER := 2;
CURSOR c1 IS
SELECT factor*salary FROM employees WHERE job_id = my_job;
BEGIN
OPEN c1; -- factor initially equals 2
LOOP
FETCH c1 INTO my_sal;
EXIT WHEN c1%NOTFOUND;
factor := factor + 1; -- does not affect FETCH
END LOOP;
CLOSe c1;
END;
/

Functions return values (not procedures) and the return is also declared in your spec.

PROCEDURE raise_salary (emp_id INTEGER, amount REAL) IS
current_salary REAL;
salary_missing EXCEPTION;
BEGIN
SELECT sal INTO current_salary FROM emp
WHERE empno = emp_id;
IF current_salary IS NULL THEN
RAISE salary_missing;
ELSE
UPDATE emp SET sal = sal + amount
WHERE empno = emp_id;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
INSERT INTO emp_audit VALUES (emp_id, 'No such number');
WHEN salary_missing THEN
INSERT INTO emp_audit VALUES (emp_id, 'Salary is null');
END raise_salary;


FUNCTION sal_ok (salary REAL, title VARCHAR2) RETURN BOOLEAN IS
min_sal REAL;
max_sal REAL;
BEGIN
SELECT losal, hisal INTO min_sal, max_sal FROM sals
WHERE job = title;
RETURN (salary >= min_sal) AND (salary <= max_sal);
END sal_ok;


Don't get discouraged... you're really not that far off... just some of the programming structures are mislocated.
[ December 11, 2007: Message edited by: Paul Campbell ]
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
But how can i have ...the cursor be created with different queries in ...spec depending on the value of operationType...???
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
as long as they are the same type... i.e, number... you can have two cursors populate a return variable... but in your case... I don't even see where you need a cursor

FOR example (this is following the explicit cursor example you started with, personally I prefer implicit because I don't like keeping up with cursors).

FUNCTION GetInfo( p_action IN VARCHAR2
, p_something IN VARCHAR2)
RETURN NUMBER IS

CURSOR c_count IS
SELECT count(*)
FROM kumars_table;

CURSOR c_info IS
Select emplID
FROM kumars_table
WHERE column = p_something;

v_out NUMBER := 0;

BEGIN

IF p_action = 'GET' THEN
OPEN c_info;
FETCH c_info INTO v_out;
CLOSE c_info;
ELSIF p_action = 'COUNT' THEN
OPEN c_count;
FETCH c_count into v_out;
CLOSE c_count;
ELSE
v_out := -1; -- to identify an error or create user exception
END IF;

RETURN v_out;

END;
[ December 11, 2007: Message edited by: Paul Campbell ]
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Thanks paul..once at work plce...i would try this out and get back to you...
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by A Kumar:
Thanks paul..once at work plce...i would try this out and get back to you...


Hey don't forget... it you are only returning single rows and not needing to loop through rows with your cursor... don't use a cursor... cursors do impact performance and only use them when you need them to fetch multiple rows... in your case (from what I've seen) you only need to use the Select with the Into clause (to place it in your return variable).
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Hi Paul,

I moved the count into a different procedure where in i write the count into a number variable.

The other procedure for getting records return a cursor..

And i googled for closing of such cursor..

Closing cursor when invoked from java program
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by A Kumar:
Hi Paul,

I moved the count into a different procedure where in i write the count into a number variable.

The other procedure for getting records return a cursor..

And i googled for closing of such cursor..

Closing cursor when invoked from java program


Thank you... that is good information to know.
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
But i also got contradicting info ..also...that even if yoou close the connection from java program...at the backend...DB,it would be still there...

So its like a 50-50 situation...

Need to google a bit more tomm in office...
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Originally posted by A Kumar:
But i also got contradicting info ..also...that even if yoou close the connection from java program...at the backend...DB,it would be still there...

So its like a 50-50 situation...

Need to google a bit more tomm in office...


Ahh... I think that last bit of info is what I needed... (remember I've been in data warehousing 15 years... learning Java now).

This is what you likely needed to know:

http://download-uk.oracle.com/docs/cd/B19306_01/win.102/b14307/featRefCursor.htm
Obtaining an OracleRefCursor Object

There are no constructors for OracleRefCursor objects. They can be acquired only as parameter values from PL/SQL stored procedures, stored functions, or anonymous blocks.

An OracleRefCursor object is a connected object. The connection used to execute the command returning an OracleRefCursor object is required for its lifetime. Once the connection associated with an OracleRefCursor object is closed, the OracleRefCursor object cannot be used.
Obtaining a REF CURSOR Datatype

A REF CURSOR datatype can be obtained as an OracleDataReader, DataSet, or OracleRefCursor object. If the REF CURSOR datatype is obtained as an OracleRefCursor object, it can be used to create an OracleDataReader object or populate a DataSet from it. When accessing a REF CURSOR datatype, always bind it as an OracleDbType.RefCursor parameter.
Populating an OracleDataReader from a REF CURSOR

A REF CURSOR datatype can be obtained as an OracleDataReader object by calling the ExecuteReader method of the OracleCommand object. The output parameter with the OracleDbType property set is bound to OracleDbType.RefCursor. None of the output parameters of type OracleDbType.RefCursor is populated after the ExecuteReader method is invoked.

If there are multiple output REF CURSOR parameters, use the NextResult method of the OracleDataReader object to access the next REF CURSOR datatype. The OracleDataReader NextResult method provides sequential access to the REF CURSOR datatypes; only one REF CURSOR datatype can be accessed at a given time.

The order in which OracleDataReader objects are created for the corresponding REF CURSOR datatypes depends on the order in which the parameters are bound. If a PL/SQL stored function returns a REF CURSOR datatype, then it becomes the first OracleDataReader object and all the output REF CURSOR datatypes follow the order in which the parameters are bound.
Populating the DataSet from a REF CURSOR

For the Fill method to populate the DataSet properly, the SelectCommand property of the OracleDataAdapter class must be bound with an output parameter of type OracleDbType.RefCursor. If the Fill method is successful, the DataSet is populated with a DataTable that represents a REF CURSOR datatype.

If the command execution returns multiple REF CURSOR datatypes, the DataSet is populated with multiple DataTable objects.
Populating an OracleRefCursor from a REF CURSOR

When the ExecuteNonQuery method is invoked on a command that returns one or more REF CURSOR datatypes, each of the OracleCommand parameters that are bound as an OracleDbType.RefCursor gets a reference to an OracleRefCursor object.

To create an OracleDataReader object from an OracleRefCursor object, invoke the GetDataReader method from the OracleRefCursor object. Subsequent calls to the GetDataReader method return a reference to the same OracleDataReader object.

To populate a DataSet with an OracleRefCursor object, the application can invoke a Fill method of the OracleDataAdapter class that takes an OracleRefCursor object. Similar to the OracleDataReader object, an OracleRefCursor object is forward-only. Therefore, once a row is read from an OracleRefCursor object, that same row cannot be obtained again from it unless it is populated again from a query.

When multiple REF CURSOR datatypes are returned from a command execution as OracleRefCursor objects, the application can choose to create an OracleDataReader object or populate a DataSet with a particular OracleRefCursor object. All the OracleDataReader objects or DataSet objects created from the OracleRefCursor objects are active at the same time, and can be accessed in any order.
Updating a DataSet Obtained from a REF CURSOR

REF CURSOR types cannot be updated. However, data that is retrieved into a DataSet can be updated. Therefore, the OracleDataAdapter class requires a custom SQL statement to flush any REF CURSOR data updates to the database.

The OracleCommandBuilder object cannot be used to generate SQL statements for REF CURSOR updates.
Behavior of ExecuteScalar Method for REF CURSOR

The ExecuteScalar method returns the value of the first column of the first row of the REF CURSOR if it is one of the following:

*

A return value of a stored function execution
*

The first bind parameter of a stored procedure execution

See Also:
Oracle Database Application Developer's Guide - Large Objects for more information
Passing a REF CURSOR to a Stored Procedure

An application can retrieve a REF CURSOR type from a PL/SQL stored procedure or function and pass it to another stored procedure or function. This feature is useful in scenarios where a stored procedure or a function returns a REF CURSOR type to the .NET application, and based on the application logic, the application passes this REF CURSOR to another stored procedure for processing. Note that if you retrieve the data from a REF CURSOR type in the .NET application, you cannot pass it back to another stored procedure.

The following example demonstrate passing a REF CURSOR:

/*
connect scott/tiger@oracle
create table test (col1 number);
insert into test(col1) values (1);
commit;

create or replace package testPkg as type empCur is REF Cursor;
end testPkg;
/

create or replace procedure testSP(param1 IN testPkg.empCur, param2 OUT NUMBER)
as
begin
FETCH param1 into param2;
end;
/
*/

// C#


using System;
using Oracle.DataAccess.Client;
using System.Data;

class InRefCursorParameterSample
{
static void Main()
{
OracleConnection conn = new OracleConnection
("User Id=scott; Password=tiger; Data Source=oracle");

conn.Open(); // Open the connection to the database

// Command text for getting the REF Cursor as OUT parameter
String cmdTxt1 = "begin open :1 for select col1 from test; end;";

// Command text to pass the REF Cursor as IN parameter
String cmdTxt2 = "begin testSP (:1, :2); end;";

// Create the command object for executing cmdTxt1 and cmdTxt2
OracleCommand cmd = new OracleCommand(cmdTxt1, conn);

// Bind the Ref cursor to the PL/SQL stored procedure
OracleParameter outRefPrm = cmd.Parameters.Add("outRefPrm",
OracleDbType.RefCursor, DBNull.Value, ParameterDirection.Output);

cmd.ExecuteNonQuery(); // Execute the anonymous PL/SQL block

// Reset the command object to execute another anonymous PL/SQL block
cmd.Parameters.Clear();
cmd.CommandText = cmdTxt2;

// REF Cursor obtained from previous execution is passed to this
// procedure as IN parameter
OracleParameter inRefPrm = cmd.Parameters.Add("inRefPrm",
OracleDbType.RefCursor, outRefPrm.Value, ParameterDirection.Input);

// Bind another Number parameter to get the REF Cursor column value
OracleParameter outNumPrm = cmd.Parameters.Add("outNumPrm",
OracleDbType.Int32, DBNull.Value, ParameterDirection.Output);

cmd.ExecuteNonQuery(); //Execute the stored procedure

// Display the out parameter value
Console.WriteLine("out parameter is: " + outNumPrm.Value.ToString());
}
}

Go to previous page
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Frankly coudn't decide...Maybe i missed something..

I am back to the million dollar question

how shall i close the cursor..programmatically...from java...


I am still in the maze trying to figure out a way..

Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
if it is of type ref Cursor... which is different than what I saw in your code you simply close the connection.

There are no constructors for OracleRefCursor objects. They can be acquired only as parameter values from PL/SQL stored procedures, stored functions, or anonymous blocks.

An OracleRefCursor object is a connected object. The connection used to execute the command returning an OracleRefCursor object is required for its lifetime. Once the connection associated with an OracleRefCursor object is closed, the OracleRefCursor object cannot be used.
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Yes it is of type ref cursor.

I am closing the connection...and hence the cursor should get closed automatically...

Thanks Paul for all your effort....

Sorry been after you for the last 3 days....




[ December 13, 2007: Message edited by: A Kumar ]
Paul Campbell
Ranch Hand

Joined: Oct 06, 2007
Posts: 338
Awesome! No worries... It will be my turn soon enough in some of the other forums. ;)
A Kumar
Ranch Hand

Joined: Jul 04, 2004
Posts: 979
Hoping to help you out...
 
 
subject: Closing cursor in a stored procedure when invoked from a callable statement