C#调用Oracle存储过程插入数据失败问题求助

我刚接触用C#执行CRUD操作，已经实现了记录删除，但插入新记录时遇到困难。现有可正常运行的PL/SQL插入逻辑，我尝试用C#调用对应的存储过程，怀疑是attr_参数的构造方式有误。

DECLARE
   a_ VARCHAR2(32000) := NULL; --p0
   b_ VARCHAR2(32000) := NULL; --p1
   c_ VARCHAR2(32000) := NULL; --p2
   d_ VARCHAR2(32000) := 'CONTRACT'||chr(31)||'BISAM'||chr(30)||'REQUISITIONER_CODE'||chr(31)||'IFSAPP'||chr(30)||'ORDER_CODE'||chr(31)||'1'||chr(30); --p3
   e_ VARCHAR2(32000) := 'DO'; --p4

BEGIN
ifsapp.PURCHASE_REQUISITION_API.NEW__( a_ , b_ , c_ , d_ , e_ );
EXCEPTION 
WHEN OTHERS THEN 
rollback;
raise;
END;

DECLARE
BEGIN
    COMMIT;
END;

PROCEDURE New__ (
   info_       OUT    VARCHAR2,
   objid_      OUT    VARCHAR2,
   objversion_ OUT    VARCHAR2,
   attr_       IN OUT VARCHAR2,
   action_     IN     VARCHAR2 );

public void InserNewRecord()
{
    SetConnection(); // Sets the connection con = new OracleConnection
    cmd = new OracleCommand();
    cmd.Connection = con;
    cmd.CommandText = "ifsapp.PURCHASE_REQUISITION_API.NEW__";
    cmd.CommandType = CommandType.StoredProcedure;

    string attr_ = "'CONTRACT'||chr(31)||'BISAM'||chr(30)||'REQUISITIONER_CODE'||chr(31)||'IFSAPP'||chr(30)||'ORDER_CODE'||chr(31)||'1'||chr(30);";
    string action_ = "DO";

    cmd.Parameters.Add(new OracleParameter()
    {
        ParameterName = "info_",
        Value = null,
        Direction = ParameterDirection.Output
    });
    cmd.Parameters.Add(new OracleParameter()
    {
        ParameterName = "objid_",
        Value = null,
        Direction = ParameterDirection.Output
    });
    cmd.Parameters.Add(new OracleParameter()
    {
        ParameterName = "objversion_",
        Value = null,
        Direction = ParameterDirection.Output
    });
    cmd.Parameters.Add(new OracleParameter()
    {
        ParameterName = "attr_",
        Value = attr_,
        Direction = ParameterDirection.InputOutput
    });
    cmd.Parameters.Add("action_", OracleDbType.Varchar2).Value = action_;

    int x=cmd.ExecuteNonQuery();

    cmd.Parameters.Clear();
}

Oracle.ManagedDataAccess.Client.OracleException: 'ORA-20111: UserAllowedSite.RECNOTEXIST:

Site not used for user IFSAPP

ORA-06512: at "IFSAPP.ERROR_SYS", line 203

ORA-06512: at "IFSAPP.USER_ALLOWED_SITE_API", line 610

ORA-06512: at "IFSAPP.PURCHASE_REQUISITION_API", line 680

ORA-06512: at "IFSAPP.PURCHASE_REQUISITION_API", line 1117

核心问题出在attr_参数的构造上：

• PL/SQL中chr(31)和chr(30)是实际的控制字符（单元分隔符和记录分隔符），但你在C#中把整个PL/SQL表达式作为字符串传递，Oracle不会解析这个字符串里的||和chr()函数，而是直接把这段文本当成attr_的值，导致存储过程无法识别正确的字段-值对。

修正步骤： #

1. 构造正确的attr_字符串
   直接在C#中生成包含对应控制字符的字符串，而非传递PL/SQL表达式：

char unitSeparator = (char)31;
char recordSeparator = (char)30;
string attr_ = $"CONTRACT{unitSeparator}BISAM{recordSeparator}REQUISITIONER_CODE{unitSeparator}IFSAPP{recordSeparator}ORDER_CODE{unitSeparator}1{recordSeparator}";

2. 修正参数配置
   输出参数需要指定正确的OracleDbType和长度，避免截断或参数错误：

// 示例：info_参数的正确配置
cmd.Parameters.Add(new OracleParameter()
{
    ParameterName = "info_",
    OracleDbType = OracleDbType.Varchar2,
    Size = 32000,
    Direction = ParameterDirection.Output
});
// objid_、objversion_、attr_参数都需要做同样的类型和长度配置

3. 添加事务提交逻辑
   和PL/SQL中的COMMIT对应，执行后显式提交事务，确保插入持久化：

con.Commit();

修正后的完整C#代码 #

public void InsertNewRecord()
{
    SetConnection(); // 确保连接已正确打开
    try
    {
        using (var cmd = new OracleCommand("ifsapp.PURCHASE_REQUISITION_API.NEW__", con))
        {
            cmd.CommandType = CommandType.StoredProcedure;

            // 构造正确的attr_参数
            char unitSeparator = (char)31;
            char recordSeparator = (char)30;
            string attr_ = $"CONTRACT{unitSeparator}BISAM{recordSeparator}REQUISITIONER_CODE{unitSeparator}IFSAPP{recordSeparator}ORDER_CODE{unitSeparator}1{recordSeparator}";
            string action_ = "DO";

            // 配置输出参数
            cmd.Parameters.Add(new OracleParameter()
            {
                ParameterName = "info_",
                OracleDbType = OracleDbType.Varchar2,
                Size = 32000,
                Direction = ParameterDirection.Output
            });
            cmd.Parameters.Add(new OracleParameter()
            {
                ParameterName = "objid_",
                OracleDbType = OracleDbType.Varchar2,
                Size = 32000,
                Direction = ParameterDirection.Output
            });
            cmd.Parameters.Add(new OracleParameter()
            {
                ParameterName = "objversion_",
                OracleDbType = OracleDbType.Varchar2,
                Size = 32000,
                Direction = ParameterDirection.Output
            });

            // 配置attr_参数
            cmd.Parameters.Add(new OracleParameter()
            {
                ParameterName = "attr_",
                OracleDbType = OracleDbType.Varchar2,
                Size = 32000,
                Value = attr_,
                Direction = ParameterDirection.InputOutput
            });

            // 配置action_参数
            cmd.Parameters.Add("action_", OracleDbType.Varchar2).Value = action_;

            cmd.ExecuteNonQuery();
            con.Commit(); // 提交事务
        }
    }
    catch (Exception ex)
    {
        con.Rollback(); // 出错时回滚
        throw;
    }
    finally
    {
        con.Close(); // 关闭连接
    }
}

修改后attr_参数与PL/SQL中生成的内容完全一致，存储过程能正确解析字段和对应值，可解决当前报错问题。

内容的提问来源于stack exchange，提问作者Gokhan