Unanswered: Assigning Table permissions in stored procedures?!
I have an oracle stored procedure that has an execute immediate command to create a table. Is there anyway in the coding of the stored procedure, I can ENSURE that regardless of the type of user that is trying to execute the sp that they be allowed permission to create the table?
I need to maintain the ability of creating and populating the table in the stoerd procedure I need to be able to manipulate the results after I execute this stored procedure in my application. And after that I would like to execute another stored procedure to drop the table.
(Sounds like I should have used a ref cursor but I would like to be able to find out if I can somehow do this in the code first)
I tried the execute immediate 'grant all on equipment to public'; command after the creation of the stored procedure but that of course didnt work.
I wonder would creating a global temporary variable work or will that table be dropped as soon as I am done executing the sp??
A global temporary table is never dropped. Onlythe data is dropped when a commit if issued, or the session drops (selectable). The data would NOT disappear from the GTT when the procedure completes as long as the call to the procedure is made in the same session.
You do not need a parachute to skydive. You only need a parachute to skydive twice.
"I need to maintain the ability of creating and populating the table in the stoerd procedure"
If you use EXECUTE IMMEDIATE to create the table, the you need to only use the EXECUTE IMMEDIATE again to populate the data (using an INSERT may be). I feel that the approach you are taking is round about. Also in multi-user/multi-session environments, there may be possibility of duplicates and hence errors. It is better to create a GTT once in the database and use it across sessions (with Oracle having to take care of data truncation at end of session) without having to DROP it again. Data manipulation can then be handled using Ref Cursors.