Usage of SQL%ROWCOUNT

Toad for Oracle
1

Hi Experts,

I have a doubt on usage of SQL%ROWCOUNT. I have a below package created.

Here, when I try to print SQL%ROWCOUNT, it is showing value as 1 and if I print the variable, it is showing as 0 (zero) (Consider the case that I have really one valid record in base table which satisfies the condition). So I am not sure, what to be used :frowning:

Also, can any one throw some light on how SQL%ROWCOUNT works in my case?

Thanks in Advance,

Srivathsava

CREATE OR REPLACE PACKAGE BODY APPS.BC_UPDT_IBAN_DETAILS_PKG

AS

PROCEDURE validate_supplier

IS

CURSOR c_supp_search

IS

SELECT ORG_ID,

BANK_ACCOUNT_NAME,

BANK_ACCOUNT_NUM,

VENDOR_NAME,

COUNTRY,

PARTY_SITE_NAME

FROM BC_UPDT_IBAN_DTLS_STG

WHERE process_status = ‘NEW’;

BEGIN

fnd_file.put_line(fnd_file.LOG,‘Start of VALIDATE_SUPPLIER…’);

FOR c_supp_search_rec IN c_supp_search LOOP

BEGIN

fnd_file.put_line(fnd_file.LOG,'Validating the Account Details: ’

||c_supp_search_rec.BANK_ACCOUNT_NAME

||’ '||c_supp_search_rec.bank_account_num

||’. For Party Site: ’

||c_supp_search_rec.PARTY_SITE_NAME

||’ '||c_supp_search_rec.ORG_ID);

SELECT COUNT(1)

INTO l_cnt_accnt_num

FROM AP_SUPPLIERS APS,

AP_SUPPLIER_SITES_ALL ASS,

IBY_EXTERNAL_PAYEES_ALL IEP,

IBY_PMT_INSTR_USES_ALL IPI,

IBY_EXT_BANK_ACCOUNTS IEB,

HZ_PARTIES HZPBANK,

HZ_PARTIES HZPBRANCH,

HZ_ORGANIZATION_PROFILES HOPBANK,

HR_ORGANIZATION_UNITS HOU

WHERE ASS.VENDOR_ID = APS.VENDOR_ID

AND IEP.PAYEE_PARTY_ID = APS.PARTY_ID

AND IEP.SUPPLIER_SITE_ID =ASS.VENDOR_SITE_ID

AND IEP.EXT_PAYEE_ID =IPI.EXT_PMT_PARTY_ID

AND IPI.INSTRUMENT_ID =IEB.EXT_BANK_ACCOUNT_ID

AND IEB.BANK_ID = HZPBANK.PARTY_ID

AND IEB.BRANCH_ID =HZPBRANCH.PARTY_ID

AND HZPBANK.PARTY_ID =HOPBANK.PARTY_ID

AND IEP.ORG_ID = HOU.ORGANIZATION_ID

AND ipi.instrument_type =‘BANKACCOUNT’

AND ipi.payment_function =‘PAYABLES_DISB’

AND NVL (ass.inactive_date,SYSDATE + 1) > SYSDATE

AND NVL (iep.inactive_date,SYSDATE + 1) > SYSDATE

AND NVL (ipi.end_date, SYSDATE + 1) >SYSDATE

AND iep.org_type IS NOT NULL

AND iep.supplier_site_id IS NOT NULL

AND HOU.organization_id =c_supp_search_rec.ORG_ID

AND APS.VENDOR_NAME =c_supp_search_rec.vendor_name

AND ASS.VENDOR_SITE_CODE =c_supp_search_rec.PARTY_SITE_NAME

AND IEB.BANK_ACCOUNT_NUM =c_supp_search_rec.bank_account_num

AND IEB.BANK_ACCOUNT_NAME =c_supp_search_rec.BANK_ACCOUNT_NAME

AND NVL(IEB.end_date,SYSDATE+1) >= SYSDATE;

fnd_file.put_line(fnd_file.LOG,’ Total Found: '||SQL%ROWCOUNT);

fnd_file.put_line(fnd_file.LOG,’ Total Found using the variable: '||l_cnt_accnt_num);

l_cnt_accnt_num:=SQL%ROWCOUNT;

fnd_file.put_line(fnd_file.LOG,’ After assigning to the variable: '||l_cnt_accnt_num);

fnd_file.put_line(fnd_file.LOG,’ Total Found usign ROWCOUNT: '||SQL%ROWCOUNT);

IF (l_cnt_accnt_num > 0) THEN

fnd_file.put_line(fnd_file.LOG,’ Updating the staging table for valid account’);

UPDATE BC_UPDT_IBAN_DTLS_STG

SET PROCESS_STATUS = ‘VALID’

WHERE vendor_name = c_supp_search_rec.vendor_name

AND PARTY_SITE_NAME = c_supp_search_rec.PARTY_SITE_NAME

AND bank_account_num = c_supp_search_rec.bank_account_num

AND bank_account_name=c_supp_search_rec.bank_account_name

AND ORG_ID=c_supp_search_rec.ORG_ID

AND COUNTRY=c_supp_search_rec.COUNTRY;

COMMIT;

ELSE

/*fnd_file.put_line(fnd_file.LOG,‘Updating the staging table for invalid account’);

fnd_file.put_line(fnd_file.LOG,'Account Name: '||c_supp_search_rec.bank_account_name);

fnd_file.put_line(fnd_file.LOG,'Account Number: '||c_supp_search_rec.bank_account_num);

fnd_file.put_line(fnd_file.LOG,'Vendor Name: '||c_supp_search_rec.vendor_name);

fnd_file.put_line(fnd_file.LOG,'Site: '||c_supp_search_rec.PARTY_SITE_NAME);*/

UPDATE BC_UPDT_IBAN_DTLS_STG

SET PROCESS_STATUS = ‘INVALID’,

ERROR_MESSAGE = ‘Invalid Account Details’

WHERE vendor_name = c_supp_search_rec.vendor_name

AND PARTY_SITE_NAME = c_supp_search_rec.PARTY_SITE_NAME

AND bank_account_num = c_supp_search_rec.bank_account_num

AND bank_account_name=c_supp_search_rec.bank_account_name

AND ORG_ID=c_supp_search_rec.ORG_ID

AND COUNTRY=c_supp_search_rec.COUNTRY;

–fnd_file.put_line(fnd_file.LOG,'Total Updated: '||SQL%ROWCOUNT);

COMMIT;

END IF;

EXCEPTION

WHEN OTHERS THEN

l_message :=SQLERRM;

UPDATE BC_UPDT_IBAN_DTLS_STG

SET PROCESS_STATUS = ‘INVALID’,

ERROR_MESSAGE = l_message

WHERE vendor_name = c_supp_search_rec.vendor_name

AND PARTY_SITE_NAME = c_supp_search_rec.PARTY_SITE_NAME

AND bank_account_num = c_supp_search_rec.bank_account_num

AND bank_account_name=c_supp_search_rec.bank_account_name

AND ORG_ID=c_supp_search_rec.ORG_ID

AND COUNTRY=c_supp_search_rec.COUNTRY;

COMMIT;

–l_validation :=‘Account Number Validation’;

l_message :=’ Undefined exception when Validating the Account Number: ’

||c_supp_search_rec.bank_account_num

||’. Account Name: ’

||c_supp_search_rec.bank_account_name

||’. The Error Message is: ’

|| SQLERRM;

–l_error_code := NULL;

fnd_file.put_line(fnd_file.LOG,l_message);

END;

END LOOP;

fnd_file.put_line(fnd_file.LOG,‘End of VALIDATE_SUPPLIER…’);

EXCEPTION

WHEN OTHERS THEN

–l_validation := NULL;

–l_error_code := NULL;

l_message :=‘Undefined Exception while VALIDATE_SUPPLIER Procedure. The Error Message is: .’

|| SQLERRM;

END validate_supplier;

END BC_UPDT_IBAN_DETAILS_PKG;

2

SQL%ROWCOUNT returns the number of rows fetched/processed by the last DML executed. If the DML fails after fetching 1 row, due to any reason, SQL%ROWCOUNT will return only 1, the number of rows fetched/processed to that point. COUNT() will give you the number of rows in your table. In your example, the COUNT(1) is the same as COUNT(*).

3

Thanks Dennis.

In my case, for example, the above query is valid and has 1 record in data base. but, in IF Condition if I am giving as SQL%ROWCOUNT, then it is going inside LOOP and if I am using COUNT(1) INTO l_cnt_accnt_num, then 0 is assigning to that variable and going inside LOOP if I am using l_cnt_accnt_num in IF Condition :frowning:

Also, if we are using SQL%ROWCOUNT and database has more than 1 valid record that satisfies that query, then what will SQL%ROWCOUNT holds? (assuming the case you said as DML Fails will never happen)

Thanks in Advance,

Srivathsava

4

It depends on how many rows were processed by the last DML (in your case a query) processed. In your test, I would expect SQL%ROWCOUNT to return 1 since the select count returns 1 row (the count), but they are not reporting the same thing. However, you are also calling it inside the fnd_file procedure - and if that procedur runs a query, then it will most likely return that result, instead of what you are running. My suggestion would be make an anonymous block out of that section of code and replace all the fnd_file calls with dbms_output.put_line statements, and run it like that. This way you are not making any extra calls that are out of your control. Then you can narrow it down from there.

5

btw…In looking at your code, I’m not sure why you would want to call SQL%ROWCOUNT anyway, because all your doing is making sure there is at least one record that would return in your count variable. It looks like if you find a row, then you update it with a status of Valid, otherwise, it’s invalid. Not sure why you would not just trust the count in that case, and forgo SQL%ROWCOUNT altogether. Your already validating that the record is valid or not based on your count query. You generally would only use that when you want to valid the number of rows you updated, or how many records were returned by a cursor, or something like that.