Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to check if a cursor has rows or not?

636630Nov 5 2008 — edited Nov 6 2008
I have a stored procedure, as follows.. If the query is returning any rows then i jst want to return the results of the query else i want to call another stored procedure whose values should be used.. How do i check if the query has any values or not?? I tried using cursor%FOUND, but still it goes to else part even if there is data in the table from where i am querying..
create or replace
PROCEDURE              "PROC_1" 
(
  v_AccountId IN VARCHAR2 DEFAULT NULL ,
  v_endDate IN Date DEFAULT NULL,
  cv_1 IN OUT SYS_REFCURSOR,
  cv_2 IN OUT SYS_REFCURSOR
)
AS

return_Flag number default 0;
BEGIN

OPEN cv_1 FOR
   Select DEPOSIT_CNT,DEPOSIT_AMT,WITHDRAWAL_CNT,WITHDRAWAL_AMT,ATM_CREDIT_CNT,ATM_CREDIT_AMT,ATM_DEBIT_CNT,ATM_DEBIT_AMT,CASH_CREDIT_CNT,CASH_CREDIT_AMT,CASH_DEBIT_CNT,CASH_DEBIT_AMT,CLRNG_CREDIT_CNT,CLRNG_CREDIT_AMT,CLRNG_DEBIT_CNT,CLRNG_DEBIT_AMT,TRANSFER_CREDIT_CNT,TRANSFER_CREDIT_AMT,TRANSFER_DEBIT_CNT,TRANSFER_DEBIT_AMT,CUMMULATIVE_CNT,CUMMULATIVE_AMT,CASH_COUNT,CASH_AMOUNT,DAY1,DAY2,DAY3,DAY4,DAY5,DAY6,CDAY_DEPOSIT_COUNT,CDAY_DEPOSIT_AMOUNT,CDAY_WITHDRAWAL_COUNT,CDAY_WITHDRAWAL_AMOUNT,CDAY_ATM_CREDITTXN_COUNT,CDAY_ATM_CREDITTXN_AMOUNT,CDAY_ATM_DEBITTXN_COUNT,CDAY_ATM_DEBITTXN_AMOUNT,CDAY_CASH_CREDITTXN_COUNT,CDAY_CASH_CREDITTXN_AMOUNT,CDAY_CASH_DEBITTXN_COUNT,CDAY_CASH_DEBITTXN_AMOUNT,CDAY_CLEARING_CREDITTXN_COUNT,CDAY_CLEARING_CREDITTXN_AMOUNT,CDAY_CLEARING_DEBITTXN_COUNT,CDAY_CLEARING_DEBITTXN_AMOUNT,CDAY_TRANSFER_CREDITTXN_COUNT,CDAY_TRANSFER_CREDITTXN_AMOUNT,CDAY_TRANSFER_DEBITTXN_COUNT,CDAY_TRANSFER_DEBITTXN_AMOUNT,CDAY_CUMMULATIVETXN_COUNT,CDAY_CUMMULATIVETXN_AMOUNT,CDAY_CASH_TXN_COUNT,CDAY_CASH_TXN_AMOUNT from Account_Stats_Table where Account_id = v_AccountId;
  
   if cv_1%Found then
   dbms_output.put_line('Account_id in Stats Table');
   return_flag:=1;
   else
   dbms_output.put_line('Account_id not in Stats Table');
   AH_TEST_PROC(v_AccountId,v_endDate,cv_2);
   end if;
  
END PROC_1;
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 4 2008
Added on Nov 5 2008
8 comments
33,531 views