Skip to Content

Archived discussions are read-only. Learn more about SAP Q&A

LOCAL TEMPORARY TABLE in HANA Procedures

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

Tags:
replied

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

2 View this answer in context

Helpful Answer

by
Not what you were looking for? View more on this topic or Ask a question