For an asp.net app I'm working on, I have to hit an Oracle database and get information from a Stored Procedure.  Simple enough, I had thought, but it proved to be quite an adventure in debugging. 

First off, I needed ODP.NET for Oracle 10g.  Every link on the Oracle site seemed to go to a download for ODP.NET for 11g.  Assuming (foolishly) that Oracle, like almost every other software, supported backwards compatibility, I thought nothing of it and installed this version. 

I spent a bit of time before I found a relevant blog post with a direct link to the correct package.
I was trying to construct the command with ODP.NET with the same syntax as what I was using to execute it in SQL*PLUS, namely, "execute package_name_pkg.p_GetTheDataNeeded".  This was throwing an OracleException with the following information:

ORA-06550: line 1, column 12:
PLS-00103: Encountered the symbol "package_name_pkg" when expecting one of the following:
:= . ( @ % ;
The symbol ":=" was substituted for "package_name_pkg" to continue.

I soon realized that the 'execute' word was incorrect, and removed it.  This got me a step closer to my ultimate goal.  I was still presented with an OracleException, this time for "wrong number or types of arguments in call to 'p_GetTheDataNeeded'".

In SQL*PLUS, I had been saving the results of the stored proc in a ref cursor, and then printing that data.  I decided to give that a whirl in my app, and thus added an output parameter:

cmd.Parameters.Add(new OracleParameter("rc1", Oracle.DataAccess.Client.OracleDbType.RefCursor)).Direction = ParameterDirection.Output

Sure enough, this did it.  In my research I came across quite a few people online who had received the same PLS-00103 error despite not having the 'execute' word in their command.  If you're getting the same error, I'd suggest checking the syntax of your command as that is likely the source of the issue.

Here is some sample code for you to examine.  Use the package linked above and the same syntax for your query string and your OracleCommand object.  The syntax for the RefCursor parameter is pretty strange, so I hope this can help someoen out.

public void CallOracleDatabase(int id)
        {
            string connectionstring = "Data Source=dev;User Id=user;Password=password;";
            var cnxn = new OracleConnection(connectionstring);
            OracleCommand cmd = new OracleCommand();
            cmd.Connection = cnxn;
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "package_name_pkg.p_GetTheDataNeeded";
            cmd.Parameters.Add(new OracleParameter("myrefcursor", Oracle.DataAccess.Client.OracleDbType.RefCursor)).
                Direction = ParameterDirection.Output;
            cmd.Parameters.Add("paramname", OracleDbType.Int32, 4).Value = id;
            cnxn.Open();
            OracleDataReader dr = cmd.ExecuteReader();
            //.... 
            cnxn.Close();
        }