Execute Oracle Procedure from VB.NET ORA-009000 error
-
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 UsingAt which point I get the aforementioned exception. What am I doing wrong? Sam.
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
-
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
No, the procedure works correctly when I execute it from SQLLite.
-
No, the procedure works correctly when I execute it from SQLLite.
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
-
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
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.
-
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
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)
loopcnt := 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;
-
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 UsingAt which point I get the aforementioned exception. What am I doing wrong? Sam.
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[^]
-
No, the procedure works correctly when I execute it from SQLLite.
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
-
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 UsingAt which point I get the aforementioned exception. What am I doing wrong? Sam.
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
begindeclare @testvar int
select @testvar = 1end
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
-
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
You're right. Actually, I'm using Oracle's "SQL Developer". It was just easier to say SQLLite.
-
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)
loopcnt := 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;
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
declarekeyword 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"
•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
thatraja
-
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 UsingAt which point I get the aforementioned exception. What am I doing wrong? Sam.
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
-
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
I think one more hit & run :sigh:
thatraja
-
I think one more hit & run :sigh:
thatraja
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