All Forums Database
juanalfonso 41 posts Joined 01/16
05 Feb 2016
How to define an array of varchars in Stored Procedure Language?

Hello to everybody,

I'm studying a migration from Oracle PL-SQL to Teradata Stored Procedure Language and I'm having problems trying to find a way to declare an array of varchars inside procedures/functions.

Oracle already has a type "dbms_sql.varchar2_table" and I just have to declare an instance and use it, and you don't even have to specify a length. Also I have the alternative of creating my own type "TYPE array_cadena is table of varchar2(252) index by binary_integer" and instance it afterwards as well.

I've seen that a type "SYSUDTLIB.array_strings" exists, but I have problems with it (I can't declare an instance inside the procedure/function; if I do it outside, I have problems of UDTUSAGE access; etc...).

Any help with it?

Thanks and best regards

dnoeth 4628 posts Joined 11/04
08 Feb 2016

Arrays in Teradata must be defined/created before you can use them, I know that's annoying.
If "SYSUDTLIB.array_strings" doesn't match your needs you must CREATE a new array type. Your user needs a UDTTYPE grant on SYSUDTLIB to be able to CREATE TYPE and UDTUSAGE on SYSUDTLIB (or the specific type) to instance/use it.
 
Btw, you should double check if you really need arrays, in lots of cases PL-SQL logic can/should be rewritten using set-based logic, see George Coleman's blog
 

Dieter

juanalfonso 41 posts Joined 01/16
08 Feb 2016

Thanks for the answer.
About what I wrote on my post, I have to say that the type "SYSUDTLIB.array_strings" is one that I created myself, it doesn't exist by default, sorry for the mistake.
Anyway, I proceeded to create my new array type:

CREATE TYPE SYSUDTLIB.array_strings AS VARCHAR(252) ARRAY[9];

And i gave to the user all the privileges you told me:

GRANT UDTTYPE ON SYSUDTLIB TO user_novatd;
GRANT UDTUSAGE ON SYSUDTLIB TO user_novatd;

In the procedure code I have the following:

[...]

DECLARE salida array_strings Default NULL;

Set salida[1] = 'Calle';

Set salida[2] = 'Pepe';

Set salida[3] = '5';

Set salida[4] = 'Bar El Arbusto';

Set salida[5] = 'B';

Set salida[6] = 'Derecha';

Set salida[7] = '5';

Set salida[8] = 'A';

Set salida[9] = '25';
[...]

But when I execute it, I receive the following error:

REPLACE PROCEDURE Failed. [5526] SPL5000:(WL70). E(3523): An owner referenced by user does not have UDTUSAGE access to SYSUDTLIB.array_strings

What I have to do to not have the error? And also, is it possible to create an array type with a non-defined length?
One of the uses I want it for is reading a string and separate the words found in an array of strings. For example:
If I have an input like "word1 word2 word3", I would get an array of words where:

array(1) = 'word1'
array(2) = 'word2'
array(3) = 'word3'

Thanks and regards

dnoeth 4628 posts Joined 11/04
08 Feb 2016

You need to grant the rights to the database where the SP is created in (or change the SECURITY option), this is the same for Views/Macros.
But you might not need an array at all, there's a nice STRTOK_SPLIT_TO_TABLE function, see
http://forums.teradata.com/forum/database/split-the-column-which-is-delimited-into-separate-rows#comment-138002
 
If the splitting rules are more complicated you can use REGEX_SPLIT_TO_TABLE

Dieter

juanalfonso 41 posts Joined 01/16
08 Feb 2016

Sorry but, because of the way the procedures are already done in Oracle PL-SQL, I think i'll have to continue with the idea of ussing arrays, due to that's the most direct and easy way to translate it, but thanks for the alternatives exposed.

My user is "user_novatd" and the database is "novatd".

I've executed this:

GRANT UDTTYPE ON SYSUDTLIB TO user_novatd;

GRANT UDTUSAGE ON SYSUDTLIB TO user_novatd;

But it doesn't seem to be enough for having the privileges, cos I receive the error message I told you in my previous post.

Am I missing something? What else I have to do to grant the rights to "novatd"?

dnoeth 4628 posts Joined 11/04
08 Feb 2016

You need to GRANT UDTUSAGE ON SYSUDTLIB TO novatd WITH GRANT OPTION;
 
Regarding alternatives, if PL-SQL utilizes non-set-based syntax like cursors processing data you will notice that rewriting them might run 100 to 10000x faster. A sequential FETCH NEXT is worst case in a parallel DBMS.

Dieter

juanalfonso 41 posts Joined 01/16
08 Feb 2016

Thanks for your quick reply. I haven't used cursors in Oracle yet, but maybe I'll do it in Teradata, so it's good to know.

After giving the privileges, now I can define an instance of the array type but I'm having problems to fill it. I think I read somewhere that the way to fill every element of the array was:

Set salida[1] = 'Calle';

But that is giving me a syntax error... Can you tell me which is the correct syntax to fill it? I've been looking on internet and manuals and I can't find anywhere where they explain it...

Thanks again!

dnoeth 4628 posts Joined 11/04
08 Feb 2016

I never needed to set one specific value in an array (In fact I hardly used them at all), but it seems like this SET array[x] syntax is not supported outside of a SQL Update.
You can set all strings at once like

SET salida = NEW arr_string('Calle','Pepe','5','Bar El Arbusto','B','Derecha','5','A','25');

but I assume you want to do this in a kind of loop.

Dieter

juanalfonso 41 posts Joined 01/16
09 Feb 2016

Yes, I want to use it in loops... and try to maintain the estrcuture of the original Oracle code as much as possible.

I'm using arrays:

  • As "in" and "out" parameters for functions/procedures
  • To store words from a string given as a "in" parameter (indefined number of words)
  • I'm using loops to treat the words and compare them with elements in tables or regular expressions

Here I can show you some pieces of code:

      IF cAbrtivia IN ('C', 'AV', 'CTRA', 'AUTO', 'AVIA') AND (X > nX) AND (X <= nPalabras) AND cCodVernac = 'VAS' THEN

        -- cAbrtivia is already filled with the road type
        cBaktVia := mCadena(X); -- We do a backup of the original word
        mCadena(X) := ''; -- We supress the word from the input array
        exit; -- We leave the loop cos the treatment is done
ELSIF cAbrtivia IN ('C', 'AV', 'CTRA', 'AUTO', 'AVIA') AND (X > nX) AND (X < nPalabras) AND
         NOT(mCadena(X) = 'C' AND F_HAY_NUMERO(mCadena(X-1))) AND
         NOT(mCadena(X) = 'C' AND mCadena(X+1) IS NOT NULL AND mCadena(X+1) = 'C') AND
         NOT(mCadena(X) = 'C' AND mCadena(X-1) IS NOT NULL AND mCadena(X-1) = 'C') AND
         NOT(cAbrtivia = 'AV' AND (
           regexp_like (mCadena(X-1), '^(P+R+I+M+E+I*R+A*|L+E+H+E+N+)$') OR
           regexp_like (mCadena(X-1), '^(S+E+G+U+N+D+A+|S+E+G+O+N+A+|B+I+A+R+R+E+N+)$') OR
           regexp_like (mCadena(X-1), '^(T+E+R+C+E+I*R+A*|H*I+R+U+G+A+R+E*N+)$') OR
           regexp_like (mCadena(X-1), '^((C+|Q+)U+A+R+T+A+|L+A+U+G+A+R+E*N+)$') OR
           regexp_like (mCadena(X-1), '^(Q+U+I+N+T+A+|C+I+N+(Q+|C+)U+E+N+A+|B+O+S+G+A+R+E*N+)$') OR
           regexp_like (mCadena(X-1), '^(S+E+(X+|S+)T+A+|S+I+S+E+N+A+|S+E*I+G+A+R+E*N+)$') OR
           regexp_like (mCadena(X-1), '^(S+E+P*T+I+M+A+|S+E+T+E+N+A+|Z+A+Z+P+I+A+R+E*N+)$') OR
           regexp_like (mCadena(X-1), '^(O+C+T+A+V+A+|V+U+I+T+E+N+A+|O+I*T+A+V+A+|Z+O+R+T+Z+I+G+A+R+E*N+)$') OR
           regexp_like (mCadena(X-1), '^(N+O+V+E+N+A+|B+E+D+E+R+A+T+Z+I+G+A+R+E*N+)$') OR
           regexp_like (mCadena(X-1), '^(D+E*C+I+M+A+|D+E+S+E+N+A+|H+A+M+A+R+G+A+R+E*N+)$') OR
           regexp_like (mCadena(X-1), '^(G+R+A+N+|H*A+N+D+I+A+)$') OR
           regexp_like (mCadena(X-1), '^(N+U+E+V+A+|N+O+V+A+)$')))
      THEN -- Si encontramos tipo de vía
        
        -- We do a loop to reallocate all the previous words to the road type to the end its actual possitions are set to NULL, and we leave a gap for the mark of Rest of road ("#")
        FOR Y IN 1..X-nX LOOP
          mCadena(nPalabras+Y+1) := mCadena(nX+Y-1); -- We move the word to the end
          mCadena(nX+Y-1) := ''; -- We supress the word from its old position
        END LOOP;
        
        bCambiaOrdenDir := true; -- We indicate that the address has been redistributed

I've seen that there is some built-in C functions in Teradata to manage arrays, but I can't find how to invoke them properly from an SQL procedure/function... Do you know some web link where they explain it?

Also I had a look about the  STRTOK_SPLIT_TO_TABLE, but it seems to have to use always a table as input and not just a varchar variable. And anyway, when I get the string in pieces, where could I store them? ... cos apparently I can't create a volatile or normal table in a process and inmediatedly use it, cos Teradata returns an error saying that the table does't exist if I'm using it afterwards.

I don't see much chance of maintainning my array types. I'm even thinking of going through the string using the combination of INSTR and SUBSTR Teradata functions based on blanks...

Any suggestion or advice?

Thanks

dnoeth 4628 posts Joined 11/04
09 Feb 2016

- The C-functions to access arrays are ment to be used in a C-UDF only (which would be better suited to do logic in complicated loops than SQL).
 
- STRTOK_SPLIT_TO_TABLE can be used on a variable without a table:

SELECT ARRAY_AGG(token ORDER BY tokennum, array_strings()) 
FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, '234,244,1,2,3', ',')
     RETURNS (outkey INTEGER,
              tokennum INTEGER,
              token VARCHAR(20) CHARACTER SET UNICODE)
           ) AS dt 

 
- If you create a Volatile Table within a SP it's doesn't exist, but there should be only a warning message and the SP should be created:

REPLACE PROCEDURE testproc (IN a array_strings, OUT a2 array_strings)
BEGIN
   
   BEGIN
      DECLARE CONTINUE HANDLER FOR SQLSTATE '42000'  -- error 3807 table doesn't exist
      BEGIN
      END;
      DROP TABLE vt;
   END;   

   CREATE VOLATILE TABLE vt 
    ( id INT NOT NULL  PRIMARY KEY,
      strings array_strings
    ) ON COMMIT PRESERVE ROWS
   ;
   INSERT INTO vt(1, :a)
   ;
   UPDATE vt SET strings[3] = 'bla'
   WHERE id  = 1
   ;   
   SELECT strings INTO :a2
   FROM vt
   WHERE id = 1
   ;
END;

CALL testproc(NEW array_strings('234','244','1','2','3'), a2);

 
- With an array in a Volatile Table you could keep your looping logic. It's not efficient, but this will not be done on a larger number of rows (hopefully).
Can you elaborate what this logic is actually doing?
 

Dieter

juanalfonso 41 posts Joined 01/16
10 Feb 2016

I think that is a good idea and it could work for me :)

I've executed your procedure "testproc" and the message of table not existing comes as an error, so I can't create the procedure.

I've been reading a post where you participated ("https://forums.teradata.com/forum/database/stored-procedure-with-select-on-table-that-is-creatde-in-runtime-0"), so the problem apparently is that the database was created with the user "DBC" and now I'm working over it with another user...

  • REPLACE PROCEDURE Failed. [5526] SPL5000:W(L8). E(3807): Object 'vt' does not exist

But I've tried to execute the procedure with the user "DBC" and the same error comes up.

Anyway, the ideal should be if I could work with the new user...

Do you know what is missing on here?

Thanks again

dnoeth 4628 posts Joined 11/04
10 Feb 2016

I just checked it, for SQL SECURITY DEFINER (which is the default) it runs fine when you create the SP within your own user and fails when it's in a different database. For SQL SECURITY OWNER/CREATOR/INVOKER it always compiles without warning/error.
If you need DEFINER the workaround is to create the VT once before you run REPLACE PROCEDURE.

Dieter

juanalfonso 41 posts Joined 01/16
10 Feb 2016

Ok. At the moment, supposedly, I can do whatever I need, so I've just redefined the head of the procedure adding "SQL SECURITY CREATOR" and now it works!

As a last test, I've tried to introduce at the end of the procedure "testproc" the "STRTOK_SPLIT_TO_TABLE" select as following:

 

REPLACE PROCEDURE testproc (IN a array_strings, OUT a2 array_strings, OUT a3 array_strings) SQL SECURITY CREATOR
BEGIN
   [...]
   SELECT ARRAY_AGG(token ORDER BY tokennum, array_strings()) INTO :a3 -- I'm introducing the ARRAY_AGG(...) output
   FROM TABLE (STRTOK_SPLIT_TO_TABLE(1, 'a,b,c,d,e', ',')              -- in a new OUT array parameter a3
      RETURNS (outkey INTEGER,
               tokennum INTEGER,
               token VARCHAR(20) CHARACTER SET UNICODE)
              ) AS dt
    ;
END;

And, although it compiles ok, when I make the call:

CALL testproc(NEW array_strings('234','244','1','2','3'), a2, a3);

I receive the error:

CALL Failed. [9881] TESTPROC: Function 'ARRAY_AGG' called with an invalid number or type of parameters

I assume the ARRAY_AGG must be well called and the problem is in the "INTO :a3". I change it just to "INTO a3" but I receive the same error message.

Do I have to use any type of cast or anything to make it work?

Thanks again and regards

 

dnoeth 4628 posts Joined 11/04
10 Feb 2016

I think you found a bug :)
 
I just checked the actual SQL submitted in dbc.QryLogV:
...TD_SYSFNLIB.ARRAY_AGG ( token , NEW array_strings ( ), tokennum , '1') FROM TABLE(TD_SYSFNLIB.STRTOK_SPLIT_TO_TABLE...
The bold part is not valid and returns the error. Similar when used in a View or Macro.
 
There's no workaround for things like that (this code is created internally by the parser), you must open an incident with Teradata support and let them fix it.

Dieter

venkat3107 4 posts Joined 12/11
10 Feb 2016

I have the similar requirment to convert Oracle SP in to TD. Here is my requirement:

 

 

DECLARE

 TYPE var_array_type IS TABLE OF VARCHAR(40)

   INDEX BY binary_integer;

 date_list var_array_type;

 date_num_list var_array_type;

 date_num VARCHAR(5);

 sql_stmt  VARCHAR2(32000);

 

BEGIN

 

-- Place all the month_end_dates available within customer foundation into a list

select month_end_date BULK COLLECT INTO date_list from mktg_elicit.distinct_month_end_dates;

 

-- Create array of 4 digit numbers, first two representing the year and last two representing month of segmentation

select SUBSTR(month_end_date, 8, 2) || to_char(to_date(month_end_date), 'mm' ) BULK COLLECT INTO date_num_list

from mktg_elicit.distinct_month_end_dates;

 

FOR i IN date_list.first..date_list.last LOOP

 

-- On the first iteration, create a table just with x_mem_num, segment in April 2013 and May 2013

IF i = 1 THEN

 

sql_stmt := 'create table temp_mig_clv' || date_num_list(i+1) || ' as '

|| 'select /*+ full(a) parallel(a,4) full(b) parallel(b,4) */ '

||  'coalesce(a.x_mem_num, b.x_mem_num) as x_mem_num '

|| ',segment_' || date_num_list(i) || ' '

||  ',segment_' || date_num_list(i+1) || ' '

|| ',clv_' || date_num_list(i) || ' '

||  ',clv_' || date_num_list(i+1) || ' '

|| ',cy_flyer_flg_' || date_num_list(i) || ' '

||  ',cy_flyer_flg_' || date_num_list(i+1) || ' '

|| ',flt_rev_1yr_' || date_num_list(i) || ' '

||  ',flt_rev_1yr_' || date_num_list(i+1) || ' '

|| ',onds_1yr_' || date_num_list(i) || ' '

||  ',onds_1yr_' || date_num_list(i+1) || ' '

|| 'from '

|| '(select x_mem_num '

|| ',segment_assigned as segment_' || date_num_list(i) || ' '

|| ',ba132_swa_clv as clv_' || date_num_list(i) || ' '

||  ',case when ba183_1yr_ond > 0 then 1 else 0 end as cy_flyer_flg_'  || date_num_list(i) || ' '

||  ',ba187_1yr_flt_base_rev as flt_rev_1yr_' || date_num_list(i) || ' '

||  ',ba183_1yr_ond as onds_1yr_' || date_num_list(i) || ' '

|| 'from mktg.wc_mktg_mem_bhvr_attrib_dx '

|| 'where month_end_date = ''' || date_list(i) || ''' ) a '

|| 'full join '

|| '(select x_mem_num '

|| ',segment_assigned as segment_' || date_num_list(i+1) || ' '

|| ',ba132_swa_clv as clv_' || date_num_list(i+1) || ' '

||  ',case when ba183_1yr_ond > 0 then 1 else 0 end as cy_flyer_flg_'  || date_num_list(i+1) || ' '

||  ',ba187_1yr_flt_base_rev as flt_rev_1yr_' || date_num_list(i+1) || ' '

||  ',ba183_1yr_ond as onds_1yr_' || date_num_list(i+1) || ' '

|| 'from mktg.wc_mktg_mem_bhvr_attrib_dx '

|| 'where month_end_date = ''' || date_list(i+1) || ''' ) b '

|| 'on a.x_mem_num = b.x_mem_num ';

 

EXECUTE IMMEDIATE sql_stmt;

--dbms_output.put_Line (sql_stmt);

 

-- On every other iteration (except for last), full join one more month of segmentation as a field the the previous table

-- For example, on the second iteration, full join x_mem_num and segment_assigned as of June 2013 to a table with April 2013 and May 2013

ELSIF i < date_list.last THEN

 

sql_stmt := 'create table temp_mig_clv' || date_num_list(i+1) || ' as '

|| 'select /*+ full(a) parallel(a,4) full(b) parallel(b,4) */ '

||  'coalesce(a.x_mem_num, b.x_mem_num) as x_mem_num ';

 

FOR j IN 1..i LOOP

sql_stmt := sql_stmt || ',segment_' || date_num_list(j) || ' '

|| ',clv_' || date_num_list(j) || ' '

|| ',cy_flyer_flg_' || date_num_list(j) || ' '

|| ',flt_rev_1yr_' || date_num_list(j) || ' '

|| ',onds_1yr_' || date_num_list(j) || ' ';

END LOOP;

 

sql_stmt := sql_stmt || ',segment_' || date_num_list(i+1) || ' '

|| ',clv_' || date_num_list(i+1) || ' '

|| ',cy_flyer_flg_' || date_num_list(i+1) || ' '

||  ',flt_rev_1yr_' || date_num_list(i+1) || ' '

||  ',onds_1yr_' || date_num_list(i+1) || ' '

|| 'from '

|| '(select * from temp_mig_clv' || date_num_list(i) || ' ) a '

|| 'full join '

|| '(select x_mem_num '

|| ',segment_assigned as segment_' || date_num_list(i+1) || ' '

|| ',ba132_swa_clv as clv_' || date_num_list(i+1) || ' '

||  ',case when ba183_1yr_ond > 0 then 1 else 0 end as cy_flyer_flg_'  || date_num_list(i+1) || ' '

||  ',ba187_1yr_flt_base_rev as flt_rev_1yr_' || date_num_list(i+1) || ' '

||  ',ba183_1yr_ond as onds_1yr_' || date_num_list(i+1) || ' '

|| 'from mktg.wc_mktg_mem_bhvr_attrib_dx '

|| 'where month_end_date = ''' || date_list(i+1) || ''' ) b '

|| 'on a.x_mem_num = b.x_mem_num ';

 

EXECUTE IMMEDIATE sql_stmt;

--dbms_output.put_Line (sql_stmt);

 

-- Clean up all the tables by dropping ones we don't need anymore

sql_stmt := 'drop table temp_mig_clv' || date_num_list(i) || ' ';

EXECUTE IMMEDIATE sql_stmt;

--dbms_output.put_Line (sql_stmt);

 

-- On the final iteration, create a permanent table called mktg_elicit.migration history and drop the final temp table

ELSE

 

 

sql_stmt := 'drop table migration_history ';

EXECUTE IMMEDIATE sql_stmt;

--dbms_output.put_Line (sql_stmt);

 

sql_stmt := 'create table migration_history as '

|| 'select * from temp_mig_clv' || date_num_list(i) || ' ';

EXECUTE IMMEDIATE sql_stmt;

--dbms_output.put_Line (sql_stmt);

 

sql_stmt := 'drop table temp_mig_clv' || date_num_list(i) || ' ';

EXECUTE IMMEDIATE sql_stmt;

--dbms_output.put_Line (sql_stmt);

END IF;

END LOOP;

 

END;

 

 

 

mktg_elicit.distinct_month_end_date contains distinct month end dates.

 

Can some one please guide me how to convert this SP in to TD.

juanalfonso 41 posts Joined 01/16
11 Feb 2016

Hello again,

I've been reading in the web "http://www.info.teradata.com/" the next short article:

  • http://www.info.teradata.com/htmlpubs/db_ttu_14_00/index.html#page/SQL_Reference/B035_1143_111A/Array_Data_Type.048.01.html

And the following phrase made me think that maybe there can be a more simplified way to make the stuff that Venkat or I are looking for:

"You can also create an ARRAY data type using the VARRAY keyword and syntax for Oracle compatibility"

But when I try, I can't find the way to do it like in Oracle... and I don't find any examples or information about this "VARRAY" data type.

So the big question is... How can VARRAY's been used to have the mentioned "syntax for Oracle compatibility"?

Thanks again and regards

CarlosAL 512 posts Joined 04/08
11 Feb 2016

Hi.
You can define VARRAY types a-la-Oracle:
CREATE TYPE The_Type AS VARRAY(n) OF type:
CREATE TYPE MY_VARRAY AS VARRAY(10) OF CHARACTER(15);
 
Oracle can create this VARRAYs on the fly (no CREATE TYPE required):
DECLARE
TYPE MY_ARRAY IS VARRAY(10) OF CHAR(15);
 
Cheers.
Carlos.

juanalfonso 41 posts Joined 01/16
11 Feb 2016

But, is there a way with this VARRAY data type in Teradata to do things like the following in a simple way?:

  • MY_ARRAY(2) = 5
  • MY_ARRAY(2) = MY_ARRAY(3)

Because with Oracle you can do it...

Thanks

CarlosAL 512 posts Joined 04/08
11 Feb 2016

Hello.
Oracle is Oracle. Teradata is Teradata.
As Dieter said, maybe you don't even need them. PL/SQL tends to row-at-a-time programming and overprogramming.
In Teradata you cannot modify one element on the array using a simple '='.
One workaround could be:
 
 
DECLARE MY_ARRAY1 MY_VARARRAY;
DECLARE MY_ARRAY2 MY_VARARRAY;
SET MY_ARRAY1 = NEW MY_VARARRAY('1','5','3');
SET MY_ARRAY2 = MY_ARRAY1.ARRAY_UPDATE(MY_ARRAY1.ARRAY_GET(3)),2);
 
 
MY_ARRAY2 results in ('1','3','3')
HTH.
Cheers.
Carlos.

CarlosAL 512 posts Joined 04/08
11 Feb 2016

Hi again.
Forgot to mention:
You can do it with just one instance of the array:
 
DECLARE MY_ARRAY1 MY_VARARRAY;
SET MY_ARRAY1 = MY_ARRAY1.ARRAY_UPDATE(MY_ARRAY1.ARRAY_GET(3)),2);
 
The point is that ARRAY_UPDATE returns a whole instance of the array, not a single element.
HTH.
Cheers.
Carlos.

juanalfonso 41 posts Joined 01/16
16 Feb 2016

Thanks for all the given information, Carlos and Dieter. It will be usseful for me.
Best regards

You must sign in to leave a comment.