C# pl/sql package-function

1
votes
public List<MuseumDto> GetMuseumsByName(string museum_Name)
{
    var connectionString = ConfigurationManager.
      ConnectionStrings["OracleConnectionString"].ConnectionString;
    var museum = new List<MuseumDto>();
    using (var connection = new OracleConnection(connectionString))
    {
        OracleCommand mycom = new OracleCommand();
        connection.Open();
        mycom.CommandText = "museum_package.get_MuseumByName";
        mycom.Connection = connection;
        mycom.CommandType = CommandType.StoredProcedure;

        mycom.Parameters.Add("v_museum", museum_Name);

        OracleParameter returnParameteraram = mycom.Parameters.Add("ReturnValue", OracleDbType.RefCursor);
        returnParameteraram.Direction = ParameterDirection.ReturnValue;

        OracleDataReader reader = mycom.ExecuteReader();
        try
        {
            while (reader.Read())
            {
                museum.Add(new MuseumDto
                {
                    museumId = reader.GetInt32(0),
                    name = reader.GetString(1),
                    city = reader.GetString(2),
                    about= reader.GetString(3),
                    schedule = reader.GetString(4),
                    noArtefacts= reader.GetInt32(5),

                });
            }
        }
        finally
        {
            // always call Close when done reading.
            reader.Close();
        }
    }
    return museum;
}

This is the table definition:

create table museum
( museumid     number(5) not null
, name         varchar2(30)
, city         varchar2(30)
, about        varchar2(200)
, schedule     varchar2(40)
, noartefacts  number(3) );

and here is my function:

create or replace package body museum_package as

    function get_MuseumByName
        ( v_museum Museum.Name%type )
        return sys_refcursor
    is
        v_cursor sys_refcursor;
        v_museumName museum.name%type := '%' || v_museum || '%';
    begin
        open v_cursor for
            select museumID, name, city, about, schedule, noArtefacts
            from   museum 
            where  name like v_museumName;

        return v_cursor;
    end;

end museum_package;

I'm receiving an exception:

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

2
c#plsql
ORA-06550 usually means something wrong with your PL/sql function.what does Museum.Name%type means? - Hameed Syed
@Hameed, Museum.Name%type instructs the PL/SQL compiler to anchor the function parameter to the datatype of the table column, probably a varchar2 in this case. PLS-00306 means the function wan't called with the expected parameters. - William Robertson
is a variable same as Museum.name field from table, what exactly are you asking? :) @Hameed - Alexandra Tupu
@WilliamRobertson Museum.Name%type so is a type from the current tables column if I can understand that,but how can .net compiler deduce the type.may be the OP's user need to specify the data type explicitly? - Hameed Syed
@Hameed I don't know C# but it appears to declare museum_Name as a string, which should be OK unless there is more to it. - William Robertson

2 Answers

1
votes

I can't comment in another people's posts as I don't have enough reputation (I'm new around here). Your problem may be because .NET can't map the types of the columns in Oracle with the ones you specified while reading the columns.

Could you please show us the data types of the columns of the museum table?

0
votes

Return value must be added as first parameter.

 OracleParameter returnParameteraram = mycom.Parameters.Add("ReturnValue", OracleDbType.RefCursor);
 returnParameteraram.Direction = ParameterDirection.ReturnValue;
mycom.Parameters.Add("v_museum", museum_Name);