2

I have stored procedure in Oracle

CREATE OR REPLACE PROCEDURE proc1 (p_param1 INTEGER, p_param2 CHAR, p_param3 INTEGER)
AS
BEGIN
... 
END;

and I call it in Oracle SQL Developer using this statement:

EXECUTE proc1(2013, 1, 3);

I try to call it in C# from Visual Studio but it doesn't works

_db.Execute("EXEC proc1 (2013, 1, 3)");

How can I properly call it in C#?

Thanks.

weto
  • 69
  • 1
  • 1
  • 12
  • What do you mean by _doesn't work_? Any exception or error message you get? – Soner Gönül Nov 16 '14 at 11:45
  • It throws this exception: Additional information: ORA-00900: invalid SQL statement – weto Nov 16 '14 at 11:49
  • Second parameter is of type `CHAR`. Did you try `_db.Execute("EXEC proc1 (2013, '1', 3)");`? – Abecee Nov 16 '14 at 11:49
  • of course, I'm triing it too, but it throws the same exception... – weto Nov 16 '14 at 11:51
  • Did you check any of, e.g., http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp, http://stackoverflow.com/questions/1260952/how-to-execute-a-stored-procedure-within-c-sharp-program, or http://stackoverflow.com/questions/7542517/call-a-stored-procedure-with-parameter-in-c-sharp ? – Abecee Nov 16 '14 at 12:02
  • I think, this is no problem with parameters... I try to create procedure without parameters, for example proc2. When I call it in Oracle Sql Developer using 'EXECUTE proc2;' it works, but in visual studio doesn't... – weto Nov 16 '14 at 12:10

1 Answers1

2

EXEC is a command of SQL*Plus. Try to use command without EXEC or in anonymous PL/SQL block:

_db.Execute("proc1 (2013, 1, 3)");

or

_db.Execute("begin proc1 (2013, 1, 3); end;");

Also, in this case, 1 could be converted to CHAR automatically, and in other cases you need to use quotes '':

_db.Execute("begin proc1 (2013, 'abc', 3); end;");
Dmitriy
  • 5,525
  • 12
  • 25
  • 38
  • 1
    I'm trying your first statement too and it throws the same exception: invalid SQL statement. I don't know why..? But second and third way are working both. Thanks. – weto Nov 16 '14 at 14:03
  • this saved my butt. i had tried over 30 different ways to get this to work until I landed here. thank you kindly sir. – VBAHole Jun 27 '16 at 18:37