Guest User

Untitled

a guest
Apr 26th, 2018
84
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.59 KB | None | 0 0
  1. using System;
  2. using System.Collections;
  3. using System.Data;
  4. using Oracle.DataAccess.Client;
  5.  
  6. class Program
  7. {
  8. private const string _db = "<db>";
  9. private const string _username = "<user>";
  10. private const string _password = "<password>";
  11. private const string _storedProcedureName = "<sproc>";
  12.  
  13. static void Main(string[] args)
  14. {
  15. var connectionString = string.Format(
  16. "data source={0};user id={1};password={2}",
  17. _db, _username, _password);
  18.  
  19. var connection = new OracleConnection(connectionString);
  20.  
  21. try
  22. {
  23.  
  24. connection.Open();
  25.  
  26.  
  27. var timeStamps = new[] { DateTime.Now, DateTime.Now };
  28.  
  29. var parameter = new OracleParameter("inTimeStamps", OracleDbType.TimeStamp)
  30. {
  31. Direction = ParameterDirection.Input,
  32. CollectionType = OracleCollectionType.PLSQLAssociativeArray,
  33. Size = timeStamps.Length,
  34. Value = timeStamps
  35. };
  36.  
  37. var command = connection.CreateCommand();
  38. command.CommandType = CommandType.StoredProcedure;
  39. command.CommandText = _storedProcedureName;
  40. command.Parameters.Add(parameter);
  41.  
  42. command.ExecuteReader();
  43.  
  44. }
  45. finally
  46. {
  47. connection.Close();
  48. }
  49. }
  50. }
  51.  
  52. TYPE ArrayOfTimestamps is table of timestamp index by binary_integer;
  53.  
  54. PROCEDURE TestOdpTimeStamp (inTimeStamps in ArrayOfTimestamps)
  55. IS
  56. test number;
  57. BEGIN
  58. select 1 into test from dual;
  59. END;
  60.  
  61. create or replace type MyTimeStamp as object
  62. (
  63. my timestamp
  64. )
  65. /
  66.  
  67. create or replace type mytimestamp_table as table of MyTimeStamp
  68. /
  69.  
  70. create table testinserttimestamp
  71. ( my timestamp);
  72.  
  73. create or replace procedure test_timestamp_table (p_in in mytimestamp_table)
  74. is
  75. begin
  76. for i in p_in.first..p_in.last loop
  77. insert into testinserttimestamp values (p_in(i).my);
  78. end loop;
  79. commit;
  80. end;
  81.  
  82. using System;
  83. using System.Data;
  84. using System.Windows.Forms;
  85. using Oracle.DataAccess.Client;
  86. using Oracle.DataAccess.Types;
  87.  
  88. namespace TestTimeStamp
  89. {
  90. public partial class Form1 : Form
  91. {
  92. public Form1()
  93. {
  94. InitializeComponent();
  95. }
  96.  
  97.  
  98. public class MyUdtTimeStamp : INullable, IOracleCustomType
  99. {
  100.  
  101. [OracleObjectMappingAttribute("MY")]
  102. public OracleTimeStamp My { get; set; }
  103.  
  104. public bool IsNull
  105. {
  106. get { return false;}
  107. }
  108.  
  109. public void FromCustomObject(OracleConnection con, IntPtr pUdt)
  110. {
  111. OracleUdt.SetValue(con, pUdt, "MY", My);
  112. }
  113.  
  114. public void ToCustomObject(OracleConnection con, IntPtr pUdt)
  115. {
  116. My = (OracleTimeStamp)OracleUdt.GetValue(con, pUdt, "MY");
  117. }
  118. }
  119.  
  120. [OracleCustomTypeMappingAttribute("TESTTS.MYTIMESTAMP")]
  121. public class StudentFactory : IOracleCustomTypeFactory
  122. {
  123. public IOracleCustomType CreateObject()
  124. {
  125. return new MyUdtTimeStamp();
  126. }
  127. }
  128.  
  129. [OracleCustomTypeMappingAttribute("TESTTS.MYTIMESTAMP_TABLE")]
  130. public class PersonArrayFactory : IOracleArrayTypeFactory
  131. {
  132. public Array CreateArray(int numElems)
  133. {
  134. return new MyUdtTimeStamp[numElems];
  135. }
  136.  
  137. public Array CreateStatusArray(int numElems)
  138. {
  139. return null;
  140. }
  141. }
  142.  
  143. private void button1_Click(object sender, EventArgs e)
  144. {
  145. OracleConnectionStringBuilder b = new OracleConnectionStringBuilder();
  146. b.UserID = "testts";
  147. b.Password = "ts";
  148. b.DataSource = "ora11";
  149. using (OracleConnection conn = new OracleConnection(b.ToString())) {
  150. conn.Open();
  151. using (OracleCommand comm = conn.CreateCommand())
  152. {
  153. comm.CommandText = "begin test_timestamp_table(:1); end;";
  154. OracleParameter p = new OracleParameter();
  155. p.OracleDbType = OracleDbType.Array;
  156. p.Direction = ParameterDirection.Input;
  157.  
  158. p.UdtTypeName = "TESTTS.MYTIMESTAMP_TABLE";
  159. MyUdtTimeStamp[] times = new MyUdtTimeStamp[2];
  160. MyUdtTimeStamp m1 = new MyUdtTimeStamp();
  161. m1.My = new OracleTimeStamp(DateTime.Now);
  162. MyUdtTimeStamp m2 = new MyUdtTimeStamp();
  163. m2.My = new OracleTimeStamp(DateTime.Now);
  164. times[0] = m1;
  165. times[1] = m2;
  166. p.Value = times;
  167.  
  168. comm.Parameters.Add(p);
  169.  
  170. comm.ExecuteNonQuery();
  171. }
  172.  
  173. conn.Close();
  174. }
  175. }
  176. }
  177. }
  178.  
  179. SQL> select * from testinserttimestamp;
  180.  
  181. MY
  182. -------------------------------------------------
  183. 12-10-09 21:13:54,328125
  184. 12-10-09 21:13:55,171875
  185.  
  186. comm.CommandText = "declare "+
  187. "theTS mytimestamp_table;"+
  188. "begin"+
  189. " theTS(1):= :1;"+
  190. " theTS(2):= :2;"+
  191. " test_timestamp_table(theTS);"+
  192. " end;";
Add Comment
Please, Sign In to add comment