How to define an array of varchars in Stored Procedure Language?

Database

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

Juan
20 REPLIES
N/A

Re: How to define an array of varchars in Stored Procedure Language?

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

Re: How to define an array of varchars in Stored Procedure Language?

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

Juan
N/A

Re: How to define an array of varchars in Stored Procedure Language?

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#com...

If the splitting rules are more complicated you can use REGEX_SPLIT_TO_TABLE

Re: How to define an array of varchars in Stored Procedure Language?

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"?

Juan
N/A

Re: How to define an array of varchars in Stored Procedure Language?

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.

Re: How to define an array of varchars in Stored Procedure Language?

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!

Juan
N/A

Re: How to define an array of varchars in Stored Procedure Language?

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.

Re: How to define an array of varchars in Stored Procedure Language?

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

Juan
N/A

Re: How to define an array of varchars in Stored Procedure Language?

- 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?