I have an ASP.NET application accessing an Oracle 12 database. I have written a stored procedure using a global temp table to help.
The global temp table is created with 'ON COMMIT DELETE ROWS'.
Basically the stored procedure does the following:
- Get some data. Add a char column. Insert to the global temp table.
- Use a key (in each row) to call another stored procedure.
- The stored procedure returns a 'Y' or 'N'.
- The return value will be updated to the corresponding row in the global temp table.
- Once done all rows, return like this:
open refcursor for select * from global_temp_table
I test the stored procedure. It works fine.
I then in my ASP.NET project, add another function in web service (asmx). I try to involve, but it says: "object not found".
I have read some posts and say change to 'ON COMMIT PRESERVE ROWS'. I don't want to leave data around. Is there any way to fix this issue? I have searched for sometime already.
[Edit] Other team mates have added many other stored procedures in database. They don't have to grant any right to it. The stored procedures are correctly executed when calling from web service. Of course, their sp do not use global temp table.
I have searched the web. I have seen similar posts out there. Calling an Oracle stored procedure using global temp table within ASP.NET will show the same error - object not found.
Anyway, I have found a way round. That is to use the WITH clause. Here is a reference link: https://oracle-base.com/articles/misc/with-clause
[/Edit]