Creating an array in SQL Oracle

Question

I'm trying to pick a random item from an array while performing an update in Oracle. What is the easiest way to achieve this?

I want something like this:

DECALRE ARRAY items = ('item1', 'item2', 'item3')

update table set field = items(TRUNCT(DBMS_RANDOM.value(1,3)))

that way a random value from the list is inserted in the field for each record that is being updated.

Why do you need an array to do this? Why not just update?

Ben
– Ben

2012-01-25 21:10:48 +00:00
Commented Jan 25, 2012 at 21:10 — Ben
– Ben, Commented Jan 25, 2012 at 21:10

Dan A. · Accepted Answer · 2012-01-25 21:23:39Z

2

If you're using a fixed size array, you can use a VARRAY in PL/SQL:

DECLARE
    TYPE strArray IS VARRAY(3) of VARCHAR2(10);
    v_myarray strArray;
BEGIN
    v_myarray := strArray('item1', 'item2', 'item3');
    update table
    set field = v_myarray(DBMS_RANDOM.value(1,3));   
END;
/

answered Jan 25, 2012 at 21:23

Dan A.

2,92420 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating an array in SQL Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related