How can I insert new data into an existing row's nested table? For example, I have defined
CREATE OR REPLACE TYPE businessTableForCategories AS TABLE OF VARCHAR(128);
/
CREATE TABLE Category (
name VARCHAR(128) PRIMARY KEY,
businesses businessTableForCategories
) NESTED TABLE businesses STORE AS categoryBusinessTable;
Say in Category there is an entry with name = 'Restaurant'
and businesses = businessTableForCategories('xzqpehc234ajdpa8')
.
How can I insert new data into that nested table for that entry in Category without removing the entry, or losing the data stored in the nested table?
I ask because one of the entries I am trying to insert requires an insert statement that is 25137 characters long, which is way past Oracle's limit for a single command. This is because there are many businesses in the category. I would like to create the category, and then insert the businesses one by one (or maybe small groupings) into the nested table "businesses".
See Question&Answers more detail:os