hello:
When I call a function of Oracle with parameters array object, oraDirect .NET shows me different errors. Why?
I send us script.
Thanks,
Susana
type EQA.t_reg_error is object (
codigo_error varchar2(10),
codigo_campo varchar2(10),
valor_campo varchar2(100),
vip varchar2(15)
)
type EQA.t_reg_bebe is object (
nombre varchar2 (40),
apellido_principal varchar2 (30),
apellido_secundario varchar2 (30),
fecha_nacimiento date,
sexo char (1),
nacimiento_futuro char (1)
)
type EQA.t_reg_operacion is object (
fecha_operacion date,
tipo_operacion char(1),
origen varchar2(10),
codigo_linea number(2),
codigo_accion number(5),
codigo_ocurrencia number(3),
codigo_plantilla number(2),
codigo_proveedor varchar2(15),
codigo_aa number(8),
email varchar2(50)
)
CREATE OR REPLACE TYPE EQA.T_TAB_BEBE AS
TABLE OF T_REG_BEBE
package body EQA.cop00004
as
function alta_modif_bebes (
reg_operacion in t_reg_operacion,
p_num_bebes in number,
tab_bebes in t_tab_bebe)
return t_reg_error
is
exception_error exception;
v_reg_error t_reg_error:=t_reg_error(null,null,null,null);
v_error mdp30110.reg_error;
v_codigo_fichero trabajo_cabecera.codigo_fichero%type;
v_numero_proceso resultado_procesos.numero_proceso%type;
v_estado resultado_procesos.estado%type;
v_nombre_fichero varchar2 (100);
begin
/*
...
*/
return v_reg_error;
exception
when exception_error
then
insert into historico_rechazos_online
(codigo_proceso,
codigo_rechazo,
codigo_campo,
valor_campo,
fecha_rechazo,
codigo_consumidora,
codigo_proveedor)
values ('ALTA_BEBE',
v_reg_error.codigo_error,
v_reg_error.codigo_campo,
v_reg_error.valor_campo,
sysdate,
reg_operacion.codigo_aa,
reg_operacion.codigo_proveedor);
commit;
return v_reg_error;
end;
end cop00004;
--------------------------------------------------------------------------------------
_
Public Function AltaModifBebes(ByVal reg_operacion As t_reg_operacion, ByVal p_num_bebes As Integer, ByVal tab_bebes() As t_reg_bebe) As t_reg_error
Dim oCon As New OracleConnection
Dim oCommand As New OracleCommand
Dim oResult As t_reg_error
oCon.ConnectionString = System.Configuration.ConfigurationSettings.AppSettings("ConnectionString").ToString()
Try
oCon.Open()
oCommand.Connection = oCon
oCommand.CommandType = CommandType.StoredProcedure
oCommand.CommandText = "EQA.COP00004.ALTA_MODIF_BEBES"
oCommand.CreateParameters()
Dim poOperacion As OracleObject = New OracleObject("EQA.T_REG_OPERACION", oCon)
With reg_operacion
poOperacion("FECHA_OPERACION") = .fecha_operacion
poOperacion("ORIGEN") = .origen
poOperacion("TIPO_OPERACION") = .tipo_operacion
poOperacion("CODIGO_LINEA") = .codigo_linea
poOperacion("CODIGO_ACCION") = .codigo_accion
poOperacion("CODIGO_OCURRENCIA") = .codigo_ocurrencia
poOperacion("CODIGO_PLANTILLA") = .codigo_plantilla
poOperacion("CODIGO_PROVEEDOR") = .codigo_proveedor
If .codigo_aa 0 Then
poOperacion("CODIGO_AA") = .codigo_aa
End If
poOperacion("EMAIL") = .email
End With
oCommand.Parameters("REG_OPERACION").Value = poOperacion
oCommand.Parameters("P_NUM_BEBES").Value = p_num_bebes
Dim poBebes() As OracleObject
Dim i As Integer = 0
While i < tab_bebes.Length
With tab_bebes(i)
Dim poBebe As OracleObject = New OracleObject("EQA.T_REG_BEBE", oCon)
poBebe("APELLIDO_PRINCIPAL") = .apellido_principal
poBebe("APELLIDO_SECUNDARIO") = .apellido_secundario
poBebe("FECHA_NACIMIENTO") = .fecha_nacimiento
poBebe("NACIMIENTO_FUTURO") = .nacimiento_futuro
poBebe("NOMBRE") = .nombre
poBebe("SEXO") = .sexo
ReDim Preserve poBebes(i)
poBebes(i) = New OracleObject("EQA.T_REG_BEBE", oCon)
poBebes(i) = poBebe
i += 1
End With
End While
oCommand.Parameters("TAB_BEBES").Value = poBebes
Dim myReturParam As OracleObject = New OracleObject("EQA.T_REG_ERROR", oCon)
Dim iRes As Integer = oCommand.ExecuteNonQuery()
myReturParam = CType(oCommand.Parameters("RESULT").Value, OracleObject)
'Rellenamos el objeto oResult con el valor devuelto de la función
oResult.codigo_campo = IIf(IsDBNull(myReturParam("CODIGO_CAMPO")), "", myReturParam("CODIGO_CAMPO"))
oResult.valor_campo = IIf(IsDBNull(myReturParam("VALOR_CAMPO")), "", myReturParam("VALOR_CAMPO"))
oResult.codigo_error = IIf(IsDBNull(myReturParam("CODIGO_ERROR")), "", myReturParam("CODIGO_ERROR"))
oResult.vip = IIf(IsDBNull(myReturParam("VIP")), "", myReturParam("VIP"))
Catch ex As FormatException
oResult.codigo_error = ex.Message
Catch ex As Exception
oResult.codigo_error = ex.Message
Finally
If Not oCommand Is Nothing Then oCommand.Dispose()
If Not oCon Is Nothing Then
If oCon.State = ConnectionState.Open Then oCon.Close()
oCon.Dispose()
End If
End Try
Return oResult
End Function
--------------------------------------------------------------------------------------
ERRORS
Message "ORA-59444: Message 59444 not found; product=RDBMS; facility=ORA"
Message "ORA-10420: Message 10420 not found; product=RDBMS; facility=ORA"
Message "ORA-43420: Message 43420 not found; product=RDBMS; facility=ORA"
TRACE
StackTrace " at CoreLab.Oracle.OracleCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.b(CommandBehavior A_0)
at CoreLab.Common.DbCommand.ExecuteReader()
at CoreLab.Common.DbCommandBase.ExecuteNonQuery()
at WSOraDirect.WSOraDirect.AltaModifBebes(t_reg_operacion reg_operacion, Int32 p_num_bebes, t_reg_bebe[] tab_bebes) in C:\Inetpub\wwwroot\WSOraDirect\ServiceOraDirect.asmx.vb:line 199"
Error calling a function of Oracle
You must create OracleTable object for TAB_BEBES parameter. Please use the following code.
Code: Select all
Dim poBebes As OracleTable = New OracleTable("T_TAB_BEBE", oCon)
Dim i As Integer = 0
While i < tab_bebes.Length
With tab_bebes(i)
Dim poBebe As OracleObject = New OracleObject("T_REG_BEBE", oCon)
poBebe("APELLIDO_PRINCIPAL") = .apellido_principal
poBebe("APELLIDO_SECUNDARIO") = .apellido_secundario
poBebe("FECHA_NACIMIENTO") = .fecha_nacimiento
poBebe("NACIMIENTO_FUTURO") = .nacimiento_futuro
poBebe("NOMBRE") = .nombre
poBebe("SEXO") = .sexo
poBebes.Add(poBebe)
i += 1
End With
End While-
Susana
Error calling a function of Oracle
Hello:
Now, I create an OracleTable object and I get call the function, but the parameter OracleTable arrives emptiness, with zero elements.
Why?
Thanks you,
Susana
Now, I create an OracleTable object and I get call the function, but the parameter OracleTable arrives emptiness, with zero elements.
Why?
Thanks you,
Susana