ODP(Oracle에 Array/Table Parameter를 사용하는 방법)ADO를 통해 NET 10g).NET/C#?

ODP(Oracle에 Array/Table Parameter를 사용하는 방법)ADO를 통해 NET 10g).NET/C#?

이 질문에 대한 답변은 괜찮지만, ADO를 찾고 있습니다.배열 또는 테이블을 Oracle 프로시저로 보낸 다음 해당 테이블을 프로시저에서 사용할 수 있도록 NET 코드를 지정합니다.

SQL Server 테이블 값 매개 변수의 경우 다음과 같이 매우 간단합니다.

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

그럼 클라이언트에서:

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

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

이것이 현재 제가 가지고 있는 것입니다.

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

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: 

이것은 ODP에 적합합니다.NET(odac):

Oracle 패키지는 다음과 같이 설정됩니다.

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;

그러면 C# 코드는 다음과 같습니다.

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

언급URL : https://stackoverflow.com/questions/10217922/how-to-use-array-table-parameter-to-oracle-odp-net-10g-via-ado-net-c