Issue with using oracle datareader and store procedure?

This is my first time using oracle on the backend. I am trying to fill a generic
list with a datareader. Not sure if the error is in the proc or my data object.
Can anyone please point me in the right direction?

ERROR:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'P_COFR_CASE_SELECT_ALL' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored 

 

 

 ********** CODE **************

<code>

///<summary>

/// Returns a list with COFR_CASE objects.

///</summary>

///<returns>A generics List with the COFR_CASE
objects.</returns>publicstaticCOFR_CASEList GetList()

{COFR_CASEList tempList = null;using (OracleConnection myConnection =
newOracleConnection(AppConfiguration.ConnectionString))

{

OracleCommand myCommand = newOracleCommand("p_COFR_CASE_SELECT_ALL",
myConnection);   // ERROR IS THROWN HEREmyCommand.CommandType =
CommandType.StoredProcedure;

myConnection.Open();using (OracleDataReader myReader =
myCommand.ExecuteReader())

{if (myReader.HasRows)

{tempList = newCOFR_CASEList();while (myReader.Read())

{

tempList.Add(FillDataRecord(myReader));

}

}

myReader.Close();

}

}return tempList;

}

</code>

 

************ oracle procedure ****************** 

CREATE OR REPLACE PROCEDURE p_CC_CASE_SELECT_ALL
(
    CASE_TABLE OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN CASE_TABLE FOR
 SELECT
  CASE_CONTROL_ID,
  OPERATOR_NAME,
  ADDRESS_LINE_1,
  ADDRESS_LINE_2,
  ADDRESS_LINE_3,
  ADDRESS_LINE_4,
  ADDRESS_LINE_5,
  ZIP_CODE,
  COUNTRY,
  NATIONALITY_CODE,
  CONTACT_NAME,
  ALTERNATE_ADDRESS_ID,
  CASE_TYPE_CODE,
  STATUS_CODE,
  APPROVAL_DATE,
  TERMINATION_DATE,
  EXAMINER_ID,
  CASE_COMMENT,
  ACTIVE_INACTIVE_STATUS,
  PENDING_DATE,
  CREATE_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE
 FROM CC_CASE
 WHERE ROWNUM <= 10;

END p_CC_CASE_SELECT_ALL;
/

Hi chaumette, 

It looks like you have mistyped the name of your stored procedure:

CREATE OR REPLACE PROCEDURE p_CC_CASE_SELECT_ALL
(
    CASE_TABLE OUT SYS_REFCURSOR
)
IS
BEGIN
    OPEN CASE_TABLE FOR
 SELECT
  CASE_CONTROL_ID,
  OPERATOR_NAME,
  ADDRESS_LINE_1,
  ADDRESS_LINE_2,
  ADDRESS_LINE_3,
  ADDRESS_LINE_4,
  ADDRESS_LINE_5,
  ZIP_CODE,
  COUNTRY,
  NATIONALITY_CODE,
  CONTACT_NAME,
  ALTERNATE_ADDRESS_ID,
  CASE_TYPE_CODE,
  STATUS_CODE,
  APPROVAL_DATE,
  TERMINATION_DATE,
  EXAMINER_ID,
  CASE_COMMENT,
  ACTIVE_INACTIVE_STATUS,
  PENDING_DATE,
  CREATE_DATE,
  LAST_UPDATE_USER,
  LAST_UPDATE_DATE
 FROM CC_CASE
 WHERE ROWNUM <= 10;

END p_CC_CASE_SELECT_ALL;

 

You have 

CREATE OR REPLACE PROCEDURE p_CC_CASE_SELECT_ALL

.... 

END p_CC_CASE_SELECT_ALL;

 

but you are calling:

OracleCommand myCommand = newOracleCommand("p_COFR_CASE_SELECT_ALL",
myConnection); 

You have COFR there instead of CC

 Pete

Sorry Pete, that was me masking the name (of organization) and me forgetting to
change one part. I edited my post. The problem is something else.

Not sure what I overlooked? Do I need to add a parameter to my code to handle
the output from the Oracle proc? Can someone point me to an example of a
datareader using a oracle proc with no parameters but returning data?

Hi,

  I cant say that I've ever seen a ora-6550/pls-201 that wasnt a case of callign
a procedure that doesnt exist, perhaps because the procedure name is typo'd, its
in a different schema you dont have priviledges to, etc.

 To answer your question regarding an example, here's a simple example that uses
Oracle's ODP.NET.

 Hope it helps,
Greg

  

/*
CREATE OR REPLACE procedure simplerefcur( v_deptno in number,ecur out
sys_refcursor) is
    BEGIN
     OPEN ecur for select * from emp where deptno=v_deptno;
   end;
/
*/using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

public class testrefcur
{
public static void Main()
{
    using (OracleConnection con = new OracleConnection("data
source=orcl;user id=scott;password=tiger;"))
    {
        con.Open();
        using (OracleCommand cmd = new
OracleCommand("simplerefcur",con))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            OracleParameter p1 = new OracleParameter("p1",
OracleDbType.Int32);
            p1.Value = 10;
            cmd.Parameters.Add(p1);
            OracleParameter p2 = new OracleParameter("p2",
OracleDbType.RefCursor, ParameterDirection.Output);
            cmd.Parameters.Add(p2);
            OracleDataAdapter da = new OracleDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);
        }
    }
}
}

Greg,

 I appreciate your answer. My problem involved needing to grant execute
permission on the procedure.

Thanks