Dataset not taking more than 39 rows!

Question

I have a dataset that I fill using Microsoft ODP.net and Oracle9i database. The sql returns 60 rows however the OracleHelper.ExecuteMappedDataset returns 0 rows. I found out that if the number of data rows returned is less than 40 I see the data in the dataset with count = 39 otherwise the count = 0 Any idea what could be causing it? Thanks J.

jana_ban · Answer

It has nothing to do with UTF16 or substr() the sql was returning NULL in some columns and when that was dealt with using NVL() the problem was reloved. Anyhow if you face such dilemma use the hot fix from microsoft or .net 1.1 to be able to see the actual warnings from Oracle.  http://support.microsoft.com/?id=331499 This article was previously published under Q331499 SYMPTOMS When you execute a query using Microsoft .NET Framework Managed Provider for Oracle and if the Oracle database server returns a warning message you will always receive an empty DataSet even when the query returns a DataSet that is not empty. RESOLUTION A supported fix is now available from Microsoft but it is only intended to correct the problem described in this article. Only apply it to systems that are experiencing this specific problem. This fix may receive additional testing to further ensure product quality. Therefore if you are not severely affected by this problem Microsoft recommends that you wait for the next version of .NET Framework that contains this fix. To resolve this problem immediately contact Microsoft Product Support Services to obtain the fix. For a complete list of Microsoft Product Support Services phone numbers and information about support costs visit the following Microsoft Web site: ..

spgilmore · Answer

Yes it could. UTF16 is unicode and uses multiple bytes per character. This forces it to use twice as much buffer space. This might only affect it if the client is using ASCII (which is one byte per character). If it is using UTF-8 or other type of unicode it probably won't make a difference.

jana_ban · Answer

the database server is Oracle9i however the client is still oracle8i

jana_ban · Answer

might the following be the reason? from Microsoft .NET Framework Data Provider for Oracle Reference Documentation System Requirements for the .NET Framework Data Provider for Oracle .... Oracle client software prior to version Oracle 9i cannot access UTF16 databases because UTF16 is a new feature in Oracle 9i. To use this feature you must upgrade your client software to Oracle 9i or later.

jana_ban · Answer

Yes the SQL procedure works fine manually. When called from the app with the followig: _claimSubmissionReportDataSet = (ClaimSubmissionReportDataSet)OracleHelper.ExecuteMappedDataset(PICommon.DBConnectionString CommandType.StoredProcedure schemaName + . + packageName + . + procedureName _claimSubmissionReportDataSet this.GetMapping(MapType.Query ReportDetail) parms); at first it returned only 39 rows then when I added the substr to the SQL as below it loaded the data set with 43 rows but if total is over 100 zero rows are loaded to the dataset. adding the substr sure made a difference. select distinct cf.cf_claim_form_id pr_provider_id providerId cf.payee_type_cd payeeTypeCd pr_trading_name providerName pr_account_no providerNo substr(cf.cf_part_last_name030) patientSurName substr(cf.cf_part_first_name030) patientFirstName decode(cf.cf_participant_id NULLTO_CHAR(cf.su_subs_id) cf.cf_participant_id) subscriberId cf.pa_dep_no depNo cd.cd_serv_dt serviceDate cd.cd_proc_din_no procCd substr(bc.bc_short_desc030) bcEnglishDesc substr(bc.bc_french_desc030) bcFrenchDesc cd.cd_rend_cdn_amt rendAmt sum(cob.cobc_carrier_paid_amt) paidByOther cd.cd_paid_amt paidAmt from prod.claim_form cf prod.clm_detail cd prod.benefit_category bc prod.clm_detl_cob_carrier cob prod.provider pr_prov where cf.cf_subm_provider_id = i_provider_id --106365 and cf.delete_dt is null -- and cf.row_exp_dt is null and cf.cf_lupd_oper_cd = i_user_id and cf.claim_type_cd = 'CL' -- and cf.status_type_cd = 'PC' and cf.cf_claim_form_id = cd.cf_claim_form_id and cf.revision_no = cd.claim_form_revision_no and cf.cf_entered_dt = trunc(i_submission_dt) --and cd.status_type_cd in ('PD' 'AW') and ( cd.cf_claim_form_id = cob.cf_claim_form_id(+) and cd.claim_form_revision_no =cob.claim_form_revision_no(+) and cd.cl_claim_id =cob.cl_claim_id(+) and cd.claim_revision_no =cob.claim_revision_no(+) and cd.cd_detail_id = cob.cd_detail_id (+) ) and bc.bc_bnft_catg_type_cd = cd.bc_bnft_catg_type_cd and bc.bc_bnft_catg_id = cd.bc_bnft_catg_id and ( bc.delete_dt is null and trunc(sysdate)>trunc(bc.row_eff_dt) and (bc.row_exp_dt is null or bc.row_exp_dt>=trunc(sysdate)) ) and pr_prov.pr_provider_id = cf.cf_subm_provider_id and pr_prov.PROVIDER_STATUS_CD = 'E' AND pr_prov.PARTIC_STATUS_CD IN ('NP' 'PA') and pr_prov.delete_dt is null and pr_prov.row_eff_dt <= sysdate and ( pr_prov.row_exp_dt is null or trunc(sysdate) <= pr_prov.row_exp_dt ) group by cf.cf_claim_form_id pr_provider_id pr_trading_name pr_account_no cf.payee_type_cd cf.cf_part_first_name cf.cf_part_last_name cf.su_subs_id cf.cf_participant_id cf.pa_dep_no cd.cd_serv_dt cd.cd_proc_din_no bc.bc_short_desc bc.bc_french_desc cd.cd_rend_cdn_amt cd.cd_paid_amt order by pr_provider_id cf.payee_type_cd patientSurName patientFirstName subscriberId cf.pa_dep_no cd.cd_serv_dt;

spgilmore · Answer

Try posting some code. If there is an oversight in your code we can't see it unless you show it to us. Is this a stored procedure or a query etc? What happens when you execute this query manually? Do you get all the records you want? Have you tried another provider such as an OLEDB.NET provider?

jana_ban · Answer

narrowing down the investigation to the SQL I found that having multiple strings retrieved is causing the dataset not to load. I used substr(the selected string) that seemed to allow about 100 rows to display but not more. Still the problem is not resolved and What if I don't want to substring the string? what's limiting the mapping into the dataset? any help is appreciated thanks