Using SQL stored function gives invalid SQL statement

Question

I'm trying to use stored function from Oracle Express database in c# application.

OdbcCommand com = new OdbcCommand("SILNIA",sqlConn);
com.CommandType = CommandType.StoredProcedure;

OdbcParameter sqlParam = new OdbcParameter("@ReturnValue", OdbcType.Int);
sqlParam.Direction = ParameterDirection.ReturnValue;
com.Parameters.Add(sqlParam);
com.ExecuteNonQuery();

label1.Content = com.Parameters["@ReturnValue"].Value.ToString();

where SILNIA function (compiles without errors) is defined:

create or replace
FUNCTION SILNIA RETURN NUMBER IS
w NUMBER := 1;
BEGIN
for i in 1..5 loop
w:=w*i;
end loop;

RETURN w;
END SILNIA;

On line:

com.ExecuteNonQuery();

i get

ERROR [42000] [Microsoft][ODBC driver for Oracle][Oracle]ORA-00900: invalid SQL statement.

But this works works without any problems:

OdbcCommand sqlCom = new OdbcCommand("SELECT SILNIA() FROM DUAL", sqlConn);
label1.Content = sqlCom.ExecuteScalar().ToString();

Hogan · Accepted Answer · 2010-11-13 22:11:38Z

1

You can't use ExecuteNonQuery() to run a SP. You have to do something else -- ExecuteScalar or ExecuteReader

This is because ExecuteNonQuery() is designed to only work with UPDATE, INSERT, and DELETE statements.

answered Nov 13, 2010 at 22:11

Hogan

70.7k10 gold badges83 silver badges119 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mmatloka Over a year ago

I've tried both OdbcDataReader r = com.ExecuteReader(); and label1.Content = (string)com.ExecuteScalar(); instead of com.ExecuteNonQuery(); and the same error occurs.

mint Over a year ago

You sure you can't run ExecuteNonQuery() for SP's? I'm fairly confident you can call it...

Jeffrey Kemp · Accepted Answer · 2010-11-14 08:08:34Z

0

Your function accepts no parameters - try this:

SELECT SILNIA FROM DUAL

answered Nov 14, 2010 at 8:08

Jeffrey Kemp

60.5k15 gold badges111 silver badges161 bronze badges

Collectives™ on Stack Overflow

Using SQL stored function gives invalid SQL statement

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related