0

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]

Just a HK developer
  • 680
  • 12
  • 29
  • if the object not found is coming from the database, ti means your code isn't finding the stored procedure. Make certain that within your asp.net code the procedure is properly qualified with the schema name, and make certain the account you are using to access Oracle has execute privileges on the procedure: `grant execute on myschema.proc to webusr;` – Brian Leach Dec 06 '18 at 03:08
  • Brian, other team mates of mine have added various stored procedures. They have used web services to call those sp and yet they have not encountered any permission issue. (Of course, theirs do not use global temp table.) – Just a HK developer Dec 07 '18 at 07:52

0 Answers0