Running Oracle stored procs from C#

Question

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();

score 3 · Accepted Answer · answered Mar 18 '09 at 11:12

3

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

answered Mar 18 '09 at 11:12

Ian Nelson

57,123
20
76
103

@Ian Nelson, @Grzenio - I have a similar issue. The same error and the C# code looks much the same except I am using `ExecuteReader()`. I don't see how it could be permissions as the User ID I am connecting with is the owner of the schema and the specific procedures too. Any other reasons this might occur? – one.beat.consumer Jul 11 '12 at 19:39

score 1 · Answer 2 · answered Mar 18 '09 at 11:15

1

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;

answered Mar 18 '09 at 11:15

Grzenio

35,875
47
158
240

1

Yeah, the error message is less than helpful, but I guess the thinking is that it's done for security purposes. If the user can't access the stored procedure, then the database won't even admit to its existence. – Ian Nelson Mar 18 '09 at 11:21

score 0 · Answer 3 · answered Mar 18 '09 at 11:07

0

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"?

answered Mar 18 '09 at 11:07

Ian Nelson

57,123
20
76
103

Yeah, tried that as well: identifier 'FII_DBO.CLEAR_UNIT_TEST_PRODUCT' must be declared – Grzenio Mar 18 '09 at 11:10

Quassnoi · Answer 4 · 2009-03-18T11:18:16.050

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.

Running Oracle stored procs from C#

4 Answers4