CODEWITHSUNDEEP
oracle-databaseplsql
Ruslan
Ruslan

Reputation: 423

How to save just one column values of a table into an array?

My query returns something like this:

enter image description here

I want to save each value into array. I know how to save each column of a row using SELECT INTO, but I don't know how to save rows of a table with just one column.

I would like to get this: 

my_array(1) = 11111
my_array(2) = 22222
my_array(3) = 33333
....

Length of an array is 6. I know that my query will not return more than 6 rows. If query returns less than 6 rows is it possible to put NULL into array element where there is no rows for it?

Upvotes: 0

Views: 858

Answers (1)

Kaushik Nayak
Kaushik Nayak

Reputation: 31676

As suggested in a comment you may use BULK COLLECT

select your_col BULK COLLECT 
 INTO your_collection from my_array where some_condition = 'something';

Regarding your question

if query returns less than 6 rows is it possible to put NULL into array element where there is no rows for it

You have not said why it's required but a BULK COLLECT will create as many elements in the array as the number of rows present in the query result. In case you need NULL elements, you may use count and extend collection methods to check and allocate null elements until the count is 6.

DECLARE
TYPE myarrtype is table of integer;
my_array myarrtype;
BEGIN
  select level bulk collect into my_array from dual 
  connect by level <= 4; --generates 4 integers 1-4 and loads into array.
    dbms_output.put_line('OLD COUNT '||my_array.count);

  if my_array.count <= 6 then
     my_array.extend(6 - my_array.count); --This appends required number of 
                                          --NULL elements   
  dbms_output.put_line('NEW COUNT '||my_array.count);
  end if;
END;
/

Output

1 rows affected

dbms_output:
OLD COUNT 4
NEW COUNT 6

Demo

Upvotes: 2

Related Questions