How should I use an object type in an insert DML statement?

Question

I have created two TYPE objects to try out OOP processing in PL/SQL. I tried to use my type o_customers in my INSERT statement, but I could not do it.

There is a Customers table. It has same columns as o_customers.

create or replace type o_customers as object (
id number,
name varchar2(40),
age number,
address o_addressC,
salary number
);

create or replace type o_addressC as object (
mahalle varchar(30),
apartman varchar(15),
ilce varchar(15),
apt_no number
);

declare 
    adres o_addressC;
    musteri o_customers;
begin
    adres := o_addressC('selami ali mah','çınar apt',' üsküdar',19);
    musteri:= o_customers(10,'UĞUR SİNAN SAĞIROĞLU',26,adres,1000);
    insert into customers values (musteri);
end;

Answer 1

" There is a customers table. it has same columns with o_customers"

In OOP it is not enough for objects to have the same structure to be compatible in a programming context: they must be the same type, or related to each other through inheritance.

So you need to create the table using that type:

SQL> create table customers of o_customers 
  2  /

Table created.

SQL> desc customers
 Name                   Null?    Type
 ---------------------- -------- -------------
 ID                              NUMBER
 NAME                            VARCHAR2(40)
 AGE                             NUMBER
 ADDRESS                         O_ADDRESSC
 SALARY                          NUMBER

SQL> 

Now your insert statement will work:

SQL> declare
  2      adres o_addressC;
  3      musteri o_customers;
  4  begin
  5      adres := o_addressC('selami ali mah','cınar apt','uskudar',19);
  6      musteri:= o_customers(10,'UĞUR SİNAN SAĞIROĞLU',26,adres,1000);
  7      insert into customers values(musteri);
  8  end;
  9  /

PL/SQL procedure successfully completed.

SQL> select * from customers;

        ID NAME                                            AGE
---------- ---------------------------------------- ----------
ADDRESS(MAHALLE, APARTMAN, ILCE, APT_NO)
------------------------------------------------------------------------------------------------------------------------------------------------------
    SALARY
----------
        10 UĞUR SİNAN SAĞIROĞLU                             26
O_ADDRESSC('selami ali mah', 'c??nar apt', ' uskudar', 19)
      1000


SQL>

---

Incidentally I had to make minor changes to the inserted values because the posted statement hurled

declare
*
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 6

This is because your o_addressC type attributes are too small for strings with multi-byte characters.