Home » SQL & PL/SQL » SQL & PL/SQL » Reading the ref cursor value returned by a function
Reading the ref cursor value returned by a function [message #197825] Thu, 12 October 2006 18:08 Go to next message
sumitj
Messages: 6
Registered: October 2006
Location: California
Junior Member

Hi,

I have a function who's return type is a ref cursor. this function is in another schema inside some package.

I want to call this function in my procedure and store it's value into a variable, so that I can use this variable to check some condition.

the problem is I'm not able to find what kind of variable I need to define, so that I can use that values inside that variable inside my select statement.

also how to call this function from my procedure to store its value inside that variable.

Thanks,
Sumit
Re: Reading the ref cursor value returned by a function [message #197836 is a reply to message #197825] Thu, 12 October 2006 20:01 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Read through the doco on REF CURSORS, it should help.

Ross Leishman
Re: Reading the ref cursor value returned by a function [message #198023 is a reply to message #197836] Fri, 13 October 2006 19:41 Go to previous messageGo to next message
sumitj
Messages: 6
Registered: October 2006
Location: California
Junior Member

Hi Ross,

Thanks for the link. I have gone through the document. I still haven't found out, what I was looking for. If you could help me out with it.

Basically, I have a package in another schema which contains the function that I want to use in my procedure. This function's return type is a ref cursor, which has two types of values.

In my procedure what kind of variable or object I have to declare to store the values of ref cursor returned by that function.

Also, how should I call that function. I have a synonym created for that package.

please do advise me as to how to proceed.

Thanks once again for your response.

Sumit
Re: Reading the ref cursor value returned by a function [message #198027 is a reply to message #198023] Fri, 13 October 2006 21:22 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Really? I'm having trouble believing that the section entitled "Declaring REF CURSOR Types and Cursor Variables" was no help to you at all in working out how to declare a variable to capture a REF CURSOR.

And how do you call the function? How do you call any function (in PL/SQL). var := pack.func(args);

Have a go. If you get a syntax error then post it here. At least it will prove that you've tried something.

Ross Leishman
Re: Reading the ref cursor value returned by a function [message #198361 is a reply to message #197825] Mon, 16 October 2006 16:02 Go to previous messageGo to next message
sumitj
Messages: 6
Registered: October 2006
Location: California
Junior Member

Hi Ross,
This is what I am doing. please have a look n let me know,If I am going wrong somewhere.

Schema B:
package spec:
TYPE UserType IS RECORD (principle_id principles.principle_id%TYPE,
user_name user_profile.user_name%TYPE);
--
TYPE rc_User IS REF CURSOR RETURN UserType;
Package body:
FUNCTION get_all_users (p_session_id IN VARCHAR2,
p_sqlcode OUT NUMBER,
p_sqlerrm OUT VARCHAR2)
RETURN rc_User;


Schema A (where I am calling this function)
package spec: I have delared the same record type n ref cursor type
TYPE usertype IS RECORD (
principle_id NUMBER,
user_name VARCHAR2 (20)
);

TYPE rc_user IS REF CURSOR
RETURN usertype;

package body:
My_procedure()
v_all_user_cur rc_user; --declared the cursor variable to retrieve the function result.

BEGIN
v_all_user_cur := pack_synonym.get_all_users(p_session_id,p_sqlcode,p_sqlerrm); -- called that function

END;
getting this error: PLS-00382: expression is of wrong type

let me know..if I am doing something wrong.
Re: Reading the ref cursor value returned by a function [message #198376 is a reply to message #198361] Mon, 16 October 2006 21:39 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
v_all_user_cur is a strongly typed REF CURSOR that references a schema_b_pack.usertype, and pack_synonym.get_all_users(...) returns a striongly typed REF CURSOR that references a schema_b_pack.usertype. ie. they are of different types.

Try declaring v_all_user_cur as:
v_all_user_cur schema_a_pack.UserType;


They must be of the same type to perform the assignment. Thats the reason we have strongly typed REF CURSORS (to stop us from mixing up the types).

Ross Leishman
Re: Reading the ref cursor value returned by a function [message #199046 is a reply to message #198376] Thu, 19 October 2006 20:23 Go to previous messageGo to next message
sumitj
Messages: 6
Registered: October 2006
Location: California
Junior Member

Ross,
Thanks a lot for all ur help...Smile

I have a new doubt...if you can help me with that...Smile

I am opening a ref cursor inside the if condition like this:

IF v_local_user_cur%FOUND THEN
OPEN p_rc_alerts FOR
SELECT a.alert_key, a.title, a.alert_date, c.user_name
FROM alert a, alert_distribution b, user_session c
WHERE b.to_principle_id = v_local_user_rec.principle_id
AND a.alert_key = b.alert_key
AND b.status = 'NEW'
AND c.session_identifier = p_session_id
AND NVL (alert_date, SYSDATE - 1) <= SYSDATE
AND NVL (expiration_date, SYSDATE + 1) > SYSDATE
ORDER BY alert_date ASC;

I return this ref cursor....but the problem is when there is no rows to return I get this error:
ORA-24338: statement handle not executed
I tried to put this in the ELSE part:

ELSE
OPEN p_rc_alerts FOR SELECT NULL FROM DUAL;
END IF;

NOW I am getting this error:
PLS-00382:'expression is of wrong type'
how to handle the null condition for the ref cursor. it is called from the front end. they are getting this error, when the ref cursor is empty.

Sumit
Re: Reading the ref cursor value returned by a function [message #199050 is a reply to message #199046] Thu, 19 October 2006 20:29 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Open the cursor no matter what, but put a bypass WHERE clause in there if you don't want it to return rows.
do_it := 0;
IF v_local_user_cur%FOUND THEN
  do_it := 1;
END IF;

OPEN p_rc_alerts FOR
SELECT a.alert_key, a.title, a.alert_date, c.user_name
FROM alert a, alert_distribution b, user_session c
WHERE b.to_principle_id = v_local_user_rec.principle_id
AND a.alert_key = b.alert_key
AND b.status = 'NEW'
AND c.session_identifier = p_session_id
AND NVL (alert_date, SYSDATE - 1) <= SYSDATE
AND NVL (expiration_date, SYSDATE + 1) > SYSDATE
AND do_it = 1
ORDER BY alert_date ASC;


Ross Leishman
Re: Reading the ref cursor value returned by a function [message #199055 is a reply to message #199050] Thu, 19 October 2006 20:52 Go to previous messageGo to next message
sumitj
Messages: 6
Registered: October 2006
Location: California
Junior Member

Thanks Ross,

it worked...Smile

but the first cursor always have values i guess...its the second ref cursor i.e. p_rc_alerts that won't have any rows...because all my tables are new and don't have any rows in that...Smile
That's why i asked u..if we can come around something for this cursor....to test it from the front end...if its working or not...?

it might work...wt' u have suggested...?

Sumit
Re: Reading the ref cursor value returned by a function [message #199157 is a reply to message #197825] Fri, 20 October 2006 12:01 Go to previous messageGo to next message
sumitj
Messages: 6
Registered: October 2006
Location: California
Junior Member

Ross,

As I told u...its the second cursor 'p_rc_alerts'for which I am getting the same error, when it is called from the front end, because if there are no rows selected than the cursor will not open right?

so can we have some other way to handle the null case i.e. evenif there are no rows selected the cursor still returns null and there won't be this type of error. I will have this kind of scenario.

please do let me know... as I am stucked testing this thing...Sad

Thanks,
Sumit
Re: Reading the ref cursor value returned by a function [message #199190 is a reply to message #199157] Fri, 20 October 2006 21:02 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
I don't understand your problem. Post ALL of the relevant code AND annotate it with comments where there is a problem.

Ross Leishman
Previous Topic: Help with Stored Procedure
Next Topic: VARIABLE IN PLSQL IN WHERE CLAUSE
Goto Forum:
  


Current Time: Sat Dec 10 03:15:23 CST 2016

Total time taken to generate the page: 0.23205 seconds