public string ObtenerValor(int pcod_cliente_N)
{
List <OracleParameter> lst = new List <OracleParameter>();
string res = string.Empty;
try
{
// add parameters
OracleParameter param;
param = new OracleParameter("pcod_cliente_n", OracleDbType.Int32);
param.Value = 10;
lst.Add(param);
OracleClob data = (OracleClob)MyOracleUtils.execOracleSf2("pckTest.ObtValor3", lst, OracleDbType.Clob, this.conn);
res = (string)data.Value;
}
catch (Exception)
{
throw;
}
return(res);
}
public OracleDataReader getParametros()
{
string sql = "Select DES_PARAMETRO as \"Proceso\", VAL_PARAMETRO as \"Fec.Referencia\" " + Environment.NewLine +
" from [email protected] a " + Environment.NewLine +
" where A.COD_PARAMETRO_V in ('APLOPERT', 'CIERRECONT','UFCARPRE')";
OracleDataReader dr = MyOracleUtils.executeSqlStm(sql, ConnGl.Instance.Conn);
return(dr);
}
public DataSet GetTiposSpsRetorno()
{
DataSet res = null;
string sql = "SELECT " + Environment.NewLine +
"cod_tipretorno_n as codigo, " + Environment.NewLine +
"des_tipretorno as descripcion " + Environment.NewLine +
"FROM ge_ambtipretorno";
res = MyOracleUtils.executeSqlStmDs(sql, this.conn);
return(res);
}
public OracleDataReader getCatalogoEntries(string pcond)
{
OracleDataReader res = null;
string sql = "SELECT * FROM GE_AMBCATALOGO ";
sql = sql + (pcond.Length > 0 ? " WHERE " : string.Empty) + pcond;
res = MyOracleUtils.executeSqlStm(sql, this.conn);
return(res);
}
private OracleDataReader getOraMetaParamsSp(string packageName, string methodName, string schemaDb)
{
OracleDataReader res = null;
string sql = "SELECT A.package_name , A.object_name ,A.argument_name, A.data_type, A.in_out " + Environment.NewLine +
"FROM all_arguments A " + Environment.NewLine +
"WHERE package_name = " + MyStringUtils.entreComas(packageName.ToUpper()) + Environment.NewLine +
"AND object_name = " + MyStringUtils.entreComas(methodName.ToUpper()) + Environment.NewLine +
"AND OWNER = " + MyStringUtils.entreComas(schemaDb).Trim() + Environment.NewLine +
" ORDER BY POSITION";
res = MyOracleUtils.executeSqlStm(sql, this.conn);
return(res);
}
public OracleDataReader getBitacoraEntries(string pcond)
{
OracleDataReader res = null;
string sql = "select a.fec_registro, a.nom_servicio, A.DES_MENSAJE, A.DES_TRANSACCION, a.fec_inicio, a.fec_fin, " +
" a.des_ipservidor, a.des_ipcliente, A.COD_DOMINIO_V, A.COD_USUARIO_V " +
" from GE_AMBBITACORA A " +
(pcond.Length > 0 ? " WHERE " : string.Empty) + pcond +
" order by fec_inicio desc";
System.Console.WriteLine(sql);
res = MyOracleUtils.executeSqlStm(sql, this.conn);
return(res);
}
public DataSet GetCodModulo()
{
DataSet res = null;
try
{
string sql = "select reg.cod_modulo_n as Codigo, 'Modulo ' || to_char (cod_modulo_n) as descripcion " + Environment.NewLine +
" from (select distinct cod_modulo_n from ge_ambcatalogo) reg";
res = MyOracleUtils.executeSqlStmDs(sql, this.conn);
}
catch (Exception)
{
throw;
}
return(res);
}
public OracleDataReader getCierres()
{
string sql = "SELECT -- sis.DES_SISTEMA as \"Sistema\", " + Environment.NewLine +
"sis.des_subsist as \"Sub Sistema\", " + Environment.NewLine +
"cie.FEC_ULTCIER as \"Fec.Ult.Cierre\", " + Environment.NewLine +
"cie.FEC_PROXHAB as \"Fec.Sistema\", " + Environment.NewLine +
"cie.FEC_MODESTA as \"Fec.Realiza Cierre\", " + Environment.NewLine +
"cie.USU_MODESTA as \"Usu.Realiza\" " + Environment.NewLine +
"FROM [email protected] cie, " + Environment.NewLine +
" SE_SISTEMAS @PRDPBBN04.CONSULTA sis " + Environment.NewLine +
"where cie.COD_SISTEMA_V = sis.COD_SISTEMA_V AND " + Environment.NewLine +
"cie.COD_SUBSIST_V = sis.COD_SUBSIST_V";
OracleDataReader dr = MyOracleUtils.executeSqlStm(sql, ConnGl.Instance.Conn);
return(dr);
}
public OracleDataReader getCtlHorariosSabNet()
{
string sql = "SELECT A.COD_PROCESOCTL_V as \"Nom.Proceso\", " + Environment.NewLine +
" A.FEC_ULTEJEC as \"Fec.Ult.Ejecución\", " + Environment.NewLine +
" A.USU_CORPROC as \"Usu.Ejecutó\" " + Environment.NewLine +
" FROM [email protected] A " + Environment.NewLine +
" WHERE A.COD_PROCESOCTL_V IN ('VENOPER', 'REPVENOPE', 'VENCUST', 'REPVENC') " + Environment.NewLine +
" UNION ALL " + Environment.NewLine +
" SELECT 'Cierre Divsas' as \"Nom.Proceso\", " + Environment.NewLine +
" a.FEC_ULTEJEC, a.USU_CORPROC " + Environment.NewLine +
" FROM [email protected] a " + Environment.NewLine +
"WHERE(a.cod_sistema_v, a.cod_subsist_v, a.cod_proceso) " + Environment.NewLine +
" in (('OR', 'OR', 'CIERREDIV'))";
OracleDataReader dr = MyOracleUtils.executeSqlStm(sql, ConnGl.Instance.Conn);
return(dr);
}
private void ExecQrytlsStrp_Click(object sender, EventArgs e)
{
string sql;
try
{
sql = txtQry.Text;
OracleDataReader dr = MyOracleUtils.executeSqlStm(sql, ConnGl.Instance.Conn);
DataTable dt = new DataTable();
dt.Load(dr);
dgrData.DataSource = dt;
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
/// <summary>
///
//FUNCTION get_ddl (
// object_type IN VARCHAR2,
// name IN VARCHAR2,
// schema IN VARCHAR2 DEFAULT NULL,
// version IN VARCHAR2 DEFAULT 'COMPATIBLE',
// model IN VARCHAR2 DEFAULT 'ORACLE',
// transform IN VARCHAR2 DEFAULT 'DDL')
// RETURN CLOB;
/// </summary>
/// <param name="pOwner"></param>
/// <param name="pType"></param>
/// <param name="pObjectName"></param>
/// <returns></returns>
public string GetDll(string pSchema, string pObjectType, string pObjectName)
{
string res = string.Empty;
string wObjType = pObjectType.Replace(' ', '_');
string wsql = "select dbms_metadata.get_ddl( OBJECT_TYPE => " + MyStringUtils.entreComas(wObjType) +
", NAME =>" + MyStringUtils.entreComas(pObjectName) +
", SCHEMA => " + MyStringUtils.entreComas(pSchema) +
" ) as res from dual";
OracleDataReader dr = MyOracleUtils.executeSqlStm(wsql, ConnGl.Instance.Conn);
while (dr.Read())
{
res = (Convert.ToString(dr["RES"]));
}
return(res);
}
public DataSet GetTiposServicios()
{
DataSet res = null;
try
{
string sql = "SELECT cod_tipservicio_n as codigo, " + Environment.NewLine +
"des_tipservicio as descripcion " + Environment.NewLine +
"FROM ge_ambtipservicio";
res = MyOracleUtils.executeSqlStmDs(sql, this.conn);
}
catch (Exception)
{
throw;
}
return(res);
}
public DataSet GetAccionServicio()
{
DataSet res = null;
try
{
string sql = "SELECT cod_accserv_n as codigo, " + Environment.NewLine +
" des_accserv as descripcion " + Environment.NewLine +
" FROM ge_ambaccserv " + Environment.NewLine +
" ORDER BY DES_ACCSERV";
res = MyOracleUtils.executeSqlStmDs(sql, this.conn);
}
catch (Exception)
{
throw;
}
return(res);
}
public void CargarDatos(int pcod_cliente_N)
{
List <OracleParameter> lst = new List <OracleParameter>();
string res = string.Empty;
try
{
OracleParameter param = new OracleParameter("pcod_cliente_n", pcod_cliente_N);
param.Value = 10;
lst.Add(param);
MyOracleUtils.execOracleSp2("pcktest.CargarDatos", lst, this.conn);
}
catch (Exception)
{
throw;
}
}
public DataSet GetObjetoProp()
{
DataSet res = null;
try
{
string sql = "SELECT cod_objprop_n as codigo, " + Environment.NewLine +
"des_objprop || ' - ' || des_objdb as descripcion " + Environment.NewLine +
"FROM ge_ambobjprop " + Environment.NewLine +
"ORDER BY DES_OBJPROP";
res = MyOracleUtils.executeSqlStmDs(sql, this.conn);
}
catch (Exception)
{
throw;
}
return(res);
}
public DataSet GetSqlTblMetaData(string pOwner, string pTableName,
string pVarRow, string pVarEntiName)
{
DataSet ds = null;
string sql;
try
{
List <OracleParameter> lst = new List <OracleParameter>();
OracleParameter param = new OracleParameter("POWNER", OracleDbType.Varchar2, 100);
param.Value = pOwner;
lst.Add(param);
param = new OracleParameter("pTableName", OracleDbType.Varchar2, 100);
param.Value = pTableName;
lst.Add(param);
param = new OracleParameter("pVarRow", OracleDbType.Varchar2, 100);
param.Value = pVarRow;
lst.Add(param);
param = new OracleParameter("pVarEntityName", OracleDbType.Varchar2, 500);
param.Value = pVarEntiName;
lst.Add(param);
param = new OracleParameter("pcur", OracleDbType.RefCursor, 100);
param.Direction = ParameterDirection.Output;
lst.Add(param);
OracleCommand oraCmd = MyOracleUtils.execOracleSp2(" ME_pORAMETADATA.ObtieneOracleMeta", lst, this.conn);
//((OracleRefCursor)p1.Value).GetDataReader()
OracleDataReader dr = ((OracleRefCursor )(oraCmd.Parameters["pcur"].Value)).GetDataReader();
DataTable dt = new DataTable();
dt.Load(dr);
ds = new DataSet();
ds.Tables.Add(dt);
}
catch (Exception)
{
throw;
}
return(ds);
}
public string ObtDiaSemana(DateTime pdate)
{
const string LexFmtDate = "<%fecFormyyyy-MM-dd>";
string res = string.Empty;
string sql = "SELECT '" + LexFmtDate + "' as fec, TO_CHAR(date '"
+ LexFmtDate + "', 'DAY') diaSemana" + Environment.NewLine +
"FROM dual";
string scrap = pdate.ToString("yyyy-MM-dd", CultureInfo.InvariantCulture);
sql = sql.Replace(LexFmtDate, scrap);
DataSet ds = MyOracleUtils.executeSqlStmDs(sql, this.conn);
foreach (DataRow dr in ds.Tables[0].Rows)
{
res = Convert.ToString(dr["diaSemana"]);
}
return(res);
}
public DataSet GetDependencies(DbObjectBE pDbObjectBE)
{
DataSet res = null;
string wsql = "select a.referenced_owner, a.referenced_type, a.referenced_name " + Environment.NewLine +
"from all_dependencies a " + Environment.NewLine +
" where a.owner = " + MyStringUtils.entreComas(pDbObjectBE.Owner) + Environment.NewLine +
" and a.type = " + MyStringUtils.entreComas(pDbObjectBE.Type) + Environment.NewLine +
" and a.name = " + MyStringUtils.entreComas(pDbObjectBE.Name) + Environment.NewLine;
try
{
res = MyOracleUtils.executeSqlStmDs(wsql, this.conn);
}
catch (Exception)
{
throw;
}
return(res);
}
string ObtServiceInvoker(string pServiceName)
{
DataSet ds = null;
string res = string.Empty;
string wSql = string.Empty;
try
{
wSql = "SELECT A.DES_INVOCADOR " + Environment.NewLine +
" FROM GE_AMBCATALOGO A " + Environment.NewLine +
" WHERE A.NOM_SERVICIO = " + MyStringUtils.entreComas(pServiceName);
ds = MyOracleUtils.executeSqlStmDs(wSql, this.conn);
foreach (DataRow dr in ds.Tables[0].Rows)
{
res = Convert.ToString(dr["DES_INVOCADOR"]);
}
}
catch (Exception)
{
throw;
}
return(res);
}
public string ObtCallCSharp(string pSerViceName,
string pServiceRequest,
string pEntityName,
int pIndentityParamN,
string pDbOwner)
{
string res = string.Empty;
string invoker = string.Empty;
string packageName = string.Empty;
string methodName = string.Empty;
string[] scrap;
object objRes;
try
{
invoker = ObtServiceInvoker(pSerViceName);
if (invoker.Length > 0)
{
scrap = invoker.Split('.');
packageName = scrap[0];
methodName = scrap[1];
List <OracleParameter> lst = new List <OracleParameter>();
OracleParameter param = new OracleParameter("PPACKAGENAME", OracleDbType.Varchar2, 100);
param.Value = packageName;
lst.Add(param);
param = new OracleParameter("PSPPROCNAME", OracleDbType.Varchar2, 100);
param.Value = methodName;
lst.Add(param);
param = new OracleParameter("PSERVICEREQUESTID", OracleDbType.Varchar2, 100);
param.Value = string.Empty;
lst.Add(param);
param = new OracleParameter("PSERVICENAME", OracleDbType.Varchar2, 500);
param.Value = pSerViceName;
lst.Add(param);
param = new OracleParameter("PENTITYNAME", OracleDbType.Varchar2, 100);
param.Value = pEntityName;
lst.Add(param);
param = new OracleParameter("PINDENTITYPARAM_N", OracleDbType.Int32, 100);
param.Value = pIndentityParamN;
lst.Add(param);
param = new OracleParameter("POWNER", OracleDbType.Varchar2, 100);
param.Value = pDbOwner;
lst.Add(param);
objRes = MyOracleUtils.execOracleSf2("GE_PAMBCSHARPGEN.getCallService", lst, OracleDbType.Clob, this.conn);
res = ((OracleClob)objRes).Value;
}
}
catch (Exception)
{
throw;
}
return(res);
}
private void SendSqlToDataView(string sql, DataGridView dgrView)
{
OracleDataReader dr = MyOracleUtils.executeSqlStm(sql, ConnGl.Instance.Conn);
SendDataReaderToDataView(dr, dgrView);
}
