CODEWITHSUNDEEP
vb.netoracle-databasepackage
Lucas Ramos
Lucas Ramos

Reputation: 5

Call an Oracle function with parameters with VB.NET

First of all, I'm from Argentina, sorry for mi poor english!

I'm working in Visual.NET 2010, and I'm trying to make a call to a function in an Oracle package, but I get this error:

PLS-00306: wrong number or types of arguments in call to 'FN_FINDPRONOSTICO'

This is the package definition:

create or replace
PACKAGE PKG_HOME AS

    TYPE CR_ESTACIONREFERENCIA IS REF CURSOR;
    TYPE CR_PRONOSTICO IS REF CURSOR;
    TYPE CR_ALERTA IS REF CURSOR;
    TYPE CR_ESTADIOSPREDOMINANTESZONA IS REF CURSOR;
    TYPE CR_VARIEDADES IS REF CURSOR;

    --FUNCTIONS

    FUNCTION FN_FINDESTACIONREFERENCIA(PR_IDZONAFENO IN NUMERIC) RETURN CR_ESTACIONREFERENCIA;
    FUNCTION FN_FINDPRONOSTICO(PR_IDESTACION IN NUMERIC) RETURN CR_PRONOSTICO;
    FUNCTION FN_FINDALERTAMETEO(PR_IDESTACION IN NUMERIC) RETURN CR_ALERTA;
    FUNCTION FN_FINDALERTAPLAGA(PR_IDZONAFENO IN NUMERIC) RETURN CR_ALERTA;
    FUNCTION FN_FINDALERTAENFERMEDAD(PR_IDZONAFENO IN NUMERIC) RETURN CR_ALERTA;

    PROCEDURE SP_ESTFENOPREDZONAVAR(pFechaDesde IN DATE, pFechaHasta IN DATE,pIdZona IN NUMBER ,pIdVariedad IN NUMBER);

    FUNCTION FN_ESTADIOSFENOPREDZONA(pIdZona IN NUMBER) RETURN CR_ESTADIOSPREDOMINANTESZONA;
END;

And this is the code in VB.NET 

Dim cmd As New OracleCommand("FRUTIC.PKG_HOME.FN_FINDPRONOSTICO", OraConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.BindByName = True

Dim oparam0 As OracleParameter = cmd.Parameters.Add("PR_IDESTACION", OracleDbType.Int64)
oparam0.Value = 65
oparam0.Direction = ParameterDirection.Input

Dim oparam1 As OracleParameter = cmd.Parameters.Add("CR_PRONOSTICO", OracleDbType.RefCursor)
oparam1.Direction = ParameterDirection.Output

Dim reader As OracleDataReader
reader = cmd.ExecuteReader()

Do While reader.Read()
    Dim obj As Object

    obj = reader.GetValue(0)
Loop

If I run this SQL statement (SELECT FRUTIC.PKG_HOME.FN_FINDPRONOSTICO(65) FROM DUAL) in Oracle SQL Developer, it returns the results correctly.

Where could be the problem?

Thank you very much in advance! Greetings from Argentina, Lucas

Upvotes: 0

Views: 3806

Answers (2)

Morcilla de Arroz
Morcilla de Arroz

Reputation: 2182

Maybe you have to use "ReturnValue" instead of "Output". Do this:

Dim oparam0 As OracleParameter = cmd.Parameters.Add("PR_IDESTACION", OracleDbType.Int64)
oparam0.Value = 65
oparam0.Direction = ParameterDirection.Input

Dim oparam1 As OracleParameter = cmd.Parameters.Add("CR_PRONOSTICO", OracleDbType.RefCursor)
oparam1.Direction = ParameterDirection.**ReturnValue**

Upvotes: 0

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59456

This is wrong:

Dim oparam1 As OracleParameter = cmd.Parameters.Add("CR_PRONOSTICO", OracleDbType.RefCursor)
oparam1.Direction = ParameterDirection.Output

It must be this:

Dim oparam1 As OracleParameter = cmd.Parameters.Add("CR_PRONOSTICO", OracleDbType.RefCursor)
oparam1.Direction = ParameterDirection.ReturnValue

Upvotes: 1

Related Questions