Execute Oracle Procedure from VB.NET ORA-009000 error

USAFHokie80

Hi. So I know I've done this before but I can't get it to work despite every example on the internet telling me it should... I'm trying to call a procedure from code. I keep getting an "Invalid SQL statement" exception. Any ideas why?

create procedure DoSomething
as
begin
--does some calculations and updates some rows
end DoSomething;

I try to execute from code like so:

Using conn = New OracleConnection(MyConnectionString)
Using cmd = conn.CreateCommand()
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "DoSomething"
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
End Using
End Using

At which point I get the aforementioned exception. What am I doing wrong? Sam.

ZurdoDev

Does calling your SP through Oracle directly work? Can you narrow it down to whether the error is in the Oracle syntax or in the C# calling it?

There are only 10 types of people in the world, those who understand binary and those who don't.

USAFHokie80

Yes, it works fine if I call it from an oracle client. This is only a problem trying to execute it from code.

ZurdoDev

When I googled the error and C# I saw some posts where people had to add some settings to their commands. I suggest googling until you find the right thing.

There are only 10 types of people in the world, those who understand binary and those who don't.

thatraja

You're getting error due to the statement(s) of stored procedure. Always share the relevant code/script in your question for qucik answer. Check this ORA-00900: invalid SQL statement tips[^]

thatraja

Code converters | Education Needed | Improve EverythingNew

USAFHokie80

No, the procedure works correctly when I execute it from SQLLite.

thatraja

Still you didn't share the script of stored procedure. Alright, possibly the issue is related to parameters then. Have you passed values for stored procedure parameters correctly? You should care datatime parameters particularly, you should pass correctly using date/time values instead of string value. Also the format. Did you check the link in my answer?

thatraja

Code converters | Education Needed | Improve EverythingNew

USAFHokie80

I didn't share the scrip because it works when it's execute on the server. The code I provided has identical signatures. There are no parameters passed into the procedure. Perhaps that is a problem - that since I didn't pass any parameters the Oracle library doesn't want to execute the command even though there are no parameters for the procedure.

USAFHokie80

Here is the actual code, though I can't imagine why this would help:

create or replace
procedure UpdateUnitType as
--updates unit type codes
cnt number(10,0):=0;

begin

for u in (select * from unit where type_cd = 1)
loop

cnt := 0;

select count(billet\_id)
into cnt
from billet
where fiscal\_year = u.fiscal\_year
and uic = u.uic;      

if cnt > 0 then

  update unit
  set type\_cd = 2
  where unit\_id = u.unit\_id;

end if;

end loop;

end updateunittype;

Jorgen Andersson

Well, what the ORA-00900 says is that there is something wrong with the CommandText, I'm afraid you will have to share it if you want any useful feedback

Wrong is evil and must be defeated. - Jeff Ello[^]

Simon_Whale

hopefully I am missing something but are you sure you are using SQLite? as this link shows that it doesn't support stored procedures. http://www.sqlite.org/whentouse.html[^]

Quote:

n order to achieve simplicity, SQLite has had to sacrifice other characteristics that some people find useful, such as high concurrency, fine-grained access control, a rich set of built-in functions, stored procedures, esoteric SQL language features, XML and/or Java extensions, tera- or peta-byte scalability, and so forth.

Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON

GuyThiebaut

With these sorts of issues the first thing I do is write a really simple stored procedure to eliminate the stored procedure being an error. Now I know you have tested the stored procedure from Oracle however I would do this anyway. So create something like :

create procedure test_orac as
begin

declare @testvar int
select @testvar = 1

end

Then call this procedure from .NET . If you still get the same error check your tnsnames.ora file to be sure that it points to the correct service and that the syntax within that file is correct.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

USAFHokie80

You're right. Actually, I'm using Oracle's "SQL Developer". It was just easier to say SQLLite.

thatraja

It's been long time ....Oracle, try different syntax like below

cmd.CommandText = "{CALL StoredprocedureName()}"

OR

cmd.CommandText = "BEGIN StoredprocedureName(); END;"

And are you using oracle "package" by any chance? Because the stored procedure missing the declare keyword for cnt. Possbily you have declared the cnt inside the package & executed the stored procedure to see the output. It would work. So if you have the stored procedure inside the package, you should mention(prefix) the package before the stored procedure in your code like below.

cmd.CommandText = "PackageName.ProcedureName"

From link:[^]

•I was using a older JDBC driver so after fixing the above error I was getting org.springframework.dao.InvalidDataAccessResourceUsageException: could not execute query; SQL [{ call PROC_GET_NEW_CHECK_NUMBER(?,?,?) }]; nested exception is org.hibernate.exception.SQLGrammarException: could not execute query at com.ibm.ejs.jms.listener.ServerSessionDispatcher.dispatch(ServerSessionDispatcher.java:44) at com.ibm.ejs.container.MDBWrapper.onMessage(MDBWrapper.java:100) java.sql.SQLSyntaxErrorException: ORA-00900: invalid SQL statement To fix this I upgraded my JDBC driver to the correct version that matches the Oracle installation. So in my case I used ojdbc6_11g_11_2_0_1.jar since my Oracle instance :Oracle 11.2.0.1.0

So you should use/reference the proper driver(dll) in your project. Verify both versions of assemblies

Oracle/PLSQL: ORA-00900[^]

thatraja

Code converters | Education Needed | Improve EverythingNew

GuyThiebaut

Did you resolve the issue? If you did it would be interesting to know how you resolved the issue.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens

thatraja

I think one more hit & run :sigh:

thatraja

Code converters | Education Needed | Improve EverythingNew

GuyThiebaut

Yes ,for some, the forum works one way - it's there just to get answers to your questions and not to contribute to the wider knowledge of the community.

“That which can be asserted without evidence, can be dismissed without evidence.”

― Christopher Hitchens