CODEWITHSUNDEEP
c#asp.netoracle10g
Khurram Zulfiqar Ali
Khurram Zulfiqar Ali

Reputation: 241

ORA-00900: invalid SQL statement while filling DataAdapter with Dataset. C# ASP.net

I am using VS'12 ASP.net 4.5 and oracle 10.2.0.1

Here is my code where exception arises.

Agreement agreement = null;

            using (OracleConnection MyConn = new BaseRepository().GetConnection())
            {

                MyConn.Open();
                OracleCommand commOracle = new OracleCommand("PACKAGE_AGREEMENT.USP_GET_AGREEMENT_BY_ID", MyConn);


                VendorRepository vendorRepository = new VendorRepository();

                commOracle.Parameters.Add("SP_AGREEMENT_ID", OracleDbType.Int32, Agreementid, System.Data.ParameterDirection.Input);
                commOracle.Parameters.Add("SP_CUR_AGREEMENT", OracleDbType.RefCursor, ParameterDirection.Output);

                OracleDataAdapter daOracle = new OracleDataAdapter(commOracle);
                DataSet dsOracle = new DataSet();

                daOracle.Fill(dsOracle);//Here the exception arises.


                if (dsOracle.Tables[0].Rows[0] != null)
                {
                    DataRow row = dsOracle.Tables[0].Rows[0];
                    agreement = new Agreement();

                    agreement.pkAgreementId = Convert.ToInt32(row["pkAgreementId"]);
                    agreement.Vendor = vendorRepository.GetById(Convert.ToInt32(row["fkVendorId"]));
                    agreement.IsActive = row["IsActive"].ToString() == "N" ? false : true;
                    agreement.IsDeleted = row["IsDeleted"].ToString() == "N" ? false : true;
                    agreement.RentalAmount = Convert.ToInt32(row["RentalAmount"]);
                    agreement.VehicleCost = Convert.ToInt32(row["VehicleCost"]);
                    agreement.DateOfAgreement = Convert.ToDateTime(row["DateOfAgreement"]);
                    agreement.DateCreated = Convert.ToDateTime(row["DateCreated"]);
                    agreement.DateModified = Convert.ToDateTime(row["DateModified"]);

                }
            }

My Package PACKAGE_AGREEMNET specification:

create or replace PACKAGE PACKAGE_AGREEMENT AS TYPE REF_CURSOR IS REF CURSOR; PROCEDURE USP_GET_AGREEMENT_BY_ID(SP_AGREEMENT_ID IN NUMBER, SP_CUR_AGREEMENT OUT REF_CURSOR);  END PACKAGE_AGREEMENT;

Package body :

create or replace PACKAGE BODY PACKAGE_AGREEMENT AS PROCEDURE USP_GET_AGREEMENT_BY_ID(SP_AGREEMENT_ID IN NUMBER, SP_CUR_AGREEMENT OUT REF_CURSOR) IS BEGIN OPEN SP_CUR_AGREEMENT FOR SELECT "Agreement".*, ora_rowscn as TimeStamp FROM "Agreement" WHERE "pkAgreementId" = SP_AGREEMENT_ID AND "IsDeleted" = 'N'; END USP_GET_AGREEMENT_BY_ID; END PACKAGE_AGREEMENT;

Now if I run the package in the visual studio (data connections). it compiles with no errors and no warnings. I don't know where the problem is.

Please help.

Thanks in advance.

Upvotes: 2

Views: 2329

Answers (1)

Wahaj Ahmed Ansari
Wahaj Ahmed Ansari

Reputation: 263

Have you set your command type earlier before executing it??

Upvotes: 1

Related Questions