Call an Oracle function with parameters with VB.NET

Question

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

Wernfried Domscheit · Accepted Answer · 2015-04-07 15:01:47Z

1

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

answered Apr 7, 2015 at 15:01

Wernfried Domscheit

60.4k10 gold badges93 silver badges132 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Morcilla de Arroz · Accepted Answer · 2015-04-07 15:02:03Z

0

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**

answered Apr 7, 2015 at 15:02

Morcilla de Arroz

2,18222 silver badges32 bronze badges

1 Comment

Lucas Ramos Over a year ago

Thanks you too! This was also the correct answer! Greetings!

Collectives™ on Stack Overflow

Call an Oracle function with parameters with VB.NET

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related