Create local temporary table in procedure
I have a procedure (I wrote in the procedure editor in Project Explorer). In the procedure I'm trying to create a local temporary column table however when I attempt to activate the procedure I get error;
feature not supported: DDL is not supported in the READ ONLY procedure
In the past when I created a procedure via old method (via CONTENT/NEW PROCEDURE) I had the option of choosing READ or READ/WRITE. I'm not seeing this option in the new procedure editor. Also I noticed a second tab next to SQLScript tab called LOCAL TABLE TYPES. I can't seem to place my create local temporary table there either.
I've read in other posts that this is possible... what do I need to do in order to be able to create and update this temp table?
Hey Patrick, I have hit this a number of times and left scratching my head - so I have learned to look for this right away if I am using tables in new schemas or my personal schema.
Anything executed out of the repository is executed using the SYS_REPO user and not your own ID. Therefore, SYS_REPO must be able to "pass on" the privileges that he is assigned. This is achieved by making the privileges 'grantable' as you show.
In the case of analytic views, SELECT must be grantable on whatever schemas are involved. In the case of read/write procedures, the INSERT/DELETE/UPDATE must be grantable on whatever schemas are involved.
I can't comment on the local temporary table as I have not used it quite yet.