ORA-009000 Invalid SQL Statement when executing from code
-
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? And yes, the procedure compiles and gives no errors when calling from an oracle client.
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.
Does it work when you execute the stored procedure through Oracle?
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
-
Does it work when you execute the stored procedure through Oracle?
Every day, thousands of innocent plants are killed by vegetarians. Help end the violence EAT BACON
Yes. This is a only a problem calling the procedure from code.
-
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? And yes, the procedure compiles and gives no errors when calling from an oracle client.
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 have already posted this question in the Database forum[^]. You've been here six and a half years, and you still haven't realized that cross-posting questions isn't acceptable behaviour?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
-
You have already posted this question in the Database forum[^]. You've been here six and a half years, and you still haven't realized that cross-posting questions isn't acceptable behaviour?
"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer
Oh, I'm terribly sorry that I added another few hundred duplicate bytes the massive amount of data stored here. However will this site possibly be useful now that I've completely gummed it up???? What in incredibly silly thing to get upset about. Report me to the admins, quick! BTW, since I don't actually know the traffic on the individual discussions and since this question very clearly spans multiple topics, I don't see the problem here. So if you don't like my post and have nothing to contribute other than being a pompous jerk, feel free to pass over my posts.
-
Oh, I'm terribly sorry that I added another few hundred duplicate bytes the massive amount of data stored here. However will this site possibly be useful now that I've completely gummed it up???? What in incredibly silly thing to get upset about. Report me to the admins, quick! BTW, since I don't actually know the traffic on the individual discussions and since this question very clearly spans multiple topics, I don't see the problem here. So if you don't like my post and have nothing to contribute other than being a pompous jerk, feel free to pass over my posts.
USAFHokie80 wrote:
Oh, I'm terribly sorry that I added another few hundred duplicate bytes the massive amount of data stored here. However will this site possibly be useful now that I've completely gummed it up????
WRONG. What you've done is place the same question in multiple places, splitting up the possibility of getting a collaborated answer. Now the answers you get could possibly conflict with each other, confusing YOU. This has nothing to do with the number of bytes you posted.
USAFHokie80 wrote:
BTW, since I don't actually know the traffic on the individual discussions and since this question very clearly spans multiple topics, I don't see the problem here. So if you don't like my post and have nothing to contribute other than being a pompous jerk, feel free to pass over my posts.
By the error message alone, it's only a database question. It has nothing to do with VB.NET or C#. It's simple. Something in the string you built and sent to Oracle wasn't correct. Since you didn't show the complete statement you sent, it's impossible to tell you what you did wrong.
A guide to posting questions on CodeProject
How to debug small programs
Dave Kreskowiak -
USAFHokie80 wrote:
Oh, I'm terribly sorry that I added another few hundred duplicate bytes the massive amount of data stored here. However will this site possibly be useful now that I've completely gummed it up????
WRONG. What you've done is place the same question in multiple places, splitting up the possibility of getting a collaborated answer. Now the answers you get could possibly conflict with each other, confusing YOU. This has nothing to do with the number of bytes you posted.
USAFHokie80 wrote:
BTW, since I don't actually know the traffic on the individual discussions and since this question very clearly spans multiple topics, I don't see the problem here. So if you don't like my post and have nothing to contribute other than being a pompous jerk, feel free to pass over my posts.
By the error message alone, it's only a database question. It has nothing to do with VB.NET or C#. It's simple. Something in the string you built and sent to Oracle wasn't correct. Since you didn't show the complete statement you sent, it's impossible to tell you what you did wrong.
A guide to posting questions on CodeProject
How to debug small programs
Dave KreskowiakThanks for the concern, but I think that I'm probably the best one to decide what is or isn't confusing to me. As for the error, you apparently don't understand. The procedure signature is identical to what I've posted. Only the name of the procedure is different. And as I stated, the procedure itself works perfectly well when executed from SQLLite command line client as "execute DoSomething;". So the problem actually is with the way it's being called from code and -not- the database. That is the complete statement, again, with only the actual name changed.
-
Thanks for the concern, but I think that I'm probably the best one to decide what is or isn't confusing to me. As for the error, you apparently don't understand. The procedure signature is identical to what I've posted. Only the name of the procedure is different. And as I stated, the procedure itself works perfectly well when executed from SQLLite command line client as "execute DoSomething;". So the problem actually is with the way it's being called from code and -not- the database. That is the complete statement, again, with only the actual name changed.
Wow. It's all about how this affects you and you alone, isn't it.
USAFHokie80 wrote:
The procedure signature is identical to what I've posted. Only the name of the procedure is different. And as I stated, the procedure itself works perfectly well when executed from SQLLite command line client as "execute DoSomething;". So the problem actually is with the way it's being called from code and -not- the database.
If all you're doing is calling the procedure by name and not passing any parameters (which your code suggests,) the error is in the stored procedure.
A guide to posting questions on CodeProject
How to debug small programs
Dave Kreskowiak -
Oh, I'm terribly sorry that I added another few hundred duplicate bytes the massive amount of data stored here. However will this site possibly be useful now that I've completely gummed it up???? What in incredibly silly thing to get upset about. Report me to the admins, quick! BTW, since I don't actually know the traffic on the individual discussions and since this question very clearly spans multiple topics, I don't see the problem here. So if you don't like my post and have nothing to contribute other than being a pompous jerk, feel free to pass over my posts.
He tried to help you.... End of the story.
thatraja
-
Wow. It's all about how this affects you and you alone, isn't it.
USAFHokie80 wrote:
The procedure signature is identical to what I've posted. Only the name of the procedure is different. And as I stated, the procedure itself works perfectly well when executed from SQLLite command line client as "execute DoSomething;". So the problem actually is with the way it's being called from code and -not- the database.
If all you're doing is calling the procedure by name and not passing any parameters (which your code suggests,) the error is in the stored procedure.
A guide to posting questions on CodeProject
How to debug small programs
Dave KreskowiakI cannot see how my asking this question could negatively affect anyone. It seems like people on here, like most of the internet, just want to complain about something. This is tantamount to chastising me for using an incorrect homonym in my sentences. And once again, the procedure runs flawlessly when execute from an Oracle client. If there were a problem with the code in the procedure I would have gotten an error. But, just to satisfy this useless endeavor, I'll indulge you.
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;
-
I cannot see how my asking this question could negatively affect anyone. It seems like people on here, like most of the internet, just want to complain about something. This is tantamount to chastising me for using an incorrect homonym in my sentences. And once again, the procedure runs flawlessly when execute from an Oracle client. If there were a problem with the code in the procedure I would have gotten an error. But, just to satisfy this useless endeavor, I'll indulge you.
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;
USAFHokie80 wrote:
If there were a problem with the code in the procedure I would have gotten an error. But, just to satisfy this useless endeavor, I'll indulge you.
Don't ever assume this. What you're doing in the "client" is not exactly the same as what you're doing from code. Also, what you do in the stored procedure declaration can also affect what you have to do in your code depending on which provider library you're using. For example, "CALL stored procedure name" in the "client" will not work if you're using Oracle's ODBC provider, but it works if you use MS's ODBC provider in your code. What does the connection string look like?
A guide to posting questions on CodeProject
How to debug small programs
Dave Kreskowiak
