How to define ARRAY of varying length?

Database

How to define ARRAY of varying length?

Hi All,

 I need to create a ARRAY of VARCHAR(100) of varying length whose length would be defined at runtime. The requirement is to have this UDT as INPUT of a stored procedure which will be invoked by a Java code, that will be passing arrays of varying length from time to time. It seems to me that ARRAY in Teradata must be defined with a fixed length. Can someone please suggest how to define ARRAY of varying length whose length is not defined during creation of the UDT.

Thanks,

Avi

1 REPLY
Enthusiast

Re: How to define ARRAY of varying length?

Hi Avi,

I didn't understand the Question Properly. The question from you must be The Array is of size VARCHAR(100) in length and the no of Array Elements would be passed in Run Time. The Array Syntax is,

CREATE TYPE numbers_ary AS VARCHAR(100) ARRAY[5];

Your query can be solved by using Procedure. You can get the Parameter for the Arrayelement an you can construct the Procedure like this,

CREATE PROCEDURE PROC1(IN VALUE INTEGER, OUT P2 INTEGER)

BEGIN

DECLARE QUER VARCHAR(100); 

QUER='CREATE TYPE numbers_ary VARCHAR(100) ARRAY['||:VALUE'||'];'

CALL DBC.SYSEXECsql(QUER);

SET P2=SQLCODE

END;

This Procedure, will create the Variable length Array. 

Thanks & Regards,

Adharssh.