cancel
Showing results for 
Search instead for 
Did you mean: 

LOCAL TEMPORARY TABLE in HANA Procedures

martin_lindner
Explorer
0 Kudos

Hi experts,

I have a question about temporary tables insight SAP HANA Procedures. If I use the local SQL editor in the following way it works:

create local temporary table #test_table(mandt VARCHAR(3),type_id VARCHAR(4));

insert into #test_table values('001','Dum');
insert into #test_table values('002','Dum2');

     SELECT
       mandt,
       type_id
     FROM #test_table
     WHERE mandt = '001';

But now I want to use the same insight an procedure with edit rights. So I wrote down the following:

create procedure "_SYS_BIC"."d052319/DEMO_PROC" ( in MANDT VARCHAR(3) ,
                                                                                    out OUTPUT_TABLE "_SYS_BIC"."d052319/DEMO_PROC/tabletype/OUTPUT_TABLE" )

language SQLSCRIPT sql security definer  as

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

create local temporary table #test_table(mandt VARCHAR(3),type_id VARCHAR(4));

insert into #test_table values(mandt,'Dum1');
insert into #test_table values('002','Dum2');

output_table =
     SELECT
       mandt,
       type_id
     FROM #test_table
     WHERE mandt = mandt;

END
;

But by execution I got the error that the table #test_table doesn't exist. The same thing comes also if you create a table in the procedure and try to directly access them. But is there any possibility to say use the created table in the line before?

The error message if you need it for more details was:

Could not execute 'create procedure "_SYS_BIC"."d052319/DEMO_PROC" ( in MANDT VARCHAR(3) , out OUTPUT_TABLE ...'

SAP DBTech JDBC: [259] (at 353): invalid table name:  Could not find table/view #TEST_TABLE in schema D052319: line 9 col 15 (at pos 353)

Best regards and thanks to all of you that have helpfull answears,

Martin

Accepted Solutions (1)

Accepted Solutions (1)

Former Member

Hi Martin,


Did a bit of testing around this. The conclusion is you cannot use local temporary tables within procedures - this is not supported. If it did you would end up with the following problems:

a. In the Current Session - You can only call the procedure once. When you call it a second time, it will error as the table already exists and it cannot be created again. You can get around this by having a drop statement before the create statement but then you'll end up with problem (b).

b. in the Next session - You wouldn't be able to call the procedure at all since the drop statement will fail as the temporary table wouldn't exist at that point.

You could instead use a global temporary table (only metadata is persistent) or a normal table (both metadata and data persists).

You could either have the create global temporary table outside the procedure and just the insert statements inside Or have the "create" inside with a 'drop table' statement before so that you don't get an error while calling the procedure second time around.

Hope that helps.

Thanks,

Anooj

martin_lindner
Explorer
0 Kudos

Hi Anooj,

the reason why I want to use a local temporary is that I want to insert a evaluation path inside for one user. So I need a way where I could store it in a table ordered and I didn't want to make it persistent because I have to recalculate after each user decision.

Have you any solutions for that?

Best regards,

Martin

Former Member
0 Kudos

Hi Martin,

As suggested in my previous response, can you not use a global temporary table instead to meet your requirements?

Remember, only the metadata of global temporary tables is persisted. Actual data content is deleted/truncated when you leave the HANA session.

An example would be:

create global temporary table "SYSTEM"."test_table" (mandt VARCHAR(3),type_id VARCHAR(4));

create procedure "SYSTEM"."DEMO_PROC"(in MANDT VARCHAR(3),

out OUTPUT_TABLE "SYSTEM"."OUTPUT_TABLE" )

language SQLSCRIPT sql security definer  as

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

BEGIN

truncate table "SYSTEM"."test_table";

insert into "SYSTEM"."test_table" values(:mandt,'Dum1');

insert into "SYSTEM"."test_table" values('002','Dum2');

output_table =

     SELECT

       mandt,

       type_id

     FROM "SYSTEM"."test_table"

     WHERE mandt = :mandt;

END;

Thanks,

Anooj

Answers (2)

Answers (2)

Former Member

Hi,

For those who landed here while searching on temporary table topic:  this limitation of local temporary tables has been fixed in current version of Hana, so that local temp tables can be created within the stored procedure. They should be dropped upon session completing automatically, but It would not hurt to drop them at the end of procedure. Also, make sure to use right type of temp tables - row or column based. This will matter if you use such temp table in join with different type of persisted table. Because of different engines being used for different stores, the data transfer between engines will affect the performance on a bigger amounts of data

Steve,  transaction scope depends how you execute command. You can initiate transaction from your application. Then you define the scope of transaction by starting it and either committing or rolling back.

Also, every hana stored procedure has its own transaction scope when triggered w/o externally initiated transaction. And stored procedure at the top of the calling stack defines its scope then. All nested SP calls will be bound to that transaction. I think SQL Server works precisely the same way


Artem

Former Member
0 Kudos

Hello Artem,

i want to know that can't we use" ceate type tab1()  as table" command inside the procedure or function. However  it raises a question of what would be the data type of the output variable aswell. i tried using it inside a procedure and it raised an error that i can't use the create type inside the procedure but just want to make sure i didn't do any mistake.

thanks for sharing knowledge !

pawan

Former Member
0 Kudos

Hi Pawan,

Yes, it seems you can't call directly create type from within the SP.

But good news is that you can cheat it by executing dynamic SQL, see example below:

create procedure tempTest

as

begin

  exec 'CREATE TYPE tt_test AS TABLE (col int)';

end;

call tempTest();

create procedure tempTest3 (para tt_test)

as

begin

  select * from :para;

end;

create procedure tempTest2 ()

as

begin

  d = select 1 col from dummy;

  call tempTest3(:d);

end;

call tempTest2();

drop type tt_test;

drop procedure tempTest;

drop procedure tempTest2;

drop procedure tempTest3;

 

Hope it helps,

Artem

Former Member
0 Kudos

Thanks for the information Artem.

Former Member
0 Kudos

This message was moderated.

former_member672402
Discoverer
0 Kudos

Hi,
yes, i think the limitation w.r.t creating local temporary tables is solved in the latest version. But, i have noticed an issue while executing procedure. Here is the problem description :

I am creating a looping procedure and a wrapping procedure. Wrapping SP calls looping SP multiple times, which then run in the same session. However, when each run of the looping procedure completes, the consumed memory during that last execution still appears under wrapping procedure, though that execution is complete.
I did observe that DROP local temporary tables wont free up memory, thus i used DELETE instead, but it doesn't appear to make any difference and the memory until the last executions keeps adding on the wrapping procedure.

Is there any solution available to this ?

0 Kudos

Greetings,

This post answered many of my questions - thanks. One thing that didn't get answered is what is the scope of a HANA session? What defines the beginning and ending of a session?

I need to be sure that if I have a global table that for instance has a single VARCHAR(3) field that stores an error code and session one didn't encounter an error and session two did encounter an error that the two sessions don't interfere with each other while they are both active - i.e. each session gets it's own unique global table instance.

Were going to use HANA in a transactional appl so the procedure could be called multiple times by multiple sessions.

Thanks in advance,

Steve