ORA-009000 Invalid SQL Statement when executing from code

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? 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 Using

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

Simon_Whale

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

USAFHokie80

Yes. This is a only a problem calling the procedure from code.

Richard Deeming

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

USAFHokie80

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.

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 Kreskowiak

USAFHokie80

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.

Dave Kreskowiak

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

thatraja

He tried to help you.... End of the story.

thatraja

Code converters | Education Needed | Improve EverythingNew

USAFHokie80

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)
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;

Dave Kreskowiak

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