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
댓글