10

This answer to this question is fine, but I'm looking for ADO.NET code to be able to send an array or table to an Oracle procedure and then use that table in the procedure.

In SQL Server table-valued parameters, it's pretty straightforward:

CREATE TYPE [dbo].[IntTable] AS TABLE(
    [intvalue] [int] NOT NULL,
    PRIMARY KEY CLUSTERED 
    (
        [intvalue] ASC
    )WITH (IGNORE_DUP_KEY = OFF)
)
GO

CREATE PROCEDURE dbo.UseTable
    @SomeInt INT
    ,@IntTable dbo.IntTable READONLY
AS 
BEGIN
    -- Do whatever using @SomeInt and @IntTable like:
    INSERT INTO Assignments (masterid, childid)
    SELECT @SomeInt, intvalue
    FROM @IntTable
END
GO

Then on the client:

var param = new List<int>();
param.Add(1);
param.Add(2);

Cm.Parameters
    .AddWithValue("@IntTable", param /* IEnumerable<Int> */)
    .SqlDbType = SqlDbType.Structured

This is what I currently have:

CREATE OR REPLACE TYPE TRAIT_ID_TABLE AS TABLE OF NUMBER;

PROCEDURE SET_TRAITS(P_CUST_TANK_PROD_ID IN CUST_TANK_PROD.CUST_TANK_PROD_ID%TYPE, P_TRAIT_IDS IN TRAIT_ID_TABLE)
AS
BEGIN
  DELETE FROM TANK_TRAIT
        WHERE CUST_TANK_PROD_ID = P_CUST_TANK_PROD_ID;

  INSERT INTO TANK_TRAIT(CUST_TANK_PROD_ID, TRAIT_ID)
     SELECT P_CUST_TANK_PROD_ID, COLUMN_VALUE FROM TABLE(P_TRAIT_IDS);

  COMMIT;
EXCEPTION
  WHEN OTHERS
  THEN
     ROLLBACK;
END;


var param = new OracleParameter();
param.ParameterName = "P_TRAIT_IDS";
param.OracleDbType = OracleDbType.Decimal;
param.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
param.Direction = ParameterDirection.Input;
param.Value = traitIdList.ToArray<int>();
param.Size = traitIdList.Count;
cmd.Parameters.Add(param);

And I get this on the ExecuteNonQuery:

System.AccessViolationException was caught
  Message=Attempted to read or write protected memory. This is often an indication that other memory is corrupt.
  Source=Oracle.DataAccess
  StackTrace:
       at Oracle.DataAccess.Client.OpsSql.ExecuteNonQuery(IntPtr opsConCtx, IntPtr& opsErrCtx, IntPtr& opsSqlCtx, IntPtr& opsDacCtx, IntPtr opsSubscrCtx, Int32& isSubscrRegistered, OpoSqlValCtx*& pOpoSqlValCtx, OpoSqlRefCtx& pOpoSqlRefCtx, IntPtr[] pOpoPrmValCtx, OpoPrmRefCtx[] pOpoPrmRefCtx, OpoMetValCtx*& pOpoMetValCtx, Int32 prmCnt)
       at Oracle.DataAccess.Client.OracleCommand.ExecuteNonQuery()
       at EDC2.Domain.TraitList.SaveTraits(String connectionString) in C:\code\EDC2\trunk\app\EDC2.Domain\Trait.cs:line 195
  InnerException:
Improve this question
2
  • 1
    Please see stackoverflow.com/questions/5557318/… Commented Apr 19, 2012 at 4:14
  • @Eggi Unfortunately that technique does not work with the 10g client and attempting to mix 11g client and 10g client work in .NET (or even to get them to try to all run on the 11g client) is giving me fits with incompatible provider errors and constructor intialization problems. Commented May 29, 2012 at 14:24

1 Answer 1

Reset to default
21
+50

This works for ODP.NET (odac):

Your Oracle package will be setup like:

CREATE OR REPLACE package SOME_PACKAGE as
  ...
  type t_number_tab is table of number index by pls_integer;
  ...
  procedure ins_test(i_id_tab in t_number_tab, o_inserted out number);

end SOME_PACKAGE;


CREATE OR REPLACE package body SOME_PACKAGE as

    procedure ins_test(i_id_tab in t_number_tab, o_inserted out number) is
    begin
        -- inserts all records to test table based on incoming table of ids
        forall i in i_id_tab.first .. i_id_tab.last
            insert into TEST_TAB
            (id, val1, val2)
            select id,val1,val2
            from main_tab
            where id = i_id_tab(i);

        o_inserted := SQL%ROWCOUNT;

        commit;
    exception
        when others then
            rollback;
            raise;
    end ins_test;
...
end SOME_PACKAGE;

Then your C# code would look like:

string connStr = "User Id=xxx;Password=xxxx;Data Source=xxxxx;";
OracleConnection _conn = new OracleConnection(connStr);
_conn.Open();

OracleCommand cmd = _conn.CreateCommand();
cmd.CommandText = "some_package.ins_test";
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p1 = new OracleParameter();
OracleParameter p2 = new OracleParameter();

p1.OracleDbType = OracleDbType.Decimal;
p1.Direction = ParameterDirection.Input;
p2.OracleDbType = OracleDbType.Decimal;
p2.Direction = ParameterDirection.Output;

p1.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
p1.Value = new int[3] { 1, 2, 3 };
p1.Size = 3;

cmd.Parameters.Add(p1);
cmd.Parameters.Add(p2);

cmd.ExecuteNonQuery();
Improve this answer
Sign up to request clarification or add additional context in comments.

7 Comments

Thanks - I'm trying this instead of the UDT method which only works with 11g client in development. However, I'm getting System.AccessViolationException
works for me, sounds like maybe you have multiple client versions installed and is causing issues(?, trying to use an incorrect dll ?), you definitely shouldn't be getting access violation from this (managed) code. Your ODAC version should be compatible with your Oracle client version
Gave up, went to a string holding comma-separated values - noticed that I had to change from OracleDbType to the regular DbType datatype to stop it crashing because I got the same error with the string/varchar parameter.
@CadeRoux sorry to hear that, sounds like you have a setup/environment issue more than anything, but best of luck!
Yes, I've got to get these dependencies cleaned up - the app uses both Microsoft and Oracle providers.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.