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); }