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;