using table types in HANA

[Former Member]

Hi,

I am new to hana.  My Task is to convert some oracle plsql procedures to hana stored procedures.  The oracle procedure uses objects and table objects as parameters.  I have the following questions:

1.  Can we declare objects in HANA like the way we do in oracle?

2.  How to store data into a variable from a table type IN parameter in a stored procedure ? Can somebody share the sample code ?

Thanks,

RENOO

[rama_shankar3 · Accepted solution]

Renoo:

Here is a sample:

CREATE TYPE DEVGUIDE.PCPTYPE AS TABLE (LOCATION NVARCHAR (60), ANNUAL_PRECIPITATION

REAL);

CREATE PROCEDURE DEVGUIDE.GETCOLDPRECIPITATION (IN LOW_TEMP INTEGER, OUT PRCPT

1. DEVGUIDE.PCPTYPE)

LANGUAGE SQLSCRIPT READS SQL DATA WITH RESULT VIEW DEVGUIDE.PROCVIEW AS

BEGIN

            PRCPT = SELECT LOCATION, ANNUAL_PRECIPITATION FROM DEVGUIDE.LOCAL_CLIMATE WHERE

            TEMPERATURE_LOW = :LOW_TEMP ;

END;

CALL DEVGUIDE.GETCOLDPRECIPITATION(-2, null );

Regards,

Rama

[Former Member · Accepted solution]

Hi Rama,

Thanks for the sample code.  I have a doubt here.  You have declared an OUT parameter PRCPT right ?  How do i see/access the contents of this table type.  ?

[former_member184768 · Accepted solution]

you can fire a SQL stmt like:

SELECT * FROM DEVGUIDE.PROCVIEW WITH PARAMETERS ('20')

Please refer to the SQL script manual (page 17) for the sample example.

Regards,

Ravi

[Former Member · Accepted solution]

Thanks Ravi. 

[rama_shankar3 · Accepted solution]

Thanks Ravi.

When you get a chance, please can you share your thoughts on a wierd error that I am getting using the procview?

Please review http://scn.sap.com/message/13224220 blog and respond.

Rama

[former_member223694 · Accepted solution]

Ravi,

I have a similar requirement, but not for a proc with OUT parameter , but IN parameter of Table type.

In oracle I can declare and populate a PLSQL Table (collection) variable and can pass that as parameter while invoking other procs.

Similarly, Is there a way to declare, initialize /populate and use a table type variable in hana SQL script?

If yes please let me know.

Thank you & Happy new year,

kind regards

K Sudhakaran

[Former Member · Accepted solution]

also,

is there a way to create table variable in custom code using odbc/jdbc and pass it to a stored procedure (as an IN param)

thanks

[former_member184768]

Hi Renoo,

If I understand correctly, you are looking for something like Object in Oracle which is nothing but a structure with column definitions. The object can later be used as TYPE and you can instantiate it to store data.

Secondly Objects in Oracle can also be used in the Table definitions.

You have a similar concept in HANA for Table Type, ONLY difference is you cannot store data in TABLE TYPE. You can use a defined table type in the procudure and can create objects to temporarily store data and NOT persist it in the database.

Also I don't think you can use Table types in Table definitions.

You can refer to the SQL Script manual for the examples of the same.

Regards,

Ravi

[Former Member]

THanks Ravi.

[Former Member]

Hello Ravindra,

I am trying to use the Table type to define an object which will hold data temporarily similar to internal table in ABAP. How can I use table type variable to store data from select statements when run in a loop?

Please advice.

Thanks

[yeushengteo]

Hi,

Maybe you can create a temporary table inside your procedure like below:

     CREATE LOCAL TEMPORARY TABLE #TMP_XX1 (var1 nvarchar(1))

Subsequently you can insert and select from the temporary table using SQL INSERT and SELECT statement just like a normal database table.

Regards.

YS