Accessing Data inside an object that is in a VARRAY Oracle

Question

I am new to PL/SQL and working with hybrid tables so I am sorry if I missed something obvious, I thought I had gotten most of the major stuff down.

I recently came across an issue with trying to access data in a VARRAY of type OBJECT.

I am trying to change some data in an object that has been stored in a VARRAY of type object but keep running into errors. The column in the table that I am trying to access was created using the VARRAY as the datatype.

I have an object :

CREATE TYPE MY_OBJECT AS OBJECT

MY_OBJECT has two values, DATE and a single char.

This object is then used to define a column's datatype as VARRAY of type OBJECT:

CREATE TYPE MY_COLUMN AS VARRAY(100) of MY_OBJECT

I have gone through two posting on Stackoverflow to try and resolve my issue, listed below:

How to update a varray type within a table with a simple update statement?

SQL Retrieving an object from VARRAY in Oracle 11g Database

---

Here is what I have after going through those to try and get access to the data so that i can change it.

UPDATE my_table SET my_column = (my_object(date), my_object('X'));

I also tried

UPDATE my_table SET my_column = (date, 'X');

These two attempts throw a "Missing Right Paraenthesis" error so I went on to a different method.

INSERT INTO my_table 
VALUES
( 
1, my_column(varray)(my_object(1, date, 'X'))
);

This throws a "Not enough values" error.

My question has two parts:

1) There are 60 columns in total in the table, in order to get around this "Not enough values" error do I have to enter in values for each column?

I thought I could directly access the column without having to deal with any other column that i did not need to access.

2) In order to access the object in the array, would a single object invocation (like my third attempt), be the way to go?

I was hoping to just try and access the my_column, get access to the VARRAY, and then access the object in the VARRAY so that I can add or change data as needed, in the object.

I thought it was pretty straight forward but i have been going in circles all day.

Any advice would be greatly appreciated!

Answer 1

1. Yes, you must enter all values if you use the default constructor.

2. I need more information about the context of your insert statement. For a stand-alone INSERT, here's an example of working syntax.

   CREATE TYPE MY_OBJECT AS OBJECT
   (
       a_date date,
       a_char varchar2(1)
   );
   
   CREATE TYPE MY_COLUMN AS VARRAY(100) of MY_OBJECT;
   
   create table my_table
   (
       col1 number,
       my_columns my_column
   );
   
   INSERT INTO my_table 
   VALUES(1, my_column(my_object(sysdate, 'X')));