You Asked

Servus Tom,
I am reading quite often your blog and it helped me a lot to learn PL/SQL
Now, i want to do something like this:

declare
  type l_array is table of varchar2(30) index by binary_integer;
  l_column_count NUMBER;
  i NUMBER;
begin
  select count(*)
  INTO   l_column_count 
  from   user_tab_columns 
  where table_name='ARTIKEL';
  
  FOR i IN 0..l_column_count-1
  LOOP
  select COLUMN_NAME INTO l_array(i) from ALL_TAB_COLUMNS where TABLE_NAME='ARTIKEL';
  
    dbms_output.put_line('Das Array hat '|| l_array(i) ||' Werte.');
  END LOOP;
end;
/

1. Read and store how many column the table has.
2. Loop from index i := 0 to column number -1, index start from 0 to ….
3. Save the Name of the column into the Array

in Oracle docu
https://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2091.htm
i saw the Definition
COLUMN_NAME VARCHAR2(30) NOT NULL Column Name

I found some similar post from you, but not exactly this. As a beginner, ist difficult to Abstract what you wrote to a similar Problem.

Thank you very much for your help!

Kind regards
Michael

and Chris said...

There are a few issues here:

1. There's no need to count how many columns first! Arrays include a COUNT method
2. PL/SQL associated array indices start at 1, not zero
3. You can use bulk processing to select the rows into the array

Also note: l_array is declared as a TYPE, not a VARIABLE.

The easiest way to fix this is to:

- Declare a variable with of the array type
- BULK COLLECT the query into this variable
- If you want to see the values in it, loop from 1 to ARR.COUNT and output them

Which all together gives:

create table t ( 
  c1 int, c2 date, c3 varchar2(10)
);

set serveroutput on
declare
  type t_array is table of varchar2(30) 
    index by pls_integer;
    
  l_array t_array;
begin
  select column_name
  bulk collect into l_array
  from   user_tab_columns 
  where  table_name='T';
  
  FOR i IN 1 .. l_array.count 
  LOOP
    dbms_output.put_line('Das Array hat '|| l_array(i) );
  END LOOP;
end;
/

Das Array hat C1
Das Array hat C2
Das Array hat C3

There's much more power to bulk processing. To find out more, read https://blogs.oracle.com/oraclemagazine/bulk-processing-with-bulk-collect-and-forall

Is this answer out of date? If it is, please let us know via a Comment