Oracle PL/SQL BULK Update from an arrray

Question

Is it possible to perform a bulk update in Oracle like this:

TYPE type_A IS RECORD
(
    var    NUMBER(15,2)
);

TYPE t_type_A IS VARRAY (9) OF type_A; 
var_t_type_A  t_type_A;


SELECT
    A
BULK COLLECT INTO var_t_type_A
FROM
    B;
    
-- lopp over

FOR i IN var_t_type_A.FIRST .. var_t_type_A.LAST
LOOP
    var_t_type_A.var = 99;
END LOOP;
    

And theh just similiar with insert do like this

   FORALL var_i_idx IN 1..table_of_t_created_new_buchung.count SAVE EXCEPTIONS
       UPDATE TABLE B somehow

Answer 1

Well your skeleton code is rather sparse so by necessity so is any answer. First off, Yes, what you seen to want is possible. Secondly you have a couple issues in you code:

1. There is no reason to create a record for a to contain a single variable.
2. I assume a varray would works with bulk collect/ forall. I have just never used one, never saw the point.
3. Your forall statement contains the "save exceptions" clause, however without an exception section this is useless. I have included the necessary definitions for that.

    declare 
        bulk_errors  EXCEPTION;  
        PRAGMA EXCEPTION_INIT (bulk_errors , -24381);  
        
        type t_type_a is table of number(15,2);  
        var_t_type_a  t_type_a;
    
    begin
    
    
        select a
          bulk collect 
          into var_t_type_a
          from b;
        
        -- lopp over
    
        for i in var_t_type_a.first .. var_t_type_a.last
        loop
            var_t_type_a(i) = 99;
        end loop;
        
        ...
    
    
        forall var_i_idx in 1..var_t_type_a.count save exceptions
           update table b 
              set some_column = var_t_type_a(var_i_indx); 
              
    exception
        when bulk_errors then 
             <logic for bulk errors>;
    end ;

For a more complete answer post a more complete problem description.