ODP.NET not recognized procedure in oracle package

Question

I have a simple package defined as follows:

CREATE OR REPLACE PACKAGE wvParty IS
TYPE refParties IS REF CURSOR;

END wvParty;

CREATE OR REPLACE PACKAGE BODY wvParty IS PROCEDURE proc_GetParties( p_party_name IN OUT VARCHAR2, x_party_info OUT refParties ) IS BEGIN p_party_name := '%'||p_party_name||'%';

    OPEN x_party_info FOR
    SELECT party_id, party_number, party_type, party_name, 
        person_first_name, person_middle_name, person_last_name,
        known_as, known_as2, known_as3, known_as4, known_as5
    FROM hz_parties
    WHERE 1=1
    AND party_name LIKE p_party_name; 
END;

END wvParty;

It gets created in the oracle db with no errors. In my code, I'm trying to call it using:

      try
  {
    OracleConnection conn = new OracleConnection(_connStr);
    conn.Open();
    OracleCommand cmd = conn.CreateCommand();
    cmd.CommandText = "wvParty.proc_GetParties";
    cmd.CommandType = CommandType.StoredProcedure;

    OracleParameter partyName = new OracleParameter();
    partyName.ParameterName = "p_party_name";
    partyName.OracleDbType = OracleDbType.Varchar2;
    partyName.Direction = ParameterDirection.InputOutput;
    partyName.Value = "Bubba";
    cmd.Parameters.Add(partyName);

    OracleParameter refParties = new OracleParameter();
    refParties.ParameterName = "x_party_info";
    refParties.OracleDbType = OracleDbType.RefCursor;
    refParties.Direction = ParameterDirection.Output;
    cmd.Parameters.Add(refParties);

    OracleDataReader rdr = cmd.ExecuteReader();

Yet, when I do I get the error:

PLS-00302: component 'PROC_GETPARTIES' must be declared.

So I'm at a loss. I've created the proc as part of the package, I've declared it as I believe I should but I'm having little success resolving this. Any suggestion?

Brian Camire Brian Camire · Accepted Answer · 2012-08-03T01:28:18

I can't comment on the C# side of things, but there are a some problems on the Oracle side, like:

proc_GetParties is defined in the package body, but is not declared in the package specification. This means that it's not visible outside the package body. This explains why you are getting the PLS-00302 error.
The 1=1 condition is not needed in the WHERE clause.
Do you really want to change the value of p_party_name within the procedure and have that change to be visible to the caller? If not, it might be better to leave p_party_name unchanged and change the LIKE condition to party_name LIKE '%' || p_party_name || '%'.

ODP.NET not recognized procedure in oracle package

1 Answers