Hi,
Below is the complete code and description about my problem:
CREATE OR REPLACE PACKAGE package_ex IS
TYPE ref_cursor IS REF CURSOR;
PROCEDURE validate(i_name IN customer.Name%TYPE,
i_dealer_number IN customer.dealer%TYPE,
o_info_cur OUT ref_cursor) IS
v_info_cur ref_cursor;
v_cur ref_cursor;
BEGIN
OPEN v_info_cur FOR
SELECT Name,
sp_customer,
dealer_name,
po,
shipto,
shiptoname,
shiptoaddr,
channel,
site
FROM parent_view_new
WHERE Name= i_name AND dealer_name=i_dealer_number
AND rownum = 1;

fetch v_info_cur INTO v_cur; => after this line, it catch exception

IF(v_info_cur%ROWCOUNT<=0) THEN
OPEN v_info_cur FOR
SELECT Name,
sp_customer,
dealer_name,
po,
shipto,
shiptoname,
shiptoaddr,
channel,
site
FROM parent_view
WHERE Name= i_name AND dealer_name=i_dealer_number
AND rownum = 1;
END IF;
o_info_cur := v_info_cur;

EXCEPTION
WHEN OTHERS THEN
---some text for exception----
END validate;
END;
I want to execute the first select query from the "parent_view_new" view if it does not have data for the given input, second select query should execute.
The problem is even there is data in the first select query, it always shows the rowcount as "0".And also after the "fetch" statement, the next line of execution passes to the "Exception" statement.
Whats wrong in the above procedure? Please help..

Thanks.

So, for the start, do not try to "handle" exceptions in EXCEPTION WHEN OTHER clause (remove it preferably forever), as it just hides the exception reason - you would post it if you knew it, would not you?

What is the definition of REF_CURSOR data type? It cannot be used for storing cursor and its result set at the same time. So, I doubt that the package body will even compile, so you would be able to "execute" it (whatever it exactly is).

Maybe you may benefit from studying some examples on using cursors. Here is the one I found at first: http://www.oracle-base.com/articles/...Recordsets.php

Or, better, read about it in PL/SQL User's Guide and Reference for your Oracle version. It is available with other Oracle documentation books e.g. online on http://tahiti.oracle.com/

Hi,
The exception I am getting is, ORA-24338:statement handle not executed. When I searched, this exception will occur "when fetching a cursor before executing the query" was the description given for this exception.
before using the above mentioned way of using the cursor's rowcount, I used count(*) followed by the select query from parent_view_new but it took long time to execute this query, since the count will be more than 40thousand records.
Then I used the cursor's fetch and rowcount to know whether the cursor has atleast 1 row but the rowcount is always 0(zero). When I execute the same query separately for the same input i found 2 rows.

Thanks.

Hi,
sorry, from your posts I have no idea what exactly you did and what was the package content when you got that exception.
Because of that ROWNUM=1 condition, each query returns at most one row, so I also do not understand "i found 2 rows" remark.
Additionally, you are aware, that after FETCHing the row inside the procedure, it will not be available for the caller?
Anyway, if it is your real requirement (return row(s) from PARENT_VIEW_NEW; if there are none, return row(s) from PARENT_VIEW) you should stop making those strange PL/SQL attempts and express this condition directly in SQL:

How about this for one cursor?
(I'm not certain about place of "rownum = 1")