oracle-databaseselectinsertrefobject-relational-model

Inserting data from table (SELECT) with REF - Oracle Database


I'm trying to insert all data from Pacient table to Pacient_OR table (Object-Relational). Is there a simple way to do that (one script), if Pacient table has column with Pojistovna_ID (foreign key) and in Pacient_OR table there is REF to the Pojistovna_OR. Both Pojistovna and Pojistovna_OR are populated with the same data, but one is relational, second is based on object type.

I tried this (and more ofc):

 INSERT INTO pacient_or
            (pacient_or.id,
             pacient_or.jmeno,
             pacient_or.prijmeni,
             pacient_or.datum_narozeni,
             pacient_or.rodne_cislo,
             pacient_or.telefon,
             pacient_or.krevni_skupina,
             pacient_or.rodinna_anamneza,
             pacient_or.adresa,
             pacient_or.pojistovna)
SELECT pacient.id,
       pacient.jmeno,
       pacient.prijmeni,
       pacient.datum_narozeni,
       pacient.rodne_cislo,
       pacient.telefon,
       pacient.krevni_skupina,
       pacient.rodinna_anamneza,
       Adresa_typ(pacient.ulice, pacient.mesto, pacient.psc),
       (SELECT Ref(poj)
        FROM   pacient pac,
               pojistovna_or poj
        WHERE  pac.pojistovna_id = poj.id)
FROM   pacient;  

This code throws error:

single-row subquery returns more than one row


Solution

  • Do not use pacient pac in the subquery. Link it to the pacient in your main from-clause. And even better do not use a subquery for this.