본문 바로가기
개발/C#

C#, Oracle UDTs(User-Defied Types) .Net 사용하기

by 이청춘아 2020. 1. 13.

Github 레파지토리 바로가기

01. 필요한 DLL

  • oci.dll
  • ociw32.dll
  • OracleDataAccess.dll
  • oraociei11.dll
  • OraOps11w.dll

 

02. 오라클 사전작업

Oracle type 생성

--
--  바코드 데이터
--
CREATE OR REPLACE TYPE TEST.BARCODE AS OBJECT
(
  Data VARCHAR2(20),
  Type VARCHAR2(20),
  Length NUMBER
)
/

--
--  바코드 목록
--
CREATE OR REPLACE TYPE TEST.BARCODEARRAY
AS VARRAY (1000) OF BARCODE
/

--
--  데이터 묶음
--
CREATE OR REPLACE TYPE TEST.BCRDATA AS OBJECT
(
  Id VARCHAR2(20),
  Count NUMBER,
  Height NUMBER,
  Barcodes TEST.BARCODEARRAY
)
/

 

Oracle SP(stored procedure) 생성

CREATE OR REPLACE PACKAGE TEST.PKG_DEV_TEST AS


PROCEDURE UDT_TEST(
    i_bcr_data IN TEST.BCRDATA,
    io_bcr_data IN OUT TEST.BCRDATA,
    o_result    OUT  varchar2
) IS
    v_txt varchar2(1000);
BEGIN
    o_result := o_result || i_bcr_data.Id;
    o_result := o_result || ', ' || i_bcr_data.Height;
    o_result := o_result || ', ' || i_bcr_data.Barcodes.count;
        
    select listagg(Data, ',') within group (order by Data) name  
    into v_txt
    from table(i_bcr_data.Barcodes);
        
    --
    --  IN OUT 파라미터 테스트로 값을 변경해본다.
    io_bcr_data.Id := i_bcr_data.Id || ' from Oracle';
    
    --
    --  OUT 파라미터 테스트
    o_result := o_result || ', ' || v_txt;
END;


END PKG_DEV_TEST;
/

 

03. C# 코드 작성

Barcode.cs

/// <summary>
/// Factory
/// </summary>
[OracleCustomTypeMapping(Barcode.Name)]
public class BarcodeFactory : IOracleCustomTypeFactory
{
    public IOracleCustomType CreateObject() => new Barcode();
}

/// <summary>
/// User type
/// </summary>
public class Barcode : IOracleCustomType, INullable
{
    public const string Name = "TEST.BARCODE";
    public bool IsNull { get; set; }

    [OracleObjectMapping("DATA")]
    public string Data { get; set; }
    [OracleObjectMapping("TYPE")]
    public string Type { get; set; }
    [OracleObjectMapping("LENGTH")]
    public int Length { get; set; }

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, "DATA", Data);
        OracleUdt.SetValue(con, pUdt, "TYPE", Type);
        OracleUdt.SetValue(con, pUdt, "LENGTH", Length);
    }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        Data = (string)OracleUdt.GetValue(con, pUdt, "DATA");
        Type = (string)OracleUdt.GetValue(con, pUdt, "TYPE");
        Length = (int)OracleUdt.GetValue(con, pUdt, "LENGTH");
    }
}

 

BarcodeArray.cs

/// <summary>
/// Factory
/// </summary>
[OracleCustomTypeMapping(BarcodeArray.Name)]
public class BarcodeArrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new BarcodeArray();
    }

    public Array CreateArray(int numElems)
    {
        return new Barcode[numElems];
    }

    public Array CreateStatusArray(int numElems)
    {
        return new OracleUdtStatus[numElems];
    }
}

/// <summary>
/// User type
/// </summary>
internal class BarcodeArray : IOracleCustomType, INullable
{
    public const string Name = "TEST.BARCODEARRAY";

    public bool IsNull { get; set; }

    [OracleArrayMapping()]
    public Barcode[] Array;

    public OracleUdtStatus[] StatusArray { get; set; }

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, Array);
    }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        object objectStatusArray = null;
        Array = (Barcode[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
        StatusArray = (OracleUdtStatus[])objectStatusArray;
    }

    public static BarcodeArray Null => new BarcodeArray { IsNull = true };
}

 

BcrData.cs

/// <summary>
/// Factory
/// </summary>
[OracleCustomTypeMapping(BcrData.Name)]
public class BcrDataFactory : IOracleCustomTypeFactory
{
    public IOracleCustomType CreateObject() => new BcrData();
}

/// <summary>
/// User type
/// </summary>
public class BcrData : IOracleCustomType, INullable
{
    public const string Name = "TEST.BCRDATA";
    public bool IsNull { get; set; }

    [OracleObjectMapping("ID")]
    public string Id { get; set; }
    [OracleObjectMapping("COUNT")]
    public int Count { get; set; }
    [OracleObjectMapping("HEIGHT")]
    public int Height { get; set; }

    [OracleObjectMapping("BARCODES")]
    public BarcodeArray Barcodes { get; set; }

    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, "ID", Id);
        OracleUdt.SetValue(con, pUdt, "COUNT", Count);
        OracleUdt.SetValue(con, pUdt, "HEIGHT", Height);
        OracleUdt.SetValue(con, pUdt, "BARCODES", Barcodes);
    }

    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        Id = (string)OracleUdt.GetValue(con, pUdt, "ID");
        Count = (int)OracleUdt.GetValue(con, pUdt, "COUNT");
        Height = (int)OracleUdt.GetValue(con, pUdt, "HEIGHT");
        Barcodes = (BarcodeArray)OracleUdt.GetValue(con, pUdt, "BARCODES");
    }
}

 

04. 테스트 코드

public partial class MainWindow : Window
{
    string ConnectionString = "Data Source = (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=testdb))); User ID=test; Password=1";

    public MainWindow()
    {
        InitializeComponent();

        BcrData bcrData = new BcrData();
        bcrData.Id = "BCR01";
        bcrData.Height = 250;

        BarcodeArray barcodeArray = new BarcodeArray();
        barcodeArray.Array = new Barcode[3];

        Barcode barcode1 = new Barcode();
        barcode1.Data = "barcode1";
        barcode1.Type = "A";
        barcode1.Length = barcode1.Data.Length;

        Barcode barcode2 = new Barcode();
        barcode2.Data = "barcode2";
        barcode2.Type = "B";
        barcode2.Length = barcode2.Data.Length;

        Barcode barcode3 = new Barcode();
        barcode3.Data = "barcode3";
        barcode3.Type = "C";
        barcode3.Length = barcode3.Data.Length;

        barcodeArray.Array[0] = barcode1;
        barcodeArray.Array[1] = barcode2;
        barcodeArray.Array[2] = barcode3;

        bcrData.Barcodes = barcodeArray;
        bcrData.Count = bcrData.Barcodes.Array.Length;


        OracleUdtTest(bcrData);
    }

    private void OracleUdtTest(BcrData bcrData)
    {
        try
        {
            using (OracleConnection connection = new OracleConnection(ConnectionString))
            {
                connection.Open();
                //
                //  oracle package
                OracleCommand command = new OracleCommand("TEST.PKG_DEV_TEST.UDT_TEST", connection);
                command.CommandType = CommandType.StoredProcedure;
                command.BindByName = true;
                //
                //  only input param
                OracleParameter i_bcr_data = command.CreateParameter();
                i_bcr_data.ParameterName = "i_bcr_data";
                i_bcr_data.Direction = ParameterDirection.Input;
                i_bcr_data.OracleDbType = OracleDbType.Object;
                i_bcr_data.UdtTypeName = BcrData.Name;
                i_bcr_data.Value = bcrData;
                //
                //  input and output param
                OracleParameter io_bcr_data = command.CreateParameter();
                io_bcr_data.ParameterName = "io_bcr_data";
                io_bcr_data.Direction = ParameterDirection.InputOutput;
                io_bcr_data.OracleDbType = OracleDbType.Object;
                io_bcr_data.UdtTypeName = BcrData.Name;
                io_bcr_data.Value = bcrData;
                //
                //  only output param
                OracleParameter o_result = command.CreateParameter();
                o_result.ParameterName = "o_result";
                o_result.Size = 1000;
                o_result.DbType = DbType.String;
                o_result.Direction = ParameterDirection.Output;
                o_result.Value = "";
                //
                //  append params
                command.Parameters.Add(i_bcr_data);
                command.Parameters.Add(io_bcr_data);
                command.Parameters.Add(o_result);
                //
                //  execute
                command.ExecuteNonQuery();
                //
                //  input and output param result
                BcrData outBcrData = (BcrData) command.Parameters["io_bcr_data"].Value;
                //
                //  only output param result
                string result = command.Parameters["o_result"].Value.ToString();

                Console.WriteLine($"outBcrData = {outBcrData.Id}");
                Console.WriteLine($"result = {result}");
            }
        }
        catch (Exception e)
        {
            Console.WriteLine(e.Message);
            Console.WriteLine(e.StackTrace);
        }
    }
}

결과

outBcrData = BCR01 from Oracle
result = BCR01, 250, 3, barcode1,barcode2,barcode3

댓글