public static DataTable TableListGet(strConnect conn)
{
string qry = "SELECT Table_Name name FROM USER_TABLES order by Table_Name";
OracleDB sql = new OracleDB(conn);
using (DataSet ds = sql.dsExcute_Query(qry))
{
return(ds.Tables[0]);
}
}
public static DataTable SPListGet(strConnect conn)
{
string qry = @"SELECT OBJECT_NAME NAME
FROM USER_procedures
WHERE OBJECT_TYPE = 'PROCEDURE'
ORDER BY OBJECT_NAME";
OracleDB sql = new OracleDB(conn);
using (DataSet ds = sql.dsExcute_Query(qry))
{
return(ds.Tables[0]);
}
}
/// <summary>
/// 패키지 목록을 조회한다.
/// </summary>
/// <param name="conn"></param>
/// <returns></returns>
public static DataTable PackageListGet(strConnect conn)
{
string qry = string.Format(
@"SELECT DISTINCT OBJECT_NAME NAME
FROM USER_procedures
WHERE OBJECT_TYPE = 'PACKAGE'
ORDER BY OBJECT_NAME");
OracleDB sql = new OracleDB(conn);
using (DataSet ds = sql.dsExcute_Query(qry))
{
return(ds.Tables[0]);
}
}
public static DataTable TableInfoGet(strConnect conn, string tablename)
{
string qry = string.Format(@"
SELECT COLUMN_ID OBJECT_ID, COLUMN_NAME NAME, COLUMN_ID, '' SYSTEM_TYPE_ID, DATA_TYPE DATATYPE
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = '{0}'
ORDER BY OBJECT_ID", tablename);
OracleDB sql = new OracleDB(conn);
using (DataSet ds = sql.dsExcute_Query(qry))
{
return(ds.Tables[0]);
}
}
public static DataTable PackageProcedureListGet(strConnect conn, string packagename)
{
string qry = string.Format(
@"SELECT PROCEDURE_NAME name
FROM USER_procedures
WHERE OBJECT_TYPE = 'PACKAGE'
AND OBJECT_NAME = '{0}'
AND PROCEDURE_NAME IS NOT NULL
ORDER BY OBJECT_NAME, SUBPROGRAM_ID", packagename);
OracleDB sql = new OracleDB(conn);
using (DataSet ds = sql.dsExcute_Query(qry))
{
return(ds.Tables[0]);
}
}
public static DataTable PackageProcedureInfoGet(strConnect conn, string packagename)
{
string[] packname = packagename.Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries);
string qry = string.Format(
@"SELECT TEXT
FROM USER_SOURCE
WHERE NAME = '{0}'
AND TYPE ='PACKAGE'
ORDER BY LINE ", packname[0]);
OracleDB sql = new OracleDB(conn);
DataTable dt = new DataTable("info");
int iParm = 0;
int idx;
dt.Columns.Add("OBJECT_ID", typeof(System.String));
dt.Columns.Add("name", typeof(System.String));
dt.Columns.Add("parameter_id", typeof(System.String));
dt.Columns.Add("system_type_id", typeof(System.String));
dt.Columns.Add("datatype", typeof(System.String));
using (DataSet ds = sql.dsExcute_Query(qry))
{
foreach (DataRow dr in ds.Tables[0].Rows)
{
string param = string.Empty;
if (iParm == 0)
{ //프로시져 시작 확인
param = Fnc.obj2String(dr["text"]);
idx = param.IndexOf(packname[1]);
if (idx >= 0)
{
iParm = 1;
param = param.Substring(idx + packname[1].Length).Trim();
}
}
if (iParm == 1)
{ //파람시작 확인
if (param.Equals(string.Empty))
{
param = Fnc.obj2String(dr["text"]);
}
idx = param.IndexOf('(');
if (idx >= 0)
{
iParm = 2;
param = param.Substring(idx).Trim();
}
}
if (iParm == 2)
{ //파람확인
if (param.Equals(string.Empty))
{
param = Fnc.obj2String(dr["text"]);
}
idx = param.IndexOf(')');
if (idx >= 0)
{
param = param.Substring(0, idx).Trim();
iParm = 3;
}
}
//파람 추가
if ((iParm == 2) && !param.Equals(string.Empty))
{
param = param.Replace(",", "").Replace("(", "").Replace(")", "").Trim();
string[] str = param.Split(new string[] { " ", "\t" }, StringSplitOptions.RemoveEmptyEntries);
if (str.Length > 1)
{
DataRow r = dt.NewRow();
r["name"] = str[0];
r["datatype"] = str[1];
dt.Rows.Add(r);
}
}
if (iParm > 2)
{
break;
}
}
}
return(dt);
}