Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Need Help With Object Types

User_K6LH0Sep 5 2022

Hi,
We have a multi level Object Type which is used to receive data from Java front-end. The object structure is mapped to the Java form in the front-end and goes something like this:

--This is just a snippet of the entire structure
CREATE OR REPLACE TYPE core_obj AS OBJECT
(
 primary_key_1 NUMBER
 , primary_key_2 NUMBER
);

CREATE OR REPLACE TYPE core_obj_nt AS TABLE OF core_obj;

...

Now this structure needs to be updated and a VARCHAR2 array needs to be added to the core_obj. Do I have to do something like this or is there a better way?

CREATE OR REPLACE TYPE core_message AS OBJECT
(
 message VARCHAR2(4000)
);

CREATE OR REPLACE TYPE core_message_nt AS TABLE OF core_message;

--Update core_obj to include the core_message_nt
CREATE OR REPLACE core_obj AS OBJECT
(
 primary_key_1 NUMBER
 , primary_key_2 NUMBER
 , message_details core_message_nt --New change
);

CREATE OR REPLACE core_obj_nt AS TABLE OF core_obj;

...

Is there a better way to do the same instead of creating the "core_message" object and "core_message_nt" nested table?
Please pardon my ignorance. Thanks in advance for all the help.

This post has been answered by mathguy on Sep 5 2022
Jump to Answer
Comments
Post Details
Added on Sep 5 2022
7 comments
141 views