I have a procedure in oracle that runs in about 40 mins when run from oracle. I have a pass through query in ms access that looks like this
Begin MyProcedure; End;
This exact code runs in oracle just fine but hangs in MS access. I don't know if it will finish, it's been going for 6 hours already and I guess it doesn't matter if it finishes or not, this is unacceptable. Can someone explain what the difference is between running it from oracle and MS access and how I can fix this
I presume MyProcedure is an Oracle stored procedure.
If that's so, I suggest you include logging into it. How? Create an autonomous transaction procedure (so that it could insert logging information into some log table and commit) and call it from MyProcedure, for example before every statement it contains (some nasty selects, updates, whatever). Doing so, you'd be able to trace MyProcedure's execution and see what takes that much time.
Apart from that, see whether there are uncommitted (or rolled back) transactions that hold certain rows (tables?) locked so - when you called MyProcedure - it waits for another session to commit (or roll back) in order to be able to continue its execution.