4262

Pass datatable to refcursor of Oracle stored procedure

Question:

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

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

I got error

<blockquote>

Invalid parameter binding, Parameter name: ParameterName

</blockquote>

at the line

db.ExecuteNonQuery(oracleCommand);

Full code:

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

Stored procedure:

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;

Answer1:

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. <a href="http://www.oracle.com/technetwork/issue-archive/2006/06-jan/o16odpnet-087852.html" rel="nofollow">http://www.oracle.com/technetwork/issue-archive/2006/06-jan/o16odpnet-087852.html</a>

Recommend

  • Error: Parameter Type is not supported in ADODB code of classic ASP
  • Using a ref cursor as input type with ODP.NET
  • passing controller json result to view [closed]
  • Calling stored procedure from C# code
  • Using Spring @Procedure to call StoredProcedure without binding to a table
  • C# : how to use parameters for SQL Server stored procedure?
  • Execute stored procedure named as a string
  • Get DataField value while updating row (asp GridView) asp.NET C#
  • Report not showing up on report viewer
  • How to give each table (or group of courses) a specific color in this HTMLTable?
  • Why some stored procedures do not return data in ASP.Net C#
  • Reversing typeof to use Linq Field
  • Post-Redirect-Get (PRG) Summary Page on Submit
  • Sending parameters to stored procedures vb.net
  • No mapping exists from object type System.Windows.Forms.DateTimePicker to a known managed provider n
  • What is happening in debug compilation that is causing the query to take longer to execute?
  • ASP.NET - Trying to implement SortParameterName property to SqlDataSource
  • noob queries on unicode and str methods in Python
  • unable to deploy nodejs socket io application on openshift
  • Combinations of a string with specific variable characters
  • Memory Leak with OracleCommand
  • Accessing types defined inside package from java
  • PL/SQL: re-write SELECT statement using IN parameter in stored procedure
  • gridview on page won't refresh, even when calling databind again
  • Can I use DynamicParameters with Template and have a return parameter in dapper?
  • SQL Procedures - Comparing values
  • How do you specify multiple AppleScript child elements of the same class?
  • DML and Exception Handling - Oracle
  • Concise regex extract function in XSLT 2.0
  • Does for loop open and close a database connection on each iteration?
  • Yii2: Config params vs. const/define
  • NetLogo BehaviorSpace - Measure runs using reporters
  • Spring security and special characters
  • JSON with duplicate key names losing information when parsed
  • C# - Getting references of reference
  • JTable with a ScrollPane misbehaving
  • Angular 2 constructor injection vs direct access
  • Java static initializers and reflection
  • Android Google Maps API OnLocationChanged only called once
  • UserPrincipal.Current returns apppool on IIS