Running Oracle stored procs from C#

Question

Hi,

A beginner question: I have a stored proc (just a procedure, without any packages) in the Oracle Database:

CREATE OR REPLACE procedure FII_DBO.CLEAR_UNIT_TEST_PRODUCT
IS
BEGIN
 ...
END CLEAR_UNIT_TEST_PRODUCT;

and it works fine in TOAD. However, when I try to run it from C# it complains:

System.Data.OracleClient.OracleException: ORA-06550: line 1, column 7:
PLS-00201: identifier 'CLEAR_UNIT_TEST_PRODUCT' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

relevant C# code:

Command = new OracleCommand();
Command.CommandText = procedureName;
Command.CommandType = CommandType.StoredProcedure;
Command.Connection = connection;
Command.ExecuteNonQuery();

Answer 1

Are you including the package name in the procedureName variable?

i.e. setting procedureName to "FII_DBO.CLEAR_UNIT_TEST_PRODUCT", not just "CLEAR_UNIT_TEST_PRODUCT"?

Answer 2

Your procedure seems to be created in another schema.

Issue

ALTER SESSION SET CURRENT_SCHEMA = FII_DBO

right after connecting.

I recall the provider has some bugs with calling stored procedures.

Set your CommandText to

BEGIN FII_DBO.CLEAR_UNIT_TEST_PRODUCT(); END;

and CommandType to Text

Also you may try to change the case of you stored procedure name, like:

fii_dbo.clear_unit_test_product

, I recall that the case matters too.

Answer 3

Check that the Oracle user that your .NET application is connecting with has permissions to execute the stored procedure.

Answer 4

Found it, the error message was a bit misleading. I was executing it as a different user, who didn't have the proper access rights. This did the trick:

grant execute on FII_DBO.CLEAR_UNIT_TEST_PRODUCT to FII_USER;