Oracle REF data type

Question

I'm trying to make this query work. I want to insert in Oficina_bancaria's nested table Cuentas the ref() of a Corriente_udt row stored in Cuenta table:

(SELECT ref(TREAT(VALUE(c) AS Corriente_udt)) FROM Cuenta c WHERE c.IBAN = '1654ES6639071895270420369756');

Corriente_udt is a subtype of Cuenta_udt which is the data type of Cuenta table.

This is the error I'm getting:

ORA-00907: missing right parenthesis

I've already tried this:

(SELECT ref(c) FROM Cuenta c WHERE c.IBAN = '1654ES6639071895270420369756');

But I also get an error:

ORA-00932: inconsistent datatypes: expected REF USER.CORRIENTE_UDT got REF USER.CUENTA_UDT

And this is my full query:

INSERT INTO TABLE (SELECT o.Cuentas FROM Oficina_bancaria o WHERE o.Codigo = 1439 AND o.Direccion = 'Alameda de Esperanza Vives 978 Valencia, 32678') (SELECT ref(TREAT(VALUE(c) AS Corriente_udt)) FROM Cuenta c WHERE c.IBAN = '1654ES6639071895270420369756');

Answer 1

I want to insert in Oficina_bancaria's nested table Cuentas the ref() of a Corriente_udt row stored in Cuenta table

Its not entirely clear what you want from your description, but I think you want to to add a reference to the Cuentas column (a corrientes_array data type) in an existing row of the Oficina_bancaria table.

Query:

UPDATE Oficina_bancaria
SET   Cuentas = COALESCE( Cuentas, Corrientes_array() )
                MULTISET UNION
                Corrientes_array(
                  ( SELECT TREAT( REF(c) AS REF Corriente_udt )
                    FROM   Cuenta c
                    WHERE  c.IBAN = '1654ES6639071895270420369756' )
                )
WHERE  Codigo    = 6356
AND    Direccion = 'Cuesta Hector Montes 15 Puerta 5  Cuenca, 02539'

or, maybe:

MERGE INTO Oficina_bancaria o
USING (
  SELECT TREAT( REF(c) AS REF Corriente_udt ) AS Corriente_ref
  FROM   Cuenta c
  WHERE  c.IBAN = '1654ES6639071895270420369756'
) c
ON (
    o.Codigo    = 6356
AND o.Direccion = 'Cuesta Hector Montes 15 Puerta 5  Cuenca, 02539'
AND c.Corriente_ref IS NOT NULL
)
WHEN MATCHED THEN
  UPDATE
  SET o.Cuentas = COALESCE( o.Cuentas, Corrientes_array() )
                  MULTISET UNION Corrientes_array( c.Corriente_ref );

(which should not insert a reference into the array if it is not found in the Cuenta table).

db<>fiddle here