1
2 public static class Utility
3 {
4 private static Dictionary<string, ArgumentDescription[]> procedureArguments = new Dictionary<string, ArgumentDescription[]>();
5 private static System.Data.DataTable allArgumentsCache = null;
6
7 /// <summary>
8 /// Contains the field values returned by the Oracle system procedure DBMS_DESCRIBE.DESCRIBE_PROCEDURE.
9 /// </summary>
10 public class ArgumentDescription
11 {
12 public int Overload;
13 public int Position;
14 public int Level;
15 public string ArgumentName;
16 public int DataType;
17 public int DefaultValue;
18 public int InOut;
19 public int Length;
20 public int Precision;
21 public int Scale;
22 public int Radix;
23 public int Spare;
24 }
25
26 /// <summary>
27 /// Calls the Oracle system procedure DBMS_DESCRIBE.DESCRIBE_PROCEDURE to return an argument of a stored procedure.
28 /// </summary>
29 /// <param name="ownerName">The package owner.</param>
30 /// <param name="packageName">The package containing the procedure.</param>
31 /// <param name="procedureName">The stored procedure name.</param>
32 /// <param name="argumentName">The argument name.</param>
33 /// <param name="connection">An <see cref="OracleConnection"/> to the Oracle database.</param>
34 /// <returns>An <see cref="ArgumentDescription"/> representing the argument requested, or null of no matching argument is found.</returns>
35 public static ArgumentDescription GetProcedureArgument(string ownerName, string packageName, string procedureName, string argumentName, OracleConnection connection)
36 {
37 argumentName = argumentName.Trim().ToUpper();
38 foreach (ArgumentDescription argument in GetProcedureArguments(ownerName, packageName, procedureName, connection))
39 {
40 if (argument.ArgumentName.ToUpper() == argumentName)
41 return argument;
42 }
43 return null;
44 }
45
46 /// <summary>
47 /// Calls the Oracle system procedure DBMS_DESCRIBE.DESCRIBE_PROCEDURE to return the arguments of a stored procedure.
48 /// </summary>
49 /// <param name="ownerName">The package owner.</param>
50 /// <param name="packageName">The package containing the procedure.</param>
51 /// <param name="procedureName">The stored procedure name.</param>
52 /// <param name="connection">An <see cref="OracleConnection"/> to the Oracle database.</param>
53 /// <returns>An array of <see cref="ArgumentDescription"/>.</returns>
54 public static ArgumentDescription[] GetProcedureArguments(string ownerName, string packageName, string procedureName, OracleConnection connection)
55 {
56 ownerName = ownerName.Trim().ToUpper();
57 packageName = packageName.Trim().ToUpper();
58 procedureName = procedureName.Trim().ToUpper();
59
60 string objectName = procedureName;
61 if (!string.IsNullOrEmpty(packageName))
62 objectName = packageName + "." + procedureName;
63 OracleCommand cmd = null;
64 string sql = null;
65
66 // query ALL_ARGUMENTS table once and cache list of all arguments
67 if (allArgumentsCache == null)
68 {
69 allArgumentsCache = new System.Data.DataTable();
70 sql = "select owner, package_name, object_name, argument_name, position from sys.all_arguments WHERE owner='" + ownerName + "' ";
71 cmd = new OracleCommand(sql, connection);
72 OracleDataAdapter da = new OracleDataAdapter(cmd);
73 da.Fill(allArgumentsCache);
74 }
75
76 // determine number of arguments in procedure
77 sql = "owner='" + ownerName + "' and object_name='" + procedureName + "' ";
78 if (!string.IsNullOrEmpty(packageName))
79 sql += " and package_name='" + packageName + "' ";
80 int argumentCount = Convert.ToInt32(allArgumentsCache.Compute("count(argument_name)", sql));
81 int argumentCountPlusOne = argumentCount + 1; // in case procedure is actually a function that returns a value
82
83 if (!procedureArguments.ContainsKey(objectName))
84 {
85 cmd = new OracleCommand("DBMS_DESCRIBE.DESCRIBE_PROCEDURE", connection);
86 cmd.CommandType = System.Data.CommandType.StoredProcedure;
87 cmd.Parameters.Add("OBJECT_NAME", OracleDbType.Varchar2).Value = objectName;
88 cmd.Parameters.Add("RESERVED1", "");
89 cmd.Parameters.Add("RESERVED2", "");
90
91 int bindSize = 30;
92 int[] arrayBindSize = new int[argumentCountPlusOne];
93 for (int i = 0; i < argumentCountPlusOne; i++)
94 arrayBindSize[i] = bindSize;
95
96 // setup the OUT associative arrays
97 string[] paramnames = new string[12] { "OVERLOAD", "POSITION", "LEVEL", "ARGUMENT_NAME", "DATATYPE", "DEFAULT_VALUE", "IN_OUT", "LENGTH", "PRECISION", "SCALE", "RADIX", "SPARE" };
98 for (int i = 0; i < paramnames.Length; i++)
99 {
100 OracleParameter p = null;
101 if (paramnames[i] != "ARGUMENT_NAME")
102 p = new OracleParameter(paramnames[i], OracleDbType.Int32, argumentCountPlusOne, null, System.Data.ParameterDirection.Output);
103 else
104 {
105 // need to set sizes on varchar2 params
106 p = new OracleParameter("ARGUMENT_NAME", OracleDbType.Varchar2, argumentCountPlusOne, null, System.Data.ParameterDirection.Output);
107 p.ArrayBindSize = arrayBindSize;
108 }
109 p.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
110 cmd.Parameters.Add(p);
111 }
112
113 cmd.ExecuteNonQuery();
114
115 argumentCount = (cmd.Parameters[3].Value as Array).Length;
116 ArgumentDescription[] argument = new ArgumentDescription[argumentCount];
117 for (int i = 0; i < argumentCount; i++)
118 {
119 int j = 3;
120 argument[i] = new ArgumentDescription();
121 argument[i].Overload = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
122 argument[i].Position = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
123 argument[i].Level = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
124 argument[i].ArgumentName = Convert.ToString((cmd.Parameters[j++].Value as Array).GetValue(i));
125 argument[i].DataType = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
126 argument[i].DefaultValue = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
127 argument[i].InOut = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
128 argument[i].Length = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
129 argument[i].Precision = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
130 argument[i].Scale = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
131 argument[i].Radix = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
132 argument[i].Spare = Convert.ToInt32((cmd.Parameters[j++].Value as Array).GetValue(i));
133 }
134
135 procedureArguments.Add(objectName, argument);
136 }
137
138 return procedureArguments[objectName];
139 }
140 }