使用DBMS_UTILITY.format_error_backtrace

DBMS_UTILITY.format_error_backtrace

For Oracle 10g以上的EXCEPTION TRACE~

Sample from book :
------------------------PK spec-----------------------------

CREATE OR REPLACE PACKAGE EEP.ERRORTEST
AS
    PROCEDURE proc1;
    PROCEDURE proc2;
    PROCEDURE proc3;
END;

Auto increase column in Oracle

Auto increase column

1.      開啟青蛙

2.      點選Sequences

3.      在空白處按右鍵→Creat Sequence

2.      輸入以下資料，按下OK即可，Sequence Name 請輸入你要的 ex: XXXXXX

3.      使用方式：

1.      "INSERT INTO Table VALUES(XXXXXX .NEXTVAL, BrokerId , TermId, SeqNo, Stock, BSCode, MthQty, ’ MthPrice, SendFlag)"

Ex: "INSERT INTO MATCH VALUES(XXXXXX .NEXTVAL,’9898’, ‘A’, ‘0001’, ‘2330’, ’B’, ‘30’, ’20.2’, 'F')";

2.取出目前櫃號：SELECT XXXXXX.CURRVAL FROM DUAL

3.取用一組新的值：SELECT XXXXXX. NEXTVAL FROM DUAL

6. 刪除Sequence

C#DTO collection & Oracle UDTs Collection

C#--- class

class SimpleTable : IOracleCustomType
{

    [OracleArrayMapping()]
   //Dto是自己的DTO
    public Dto[] Value;
    public virtual void FromCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
            OracleUdt.SetValue(con, pUdt, 0, this.Value);
    }

    public virtual void ToCustomObject(OracleConnection con, System.IntPtr pUdt)
    {
        Value = (Dto[])OracleUdt.GetValue(con, pUdt, 0);
        //this.Value = ((Dto[])(OracleUdt.GetValue(con, pUdt, 0)));
    }
    [OracleCustomTypeMapping("TESTTABLE")]
    public class SimpleTableFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            return new SimpleTable();
        }

        public virtual Array CreateArray(int length)
        {
            return new Dto[length];
        }

        public virtual Array CreateStatusArray(int length)
        {
            return null;
        }
    }
}

雙網卡設定

參考網址：http://skyboxs.net/2011/06/791/
上班的地方鎖line跟skype，
為了方便通訊不得不出此下策。

C# ARRAY n Oracle UDTs VARRAY

參考網址：http://www.codeproject.com/Articles/33829/How-to-use-Oracle-11g-ODP-NET-UDT-in-an-Oracle-Sto

C#--- class

public class SimpleVarray : IOracleCustomType, INullable
{
    [OracleArrayMapping()]
    public string[] Array;
    private bool m_bIsNull;
    public bool IsNull
    {
        get
        {
            return m_bIsNull;
        }
    }
    public static SimpleVarray Null
    {
        get
        {
            SimpleVarray obj = new SimpleVarray();
            obj.m_bIsNull = true;
            return obj;
        }
    }
    public void ToCustomObject(OracleConnection con, IntPtr pUdt)
    {
        object objectStatusArray = null;
        Array = (string[])OracleUdt.GetValue(con, pUdt, 0, out objectStatusArray);
    }
    public void FromCustomObject(OracleConnection con, IntPtr pUdt)
    {
        OracleUdt.SetValue(con, pUdt, 0, Array);
    }
}

[OracleCustomTypeMapping("EEP.TESTVARRAY")]
public class SimpleVarrayFactory : IOracleCustomTypeFactory, IOracleArrayTypeFactory
{
    public IOracleCustomType CreateObject()
    {
        return new SimpleVarray();
    }
    public Array CreateArray(int numElems)
    {
        return new string[numElems];
    }
    public Array CreateStatusArray(int numElems)
    {
        return null;
    }
}

C#----main

private static void OrderReport()
{  
    SimpleVarray test = new SimpleVarray();
    test.Array = new string[]
    {
        "A","B","C","D", "E","F","G","H",DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")
    };
    String ConnectionString = ConfigurationManager.ConnectionStrings["ABC"].ConnectionString;
    using (OracleConnection conn = new OracleConnection(ConnectionString))
    {
        conn.Open();
        using (OracleCommand cmd = conn.CreateCommand())
        {
            try
            {
                cmd.CommandText = "TEST";
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("V_List", OracleDbType.Array).Direction = ParameterDirection.Input;
                cmd.Parameters[0].Value = test;
                cmd.Parameters[0].UdtTypeName = "TESTVARRAY";
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            Console.WriteLine("1");
            Console.ReadLine();
        }
    }
}

ORACLE TYPE---

TYPE "TESTVARRAY" AS VARRAY(3000) OF VARCHAR(30)

ORACLE STORED PROCEDURE---

PROCEDURE TEST(V_List IN TESTVARRAY) AS
    BEGIN
        INSERT INTO AAAA (A, B,C, D, E, F, G, H, DATETIME) VALUES(V_List(1), V_List(2), V_List(3), V_List(4), V_List(5), V_List(6), V_List(7), V_List(8), to_timestamp(V_List(9), 'yyyy-mm-dd hh24:mi:ss.ff3'));
    END;

C# DTO n Oracle UDTs

參考網址：
http://www.codeproject.com/Articles/141728/Interaction-between-C-Application-and-Oracle-throu
C# DTO

class PersonDto : IOracleCustomType
    {
    [OracleObjectMappingAttribute("PNAME")]
    public virtual string Name { get; set; }
    [OracleObjectMappingAttribute("ADDRESS")]
    public virtual string Address { get; set; }
    [OracleObjectMappingAttribute("AGE")]
    public virtual decimal Age { get; set; }
    public virtual void FromCustomObject(OracleConnection objCon, IntPtr objUdt)
    {
        OracleUdt.SetValue(objCon, objUdt, "PNAME", this.Name);
        OracleUdt.SetValue(objCon, objUdt, "ADDRESS", this.Address);
        if (this.Age > 0) OracleUdt.SetValue(objCon, objUdt, "AGE", this.Age);
    }
    public virtual void ToCustomObject(OracleConnection objCon, IntPtr objUdt)
    {
        this.Name = ((string)(OracleUdt.GetValue(objCon, objUdt, "PNAME")));
        this.Address = ((string)(OracleUdt.GetValue(objCon, objUdt, "ADDRESS")));
        bool AgeIsNull = OracleUdt.IsDBNull(objCon, objUdt, "AGE");
        if ((AgeIsNull == false)) this.Age =
            ((decimal)(OracleUdt.GetValue(objCon, objUdt, "AGE")));
    }
    [OracleCustomTypeMappingAttribute("PERSON")]
    public class PersonFactory : IOracleCustomTypeFactory
    {
        public virtual IOracleCustomType CreateObject()
        {
            PersonDto obj = new PersonDto();
            return obj;
        }
    }
}