Using the OracleClient that comes with ADO.NET in .NET Framework, I'm trying to call OracleCommandBuilder.DeriveParameters() method on a procedure in the database, but I keep getting an OracleException with the message: ORA-06564: object CustOrdersOrders does not exist, even though I created the procedure successfully. I'm more familiar with SQL Server, so perhaps I'm missing something here.
SQL
file 1:
create or replace PACKAGE PKGENTLIB_ARCHITECTURE
IS
TYPE CURENTLIB_ARCHITECTURE IS REF CURSOR;
END PKGENTLIB_ARCHITECTURE;
/
file 2
CREATE OR REPLACE PROCEDURE "CustOrdersOrders"(VCUSTOMERID IN Orders.CustomerID%TYPE := 1, CUR_OUT OUT PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE)
AS
BEGIN
OPEN cur_OUT FOR
SELECT
OrderID,
OrderDate,
RequiredDate,
ShippedDate
FROM Orders
WHERE CustomerID = vCustomerId;
END;
/
Both these files were executed in SQL*Plus as @"path\to\file1.sql".
Code
This is using the Enterprise Library Data Access Application Block, which ultimately wraps the ADO.NET API.
DatabaseProviderFactory factory = new DatabaseProviderFactory(...); //this gets a custom configuration source
Database db = factory.Create("OracleTest");
DbCommand storedProcedure = db.GetStoredProcCommand("CustOrdersOrders");
DbConnection connection = db.CreateConnection();
connection.Open();
storedProcedure.Connection = connection;
db.DiscoverParameters(storedProcedure); //this ultimately calls OracleCommandBuilder.DeriveParameters(), which throws the exception.
When I run direct SQL queries using the same connection, they succeed.
More Details
This is actually part of unit tests written for the Data Access Application Block, which I forked here in an attempt to revive this library. That's why it's using the System.Data.OracleClient and not the ODP.NET. The entire set of tests at https://github.com/tsahi/data-access-application-block/blob/master/source/Tests/Oracle.Tests.VSTS/OracleParameterDiscoveryFixture.cs breaks in a similar way.
The tests are running on an Oracle Database XE I installed locally.
Update
Following question by @madreflection, yes, the following code runs correctly:
Database db = DatabaseFactory.CreateDatabase("OracleTest");
string spName = "AddCountry";
DbCommand dbCommand = db.GetStoredProcCommand(spName);
db.AddInParameter(dbCommand, "vCountryCode", DbType.String);
db.AddInParameter(dbCommand, "vCountryName", DbType.String);
db.SetParameterValue(dbCommand, "vCountryCode", "UK");
db.SetParameterValue(dbCommand, "vCountryName", "United Kingdom");
db.ExecuteNonQuery(dbCommand);
using (DataSet ds = db.ExecuteDataSet(CommandType.Text, "select * from Country where CountryCode='UK'"))
{
Assert.IsTrue(1 == ds.Tables[0].Rows.Count);
Assert.AreEqual("United Kingdom", ds.Tables[0].Rows[0]["CountryName"].ToString().Trim());
}
where "AddCountry" is defined as
CREATE OR REPLACE PROCEDURE ADDCOUNTRY
(vCountryCode IN Country.CountryCode%TYPE,
vCountryName IN Country.CountryName%TYPE
)
AS
BEGIN
INSERT INTO Country (CountryCode,CountryName)
VALUES (vCountryCode,vCountryName);
END;
/
It's interesting to note, though, that in this case the OracleDatabase pointed by db has in it's packages list just EntlibTest, defined (if I understand correctly) by
CREATE OR REPLACE PACKAGE EntlibTest AS
PROCEDURE GetProductDetailsById
(vProductID IN NUMBER,vProductName OUT VARCHAR2,vUnitPrice OUT NUMBER);
END EntlibTest;
/
and then there is another file defining the body of this procedure with
CREATE OR REPLACE PACKAGE BODY EntlibTest AS
PROCEDURE GetProductDetailsById
(vProductID IN NUMBER,vProductName OUT VARCHAR2,vUnitPrice OUT NUMBER)
AS
BEGIN
SELECT ProductName,UnitPrice INTO vProductName,vUnitPrice FROM Products where ProductId = vProductId;
END;
END EntlibTest;
/
