8 Replies Latest reply: Nov 11, 2014 10:45 AM by Pawan Rawat RSS

LOCAL TEMPORARY TABLE in HANA Procedures

Martin Lindner
Currently Being Moderated

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

  • Re: LOCAL TEMPORARY TABLE in HANA Procedures
    Anooj Behanan
    Currently Being Moderated

    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

    • Re: LOCAL TEMPORARY TABLE in HANA Procedures
      Martin Lindner
      Currently Being Moderated

      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

      • Re: LOCAL TEMPORARY TABLE in HANA Procedures
        Anooj Behanan
        Currently Being Moderated

        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

  • Re: LOCAL TEMPORARY TABLE in HANA Procedures
    Steve Schaefer
    Currently Being Moderated

    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

  • Re: LOCAL TEMPORARY TABLE in HANA Procedures
    Artem Liman
    Currently Being Moderated

    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

    • Re: LOCAL TEMPORARY TABLE in HANA Procedures
      Pawan Rawat
      Currently Being Moderated

      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