Create local temporary table in procedure

[#1]

Hi folks,

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?

Thanks,

-Patrick

[lbreddemann]

Hi Patrick,

could it be that the sqlscript_mode parameter on your development instance is not set to UNSECURE?

- Lars

[patrickbachmann]

Hi Lars,

I just double checked our configuration and we currently have it set to UNSECURE.  Any other ideas?

-Patrick

[lbreddemann]

Hi Patrick,

that's weired.

I just tested it on rev. 70:

/********* Begin Procedure Script ************/
BEGIN


    create local temporary table #mytemp (id integer, name varchar(20));

    insert into #mytemp values (1, 'Lars');

    select * from #mytemp;
END;
/********* End Procedure Script ************/

call _sys_bic."lars.LBTEST/TEMPTABDEMO"

ID|NAME

1 |Lars

Just worked as expected...

Do you get any other error messages (maybe in the activation logs)?

- Lars

[patrickbachmann]

Lars, also I used your example (although I seem to have to have declarations section at top - maybe you are using hdbprocedure which I dont seem to have in rev68).  Anyway with this code below I get same authorization error;

CREATE PROCEDURE LARSEXAMPLE ( )

LANGUAGE SQLSCRIPT

  SQL SECURITY INVOKER

  AS           

/********* Begin Procedure Script ************/

BEGIN

    create local temporary table #mytemp (id integer, name varchar(20));

    insert into #mytemp values (1, 'Lars');

    select * from #mytemp;

END;

/********* End Procedure Script ************/