C sharp - procedimiento oracle y C# con parametros

 
Vista:

procedimiento oracle y C# con parametros

Publicado por Stella (2 intervenciones) el 07/03/2011 03:10:35
Ayuda por favor:

Tengo el siguiente error y el procedimiento si me funciona en oracle.

ORA-06550: line 1, column 7:
PLS-00306: wrong number or types of arguments in call to 'HONORARIONOC'
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Mi código en C# es:

public DataSet InformeMensual(string TipoHonorario)
{
string[] nomParam =
{
"In_TipoHonorario"
};

object[] vlrParam =
{
TipoHonorario
};
return getDataSet("HonorarioNoc_PKG.HonorarioNoc", nomParam, vlrParam);
}





public DataSet getDataset(string strSQL, string[] NombreParametros, object[] ValoresParametros)
{
DataSet ds = new DataSet();

try
{
if (strTipo_Conexion == TipoCnn.Oracle)
{
cmdo = cnno.CreateCommand();
cmdo.CommandText = strSQL;
cmdo.CommandType = CommandType.StoredProcedure;
this.setParametrosOracle(cmdo, getParamertosOracle(NombreParametros, ValoresParametros));
OracleDataAdapter da = new OracleDataAdapter(cmdo);
da.Fill(ds);
}
else
{
cmd = cnn.CreateCommand();
cmd.CommandText = strSQL;
cmd.CommandTimeout = 99999999;
cmd.CommandType = CommandType.StoredProcedure;
this.setParametros(cmd, getParamertos(NombreParametros, ValoresParametros));
SqlDataAdapter da = new SqlDataAdapter(cmd);
da.Fill(ds);
}


return ds;
}
catch (Exception ex)
{
EscribirError(ex.ToString());
return (null);
}
}





private OracleParameter[] getParamertosOracle(string[] NombreParametros, object[] ValoresParametros)
{
OracleParameter[] arrParametros = new OracleParameter[NombreParametros.Length];

for (int ic = 0; ic <= NombreParametros.Length - 1; ic++)
{
OracleParameter Parametro = new OracleParameter();
Parametro.ParameterName = NombreParametros[ic];
Parametro.Value = ValoresParametros[ic];
arrParametros[ic] = Parametro;
Parametro = null;
}
return arrParametros;
}





private void setParametrosOracle(OracleCommand setCmd, OracleParameter[] setParameter)
{
if (setParameter != null)
{
for (int ic = 0; ic <= setParameter.Length - 1; ic++)
{
setCmd.Parameters.Add(setParameter[ic]);
}

}

}


EL PROCEDIMIENTO EN ORACLE ES:

CREATE OR REPLACE PACKAGE Body ADMSALUD.HonorarioNoc_PKG AS
TYPE mes_arr IS TABLE OF VARchar2(10)
INDEX BY BINARY_INTEGER;
TYPE NOrden_arr IS TABLE OF VARCHAR2(10)
INDEX BY BINARY_INTEGER;
TYPE FecCirugia_arr IS TABLE OF date
INDEX BY BINARY_INTEGER;
TYPE HoraCirugia_arr IS TABLE OF VARCHAR2(5)
INDEX BY BINARY_INTEGER;
TYPE IdPaciente_arr IS TABLE OF VARCHAR2(13)
INDEX BY BINARY_INTEGER;
TYPE Paciente_arr IS TABLE OF VARCHAR2(83)
INDEX BY BINARY_INTEGER;
TYPE IdCirugia_arr IS TABLE OF VARCHAR2(13)
INDEX BY BINARY_INTEGER;
TYPE Cirujano_arr IS TABLE OF VARCHAR2(83)
INDEX BY BINARY_INTEGER;
TYPE CodPresta_arr IS TABLE OF VARCHAR2(8)
INDEX BY BINARY_INTEGER;
TYPE NPrestacion_arr IS TABLE OF VARCHAR2(255)
INDEX BY BINARY_INTEGER;
TYPE PHonorario_arr IS TABLE OF VARCHAR2(7)
INDEX BY BINARY_INTEGER;
TYPE Monto_arr IS TABLE OF NUMBER(11,2)
INDEX BY BINARY_INTEGER;
TYPE Sociedad_arr IS TABLE OF VARCHAR2(4)
INDEX BY BINARY_INTEGER;
TYPE HoraIni_arr IS TABLE OF VARCHAR2(5)
INDEX BY BINARY_INTEGER;
TYPE HoraFin_arr IS TABLE OF VARCHAR2(2)
INDEX BY BINARY_INTEGER;
TYPE HoraCompFin_arr IS TABLE OF VARCHAR2(5)
INDEX BY BINARY_INTEGER;
TYPE Servicio_arr IS TABLE OF VARCHAR2(8)
INDEX BY BINARY_INTEGER;

PROCEDURE HonorarioNoc(
in_tipohonorario IN VARCHAR2,
in_fechaini IN VARCHAR2,
in_fechafin IN VARCHAR2,
mes OUT mes_arr,
norden OUT norden_arr,
feccirugia OUT feccirugia_arr,
horacirugia OUT horacirugia_arr,
idpaciente OUT idpaciente_arr,
paciente OUT paciente_arr,
idcirugia OUT idcirugia_arr,
cirujano OUT cirujano_arr,
codpresta OUT codpresta_arr,
nprestacion OUT nprestacion_arr,
phonorario OUT phonorario_arr,
monto OUT monto_arr,
sociedad OUT sociedad_arr,
horaini OUT horaini_arr,
horafin OUT horafin_arr,
horacompfin OUT horacompfin_arr,
servicio OUT servicio_arr,
srv_message OUT VARCHAR2
)
AS
srv_fetchstatus NUMBER (1);
srv_totalrows NUMBER (8);
srv_rowcount NUMBER (8);


CURSOR regrecursor
IS
SELECT nombremes (EXTRACT (MONTH FROM a.fecqrf)) AS mes,
a.ordnumero AS norden, a.fecqrf AS feccirugia,
a.horqrf AS horacirugia, e.pac_pac_rut AS idpaciente,
RTRIM (e.pac_pac_apellpater)
|| ' '
|| RTRIM (e.pac_pac_apellmater)
|| ' '
|| RTRIM (e.pac_pac_nombre) AS paciente,
b.anesid AS idcirugia,
RTRIM (f.ser_pro_apellpater)
|| ' '
|| RTRIM (f.ser_pro_apellmater)
|| ' '
|| RTRIM (f.ser_pro_nombres) AS cirujano,
c.pre_pre_codigo AS codpresta,
h.pre_pre_descripcio AS nprestacion,
c.porhonpor AS phonorario, c.ate_pre_montopagar AS monto,
c.soccod AS sociedad, d.horaini AS horaini,
SUBSTR (d.horafin, 1, 2) AS horafin,
d.horafin AS horacompfin, a.ser_obj_codigo AS servicio
FROM tabprgqrf a,
tabprdrel b,
tabprdreldet c,
taborddetcir d,
pac_paciente e,
ser_profesiona f,
pre_prestacion h,
ate_prestacion k
WHERE a.ordnumero = b.ordnumero
AND a.ordnumero = c.ordnumero
AND b.ordnumero = k.ordnumero
AND b.pre_pre_codigo = k.ate_pre_codigo
AND c.cnpqrgcod = in_tipohonorario
AND b.pre_pre_codigo = c.pre_pre_codigo
AND a.ordnumero = d.ordnumero
AND a.pac_pac_numero = e.pac_pac_numero
AND b.ser_pro_rut = f.ser_pro_rut
AND h.pre_pre_codigo = c.pre_pre_codigo
AND TRUNC (a.fecqrf) BETWEEN TO_DATE (in_fechaini, 'yyyy/mm/dd')
AND TO_DATE (in_fechafin, 'yyyy/mm/dd')
AND ( d.horaini BETWEEN ('19:00') AND ('23:59')
OR d.horaini BETWEEN ('00:00') AND ('05:59')
)
AND ( d.horafin BETWEEN ('19:00') AND ('23:59')
OR d.horafin BETWEEN ('00:00') AND ('05:59')
);
BEGIN
srv_totalrows := TO_NUMBER (RTRIM (srv_message));
srv_rowcount := 0;
srv_fetchstatus := 0;
srv_message := '1000000';

/*-----------------------------------------------------------------*/
/*------------------------ Begin User Code ------------------------*/
/*-----------------------------------------------------------------*/
BEGIN
IF NOT regrecursor%ISOPEN
THEN
OPEN regrecursor;
END IF;



LOOP

FETCH regrecursor
INTO mes (srv_rowcount + 1),
norden (srv_rowcount + 1),
feccirugia (srv_rowcount + 1),
horacirugia (srv_rowcount + 1),
idpaciente (srv_rowcount + 1),
paciente (srv_rowcount + 1),
idcirugia (srv_rowcount + 1),
cirujano (srv_rowcount + 1),
codpresta (srv_rowcount + 1),
nprestacion (srv_rowcount + 1),
phonorario (srv_rowcount + 1),
monto (srv_rowcount + 1),
sociedad (srv_rowcount + 1),
horaini (srv_rowcount + 1),
horafin (srv_rowcount + 1),
horacompfin (srv_rowcount + 1),
servicio (srv_rowcount + 1);



exit when regrecursor%notfound;

/* IF regrecursor%NOTFOUND
THEN
CLOSE regrecursor;

EXIT;
END IF;*/

srv_rowcount := srv_rowcount + 1;
srv_fetchstatus := 1;
END LOOP;

IF regrecursor%ISOPEN
THEN
CLOSE regrecursor;
END IF;

IF srv_rowcount > 0
THEN
srv_message :=
SUBSTR (srv_message, 1, 6) || '1'
|| SUBSTR (srv_message, 8);
END IF;
EXCEPTION
WHEN OTHERS
THEN
srv_message :=
SUBSTR (srv_message, 1, 1)
|| '38000'
|| srv_fetchstatus
|| 'Se produjo un ERROR';
END;

/*------------------------- End User Code -------------------------*/
/*-----------------------------------------------------------------*/
<<lespder_exit>>
NULL;


END HonorarioNoc;
end HonorarioNoc_PKG;
/
Valora esta pregunta
Me gusta: Está pregunta es útil y esta claraNo me gusta: Está pregunta no esta clara o no es útil
0
Responder