0

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!

Community
  • 1
  • 1
user3183843
  • 57
  • 1
  • 9

1 Answers1

0
  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')));
Jon Heller
  • 34,999
  • 6
  • 74
  • 132
  • Thank you for your help, your insight lead me to find something that worked. You can use a syntax like UPDATE my_table SET my_column SET = (my_array(my_object(TO_DATE('date','mm/dd/yyyy'),('X'))); – user3183843 Oct 30 '14 at 22:01