LLBLGEN 3.1 Rel Date 29-Apr-2011 .NET framework 2.0 C# Winforms Oracle 9i/MSORACLE

Hi

I have create the following SP and added to LLBLGEN. I tested by running directly @ SQL prompt and works fine. But within LLBLGEN, it generate error.

/* Formatted on 16/05/2011 6:40:01 PM (QP5 v5.136.908.31019) */ CREATE OR REPLACE PROCEDURE BCLUB1868.SP_SMARTCARDDETAILS ( P_CARDID NUMBER, P_TRANSACTIONMONTH VARCHAR2, O_RESULTSET OUT TYPES.CURSORTYPE) AS BEGIN OPEN O_RESULTSET FOR SELECT MEMBER.MEMBER_ID || MEMBERFAMILYUSERSPOUSE.MEMBER_ID || MEMBERFAMILY.MEMBER_ID AS MEMBER_ID, AFFILIATEMEMBER.AFFILIATEMEMBER_ID, EMPLOYEE.EMPLOYEE_ID, AFFILIATECLUB.AFFILIATECLUB_ID, ROOM.ROOM_ID, CORPORATE.CORPORATE_ID, SMARTCARD.CARDID, SMARTCARD.POSTPAIDUSE, SMARTCARD.PREPAIDBALANCE, SMARTCARD.DEPOSITAMOUNT, SMARTCARD.PREPAIDCARDEXPIRYDATE, EMPLOYEE.EMPLOYEENO || AFFILIATEMEMBER.AFFILIATEMEMBERNO || MEMBER.MEMBERNO || AFFILIATECLUB.AFFILIATECLUBNO || ROOM.ROOMNO || CORPORATE.CORPORATENO AS ID, SMARTCARD.USERNO, EMPLOYEE.NAME || AFFILIATEMEMBER.NAME || MEMBER.NAME || AFFILIATECLUB.NAME || ROOM.ROOMNO || CORPORATE.NAME AS NAME, MEMBENO.NAME || MEMBERSPOUSE.SPOUSENAME || MEMBERFAMILY.USERNAME || MEMBERFAMILYUSERSPOUSE.USERSPOUSENAME AS USERNAME, MONTHLYBILL.MONTHLYBILL_ID AS MONTHLYBILLID FROM SMARTCARD LEFT JOIN EMPLOYEE ON EMPLOYEE.EMPLOYEE_ID = SMARTCARD.EMPLOYEE_ID LEFT JOIN AFFILIATEMEMBER ON AFFILIATEMEMBER.AFFILIATEMEMBER_ID = SMARTCARD.AFFILIATEMEMBER_ID LEFT JOIN MEMBER ON MEMBER.MEMBER_ID = SMARTCARD.MEMBER_ID LEFT JOIN MEMBER MEMBENO ON MEMBENO.MEMBER_ID = SMARTCARD.MEMBER_ID AND MEMBENO.MEMBERNO = SMARTCARD.USERNO LEFT JOIN MEMBER MEMBERSPOUSE ON (MEMBERSPOUSE.SPOUSENO = SMARTCARD.USERNO) AND MEMBERSPOUSE.MEMBER_ID = SMARTCARD.MEMBER_ID LEFT JOIN MEMBERFAMILY ON (MEMBERFAMILY.USERNO = SMARTCARD.USERNO) AND MEMBERFAMILY.MEMBER_ID = SMARTCARD.MEMBER_ID LEFT JOIN MEMBERFAMILY MEMBERFAMILYUSERSPOUSE ON MEMBERFAMILYUSERSPOUSE.USERSPOUSENO = SMARTCARD.USERNO AND MEMBERFAMILYUSERSPOUSE.MEMBER_ID = SMARTCARD.MEMBER_ID LEFT JOIN AFFILIATECLUB ON AFFILIATECLUB.AFFILIATECLUB_ID = SMARTCARD.AFFILIATECLUB_ID LEFT JOIN ROOMCHECKIN ON ROOMCHECKIN.ROOMCHECKIN_ID = SMARTCARD.ROOMCHECKIN_ID LEFT JOIN ROOM ON ROOMCHECKIN.ROOM_ID = ROOM.ROOM_ID LEFT JOIN CORPORATE ON CORPORATE.CORPORATE_ID = SMARTCARD.CORPORATE_ID LEFT JOIN MONTHLYBILL ON (EMPLOYEE.EMPLOYEE_ID = MONTHLYBILL.EMPLOYEE_ID OR AFFILIATEMEMBER.AFFILIATEMEMBER_ID = MONTHLYBILL.AFFILIATEMEMBER_ID OR AFFILIATECLUB.AFFILIATECLUB_ID = MONTHLYBILL.AFFILIATECLUB_ID OR CORPORATE.CORPORATE_ID = MONTHLYBILL.CORPORATE_ID) WHERE SMARTCARD.CARDID = P_CARDID AND (P_TRANSACTIONMONTH IS NULL OR P_TRANSACTIONMONTH = MONTHLYBILL.TRANSACTIONMONTH); END SP_SMARTCARDDETAILS; /

Stack Trace:

Log Entry : 05/16/2011 18:47:22 Error Message CI-22060: argument [2] is an invalid or uninitialized number at System.Data.OracleClient.OracleException.Check(OciErrorHandle errorHandle, Int32 rc) at System.Data.OracleClient.OracleNumber.ToDecimal(OciErrorHandle errorHandle, Byte[] value) at System.Data.OracleClient.OracleColumn.GetDecimal(NativeBuffer_RowBuffer buffer) at System.Data.OracleClient.OracleColumn.GetValue(NativeBuffer_RowBuffer buffer) at System.Data.OracleClient.OracleDataReader.GetValues(Object[] values) at System.Data.ProviderBase.DataReaderContainer.CommonLanguageSubsetDataReader.GetValues(Object[] values) at System.Data.ProviderBase.SchemaMapping.LoadDataRow() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable) at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterBase.CallRetrievalStoredProcedure(String storedProcedureToCall, DbParameter[] parameters, DataTable tableToFill) in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\AdapterSpecific\DataAccessAdapterBase.cs:line 180 at SD.LLBLGen.Pro.ORMSupportClasses.StoredProcedureCall.FillDataTable() in c:\Myprojects\VS.NET Projects\LLBLGen Pro v3.0\Frameworks\LLBLGen Pro\RuntimeLibraries\ORMSupportClasses\Persistence\StoredProcedureCall.cs:line 134 at ClubCentricHelper.DatabaseSpecific.RetrievalProcedures.SPSmartcarddetails(Decimal pCardid, String pTransactionmonth, IDataAccessCore dataAccessProvider) in D:\Bangalore Club - Software Projects\Under Implementaion\VS Final Solution\ProjectLive\LLBLGen Pro Projects\DatabaseSpecific\RetrievalProcedures.cs:line 2165 at ClubCentricHelper.DatabaseSpecific.RetrievalProcedures.SPSmartcarddetails(Decimal pCardid, String pTransactionmonth) in D:\Bangalore Club - Software Projects\Under Implementaion\VS Final Solution\ProjectLive\LLBLGen Pro Projects\DatabaseSpecific\RetrievalProcedures.cs:line 2152 at ClubCentric.Transaction.TransactionPrepaidCardRecharge.btnreadcard_Click(Object sender, EventArgs e) in D:\Bangalore Club - Software Projects\Under Implementaion\VS Final Solution\ProjectLive\ClubCentric\Transaction\TransactionPrepaidCardReCharge.cs:line 61

What I have noticed is if Monthlybill.Monthlybill_id is not null, then the output comes correct. But if monthlybill_id is null, then it returns error.

I am calling SP like this

DataTable SmartCardDt = RetrievalProcedures.SPSmartcarddetails(58157, null);

I think this is not an LLBLGen issue. As the exception is coming from the Oracle client. You can verify this by calling the same SP using ADO.NET code. using (OracleCommand, OracleConnection...etc).

Walaa wrote:

I think this is not an LLBLGen issue. As the exception is coming from the Oracle client. You can verify this by calling the same SP using ADO.NET code. using (OracleCommand, OracleConnection...etc).

Well did a workaround by adding case

CASE
            WHEN MONTHLYBILL.MONTHLYBILL_ID IS NULL
            THEN
               0
            ELSE
               MONTHLYBILL.MONTHLYBILL_ID
         END
            AS MONTHLYBILL_ID

This works.