--根據SP的名字取SP的參數列表
SELECT argument_name, position - 1
FROM all_arguments
WHERE owner = 'MES1' AND object_name = 'QUERY'
ORDER BY sequence
/// <summary>
/// 根據SP的名字取SP的參數列表;
/// </summary>
/// <param name="strSPName"></param>
/// <returns></returns>
public static DataTable GetSPPataList(string strSPName)
{
DataTable dtTemp = new DataTable();
string strConString = System.Web.HttpContext.Current.Session["conString"].ToString();
string strSQL = "SELECT argument_name, position-1 " +
"FROM all_arguments " +
"WHERE owner = 'MES1' AND object_name = :object_name " +
"ORDER BY sequence ";
OracleParameter[] oraParas = new OracleParameter[]{
new OracleParameter(":object_name",strSPName.ToUpper())
};
dtTemp = DBHelper.OracleHelper.ExecuteDataTable(strConString, CommandType.Text, strSQL, oraParas);
return dtTemp;
}
public static System.Data.DataTable ExecuteDataTable(string connectionString, CommandType cmdType, string cmdText, params OracleParameter[] commandParameters)
{
//Create the command and connection
OracleCommand cmd = new OracleCommand();
OracleDataAdapter adp=new OracleDataAdapter();
OracleConnection conn = new OracleConnection(connectionString);
try
{
//Prepare the command to execute
PrepareCommand(adp,cmd, conn, null, cmdType, cmdText, commandParameters);
//Execute the query, stating that the connection should close when the resulting datareader has been read
DataTable dtTemp = new DataTable();
adp.Fill(dtTemp);
cmd.Parameters.Clear();
conn.Dispose();
return dtTemp;
}
catch
{
//If an error occurs close the connection as the reader will not be used and we expect it to close the connection
conn.Close();
throw;
}
}