将数据表传递给Oracle存储过程的后代 [英] Pass datatable to refcursor of Oracle stored procedure

问题描述

我正在使用ODP.NET和Oracle 11g客户端.我正在将数据表传递给以refcursor作为输入的过程.

I am using ODP.NET and Oracle 11g client. I am passing a datatable to a procedure with a refcursor as input.

dtSample是DataTable，有四个列分别称为Type，Name，Size和Description.

dtSample is DataTable with four columns called Type, Name, Size and Description.

我遇到错误

无效的参数绑定，参数名称:ParameterName

Invalid parameter binding, Parameter name: ParameterName

在线

db.ExecuteNonQuery(oracleCommand);

完整代码:

OracleCommand oracleCommand = new OracleCommand();
oracleCommand.CommandType = CommandType.StoredProcedure;
oracleCommand.CommandText = "PCK_ADMIN.PROC_INS";
oracleCommand.Parameters.Add("P_USERID", OracleDbType.Varchar2).Value = "abcd";
oracleCommand.Parameters.Add("P_ATTACH_LIST", OracleDbType.RefCursor).Value = dtSample;
oracleCommand.Parameters.Add("P_out", OracleDbType.Int32).Direction = ParameterDirection.Output;
oracleCommand.Parameters.Add("P_msg", OracleDbType.Varchar2, 50).Direction = ParameterDirection.Output;

db.ExecuteNonQuery(oracleCommand);

存储过程:

PROCEDURE PROC_INS(  P_USERID IN VARCHAR2,
                            P_ATTACH_LIST IN SYS_REFCURSOR,
                            P_out      OUT NUMBER,
                             P_msg     OUT VARCHAR2) AS

V_BRS_USERID         VARCHAR2(50);
V_ATTACHMENT_TYPE_ID BRS_USER_ATTACHMENT.ATTACHMENT_TYPE_ID%TYPE;
V_FILE_NAME          BRS_USER_ATTACHMENT.FILE_NAME%TYPE;
V_FILE_SIZE          BRS_USER_ATTACHMENT.FILE_SIZE%TYPE;
V_FILE_DESCR         BRS_USER_ATTACHMENT.FILE_DESCR%TYPE; 

BEGIN 
LOOP 
FETCH P_ATTACH_LIST INTO V_BRS_USERID, V_ATTACHMENT_TYPE_ID,V_FILE_NAME, V_FILE_SIZE, V_FILE_DESCR; 
EXIT WHEN P_ATTACH_LIST%NOTFOUND; 
    INSERT INTO USER_ATTACHMENT VALUES
            (SEQ_RER_EMP_REP_ID.NEXTVAL,
              V_BRS_USERID,
              V_ATTACHMENT_TYPE_ID,
              V_FILE_NAME,
              V_FILE_SIZE,
              V_FILE_DESCR,
              NULL,
              NULL,
              0,
              0,
              'A',
              P_USERID,
              SYSDATE,
              NULL,
              NULL
            );

END LOOP; 

CLOSE P_ATTACH_LIST;
EXCEPTION
  WHEN OTHERS THEN
  ROLLBACK; -- Transaction mgmt
  p_out := 2;
  p_msg := sqlerrm;

END PROC_REG_INS_ATTACH;            

推荐答案

您不能.您将需要传递另一个ref游标，并且只能将其作为存储过程/函数的输出，或者作为对匿名块中的表的查询来获取...也许您可以创建使用不同包装方法的包装存储过程数据类型，例如关联数组. http://www.oracle.com/technetwork /issue-archive/2006/06-jan/o16odpnet-087852.html

You can't. You'll need to pass in another ref cursor and you can only get that as the output of a stored procedure/function, or a query against a table in an anonymous block... perhaps you can create a wrapper stored procedure that uses different data types, such as associative arrays. http://www.oracle.com/technetwork/issue-archive/2006/06-jan/o16odpnet-087852.html

这篇关于将数据表传递给Oracle存储过程的后代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！