OracleCommandBuilder.DeriveParameters()引发OracleException:ORA-06564:对象不存在ORA-06512:位于"SYS.DBMS_UTILITY"处 [英] OracleCommandBuilder.DeriveParameters() throws OracleException: ORA-06564: object does not exist ORA-06512: at "SYS.DBMS_UTILITY"

问题描述

在.NET Framework中使用ADO.NET附带的OracleClient，我试图在数据库中的过程上调用OracleCommandBuilder.DeriveParameters()方法，但是我不断收到消息中的OracleException:ORA-06564: object CustOrdersOrders does not exist，即使我成功创建了该过程.我对SQL Server更加熟悉，所以也许我在这里错过了一些东西.

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.

文件1.sql:

create or replace PACKAGE PKGENTLIB_ARCHITECTURE
IS
TYPE CURENTLIB_ARCHITECTURE IS REF CURSOR;
END PKGENTLIB_ARCHITECTURE;
/

文件2.prc:

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;
/

文件3.prc

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;
/

所有这些文件都在SQL * Plus中以@"path\to\file1.sql"的身份执行.

All of these files were executed in SQL*Plus as @"path\to\file1.sql".

<configuration>
  <connectionStrings>
    <add name="OracleTest" connectionString="Data Source=(DESCRIPTION=(CID=xe)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))(CONNECT_DATA=(SID=xe)(SERVER=DEDICATED)));User Id=SYSTEM;Password=oracle;" providerName="System.Data.OracleClient" />
  </connectionStrings>
</configuration>

代码

private DbConnection connection;
private OracleCommand command;

[TestInitialize]
public void Initialize()
{
    String connectionString = ConfigurationManager.ConnectionStrings["OracleTest"].ConnectionString;
    connection = new OracleConnection(connectionString);
    command = connection.CreateCommand() as OracleCommand;
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
}

[TestMethod]
public void DeriveParametersWithoutUserDefinedTypes()
{            
    command.CommandText = "AddCountry";
    OracleCommandBuilder.DeriveParameters(command);
    Assert.AreEqual(2, command.Parameters.Count); // fails because Count = 0
}

[TestMethod]
public void DeriveParametersWithUserDefinedTypes()
{
    command.CommandText = "CustOrdersOrders";
    OracleCommandBuilder.DeriveParameters(command); //throws 'ORA-06564: object CustOrdersOrders does not exist\nORA-06512: at "SYS.DBMS_UTILITY", line 156\nORA-06512: at line 1'
    Assert.AreEqual(2, command.Parameters.Count);
}

[TestCleanup]
public void Cleanup()
{
    connection?.Dispose();
}

更多详细信息

这是在我为企业库数据访问应用程序块此处制作的fork中发生的尝试恢复该库.这就是为什么它使用System.Data.OracleClient而不是ODP.NET的原因.

More Details

This is happening in a fork I made for the Enterprise Library Data Access Application Block here in an attempt to revive this library. That's why it's using the System.Data.OracleClient and not the ODP.NET.

测试在我本地安装的Oracle Database XE上运行.

The tests are running on an Oracle Database XE I installed locally.

推荐答案

从我的 Oracle-ish 角度来看，这是您的重大错误:

From my Oracle-ish point of view, this was your huge mistake:

CREATE OR REPLACE PROCEDURE "CustOrdersOrders"
                            -                -
                            these double quotes

因为，默认情况下，Oracle将所有对象名称以大写形式存储在字典中，但是您可以根据需要以任何方式引用它，例如custordersorders，CUSTordERsordERS，CUSTORDERSORDERS，CustOrdersOrders-没问题.但是，如果将任何名称(过程，表，列，...)括在双引号中，则必须在每次引用该对象时都使用双引号，并用相同的双引号和匹配的字母大小写完全与创建该对象时使用的一样.

Because, by default Oracle stores all object names into the dictionary in upper case, but you can reference it any way you want, e.g. custordersorders, CUSTordERsordERS, CUSTORDERSORDERS, CustOrdersOrders - no problem. But, if you enclose any name (procedure, table, column, ...) into double quotes, you must use double quotes any time you reference that object, enclosed into same double quotes and matching letter case exactly as you used it when creating that object.

所以:要么将程序重新创建为CREATE OR REPLACE PROCEDURE CustOrdersOrders(这就是我的建议)，要么使用双引号.

So: either recreate the procedure as CREATE OR REPLACE PROCEDURE CustOrdersOrders (which is what I'd suggest), or use double quotes.

这篇关于OracleCommandBuilder.DeriveParameters()引发OracleException:ORA-06564:对象不存在ORA-06512:位于"SYS.DBMS_UTILITY"处的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！