how to insert value in nested table through plsql block

Question

i have created code

create type vet_visit_t is object 
(visit_date date,
reason varchar2(100)
);
create type vet_visit_list_t is table of 
vet_visit_t;
Create table  pet_table 
(
tag_no integer,
name varchar2 (60),
breed varchar2(100),
petcare vet_visit_list_t
);
insert into pet_table (tag_no,name,breed,petcare )
VALUES  (100,
           'mercury',
           'african grey parrot',
           vet_visit_list_t (vet_visit_t('01-JAN-2001','clip wing'),
                             vet_visit_t('01-apr-2002','check cholesterol'),
                             vet_visit_t('01-MAY-2002','check cholesterol')
                             )
           );

now i am using plsql block for insertion in pet_table

declare
pet_c vet_visit_list_t :=vet_visit_list_t();
pet_object vet_visit_t;
begin 
pet_object := vet_visit_t('03-feb-2004','eye checkup');
pet_c :=vet_visit_list_t(pet_object);
insert into table(select pet_table.petcare from pet_table where tag_no =100)
values (pet_c);
end;
/

i am getting error

PL/SQL: ORA-00932: inconsistent datatypes:

how to insert value in nested table through plsql block

Answer 1

The full error you get is:

ORA-06550: line 8, column 9:
PL/SQL: ORA-00932: inconsistent datatypes: expected SCHEMA.VET_VISIT_LIST_T got SCHEMA.VET_VISIT_T
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored

That makes it pretty clear what you are doing wrong. You are trying to insert a new nested table (with one element) into your existing nested table. You need to just insert the new object directly:

declare
  pet_object vet_visit_t;
begin 
  pet_object := vet_visit_t(to_date('03-feb-2004','DD-MON-YYYY'),'eye checkup');
  insert into table(select pet_table.petcare from pet_table where tag_no =100)
  values (pet_object);
end;
/

Please notice also that I've used an explicit date conversion with to_date(); you were passing in a string and relying on implicit date conversion based on your NLS settings, which isn't a good idea as your code can behave differently in another session (different client, someone else running it, etc.).

You can also do the insert more simply:

insert into table(select pet_table.petcare from pet_table where tag_no =100)
values (vet_visit_t(date '2004-02-03','eye checkup'));

which you can do in plain SQL, with no PL/SQL block required (though it will work inside a block as well, of course). This time I've also used an ANSI date literal, which I prefer for fixed dates.

Either way you end up with:

select pc.*
from pet_table pt
cross join table(pt.petcare) pc
where tag_no = 100;

VISIT_DATE REASON                        
---------- ------------------------------
2001-01-01 clip wing                     
2002-04-01 check cholesterol             
2002-05-01 check cholesterol             
2004-02-03 eye checkup