Insert into nested table plsql

Question

I'm a newbie with pl sql and I'm facing some problems with inserting into nested tables (I'm using these just to test a procedure). So my code is:

insert into t_prenotazioni
      (nro_cliente, data_disponibilita)
    values
      (righe.nro_cliente, v_data_disponibilita);

where t_prenotazioni is a table of a type defined by me, righe.nro_cliente is a value that I get from a cursor and v_data_disponibilita is a variable. The error that I get is:

PLS-00330 invalid use of type name or subtype

You can INSERT INTO a table, not a variable. If you need to populate a variable you need an assignment or some kinf of SELECT ... INTO.... FROM ...Please try to better explain what you're trying to do to get some help. — Aleksej, Apr 19 '17 at 10:51
Thanks. I'm trying to declare a nested table and I did it like this: " type prenotazioni is table of type_prenotazioni; t_prenotazioni prenotazioni;". Then I'm trying to insert into this table, the type of the table has 2 fields. — leobia, Apr 19 '17 at 10:53
A simplified [test case](https://stackoverflow.com/help/mcve) showing the definition of the table and the types would help here. — William Robertson, Apr 19 '17 at 11:57

score 2 · Accepted Answer · answered Apr 19 '17 at 11:04

You are probably trying to do something like:

declare
    type type_prenotazioni is record(nro_cliente number, data_disponibilita date);
    type prenotazioni is table of type_prenotazioni;
    vPrenotazioni prenotazioni;
begin
    vPrenotazioni := new prenotazioni();
    vPrenotazioni.extend(1);
    vPrenotazioni(1).nro_cliente := 10;
    vPrenotazioni(1).data_disponibilita := sysdate;
    --
    for i in vPrenotazioni.first .. vPrenotazioni.last loop
        dbms_output.put_line(vPrenotazioni(i).nro_cliente || ' - ' ||
                                to_char(vPrenotazioni(i).data_disponibilita, 'dd/mm/yyyy')
                               );
    end loop;
end;

I would stronlgy recommend having a look at the Oracle documentation to improve your knowledge; this is only a simple, small example, but there are many many different things you may want to do.

Thank you for the answer. My only question now is when you do vPrenotazioni.extend(1), at the next insertion (because I'm inserting not only 1 value in the table but multiple) the value that was inside the table is overwritten? — leobia, Apr 19 '17 at 12:15
Here I extend to insert a single value; if you need to insert more than one record, you need to extend accordingly and then use vPrenotazioni(i) where i is your number. Or, you may want to have a look at SELECT ... BULK COLLECT INTO .... Have a look at some BULK COLLECT examples to learn a way to massively populate your structures — Aleksej, Apr 19 '17 at 12:18

Insert into nested table plsql

1 Answers1