Running Oracle stores procs from C #

Question

Initial question: I have a stored procedure (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 great in TOAD. However, when I try to run it with 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

corresponding C # code:

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

Answer 1

Verify that the Oracle user that your .NET application connects with has permissions to execute the stored procedure.

Answer 2

The error message was found to be a little misleading. I ran it as another user who did not have proper access rights. This did the trick:

grant execute on FII_DBO.CLEAR_UNIT_TEST_PRODUCT to FII_USER;

Answer 3

procedureName?

. procedureName "FII_DBO.CLEAR_UNIT_TEST_PRODUCT", "CLEAR_UNIT_TEST_PRODUCT"?

Answer 4

Your procedure is created in a different scheme.

Problem

ALTER SESSION SET CURRENT_SCHEMA = FII_DBO

immediately after connecting.

I remember that the provider has some errors when calling stored procedures.

Set CommandTextto

BEGIN FII_DBO.CLEAR_UNIT_TEST_PRODUCT(); END;

and CommandTypeupText

You can also try changing the case of the stored procedure name, for example:

fii_dbo.clear_unit_test_product

I remember that business also matters.