public static DataTable SPListGet(strConnect conn)
{
string qry = "SELECT name FROM SYS.procedures order by name";
MsSQL sql = new MsSQL(conn);
using (DataSet ds = sql.Excute_Query(qry, ""))
{
return(ds.Tables[0]);
}
}
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 DBListGet(strConnect conn)
{
string qry = "SELECT NAME FROM SYS.databases ORDER BY NAME";
MsSQL sql = new MsSQL(conn);
sql.retryTimes_Connecting = 1;
sql.RetryTimes_Query = 1;
using (DataSet ds = sql.Excute_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 SPInfoGet(strConnect conn, string spname)
{
string qry = string.Format(@"
SELECT OBJECT_ID, P.name, parameter_id, P.system_type_id, T.name datatype
FROM SYS.all_parameters P
LEFT OUTER JOIN SYS.types T ON P.system_type_id = T.system_type_id AND P.USER_type_id = T.USER_type_id
where object_id =
(SELECT object_id FROM SYS.procedures WHERE name = '{0}')
order by parameter_id", spname);
MsSQL sql = new MsSQL(conn);
using (DataSet ds = sql.Excute_Query(qry, ""))
{
return(ds.Tables[0]);
}
}
public static DataTable TableInfoGet(strConnect conn, string tablename)
{
string qry = string.Format(@"
SELECT OBJECT_ID, P.name, column_id, P.system_type_id, T.name datatype
FROM SYS.columns p
LEFT OUTER JOIN SYS.types T ON P.system_type_id = T.system_type_id AND P.USER_type_id = T.USER_type_id
where object_id =
(SELECT object_id FROM SYS.tables WHERE name = '{0}')
ORDER BY column_id", tablename);
MsSQL sql = new MsSQL(conn);
using (DataSet ds = sql.Excute_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]);
}
}
/// <summary>
/// 객체를 생성 하면서 Connection String 만든다.
/// </summary>
/// <param name="strConn"></param>
public OracleDB(strConnect strConn)
{
Set_ConnectionString(strConn.strTNS, strConn.strID, strConn.strPass);
}
public void Set_ConnectionString(strConnect strCon)
{
Set_ConnectionString(strCon.strIP, strCon.strDataBase, strCon.strID, strCon.strPass);
}
public MsSQL(strConnect str, Function.Util.Log log = null)
{
_log = log;
Set_ConnectionString(str);
}
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);
}
public void Set_ConnectionString(strConnect strConn)
{
connectionString = Get_ConnectionString(strConn.strTNS, strConn.strID, strConn.strPass);
}
/// <summary>
/// 객체를 생성 하면서 Connection String 만든다.
/// </summary>
/// <param name="strConn"></param>
public OracleDB(strConnect strConn, Function.Util.Log log = null)
{
Log = log;
Set_ConnectionString(strConn);
}
