Execute Oracle stored procedure from .NET

Question

I am trying to run this C# code

string sql = @"EXECUTE GETEMPLOYEES1()";

OracleCommand cmd = new OracleCommand(sql, cnn);
cmd.CommandType = CommandType.Text;

cmd.ExecuteNonQuery();

but I keep getting

Invalid SQL error

My stored procedure code is very simple

CREATE OR REPLACE PROCEDURE GETEMPLOYEES1 
AS 
BEGIN
    DBMS_OUTPUT.PUT_LINE('Welcome to FYICenter!'); 
END;

I am able to run it normally by using CommandType = StoredProcedure but I need to execute some dynamic code entered by the user and it is vital to me to do it by CommandType = Text.

Do you need an additional ; on the end of your sql variable. ie. @"EXECUTE GETEMPLOYEES1();"; — DotNetHitMan
– DotNetHitMan, Commented Sep 10, 2015 at 14:55
i just tried string sql = @"CALL GETEMPLOYEES1()"; and that worked fine . I find that strange because i know EXEC should work . — Dotnetsqlcoder
– Dotnetsqlcoder, Commented Sep 10, 2015 at 15:01

mason · Accepted Answer · 2015-09-10 16:56:14Z

1

EXECUTE is a SQL*Plus command. Use either

CALL GETEMPLOYEES1()

or

BEGIN GETEMPLOYEES1(); END;

Also if you choose command type StoredProcedure the library adds BEGIN ... END; internally for you.

CALL also requires empty parentheses when a procedure is parameterless.

edited Sep 10, 2015 at 16:56

mason

32.8k12 gold badges83 silver badges127 bronze badges

answered Sep 10, 2015 at 15:04

Husqvik

5,8191 gold badge23 silver badges32 bronze badges

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

1 Comment

Dotnetsqlcoder Over a year ago

you are right , i was able to execute it with call , do you know how to do it when we pass a ref cursor to the proc also ?

Mladen Oršolić · Accepted Answer · 2015-09-10 14:53:07Z

0

Try replacing DBMS_OUTPUT.PUT_LINE('Welcome to FYICenter!'); with null;, and see if that makes it any different. Im asuming in connection context there is no sense to try to output text, there is no console.

answered Sep 10, 2015 at 14:53

Mladen Oršolić

1,4323 gold badges24 silver badges44 bronze badges

Collectives™ on Stack Overflow

Execute Oracle stored procedure from .NET

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related