This discussion is archived
13 Replies Latest reply: Apr 4, 2013 4:23 PM by AshishRoy RSS

Create table with object having multilevel nested objects

AshishRoy Newbie
Currently Being Moderated
Hi all,

Was wondering how to write a create table for the below

Error faced - ORA-22913: must specify table name for nested table column or attribute

table_log
SL#
log_date
object_holder t_account

t_account ( object contains nested table )
-------------->tb_services ( table of object )
-------------------------------------->t_services ( object has )
---------------------------------------------------------->tb_campgn ( table of object )
----------------------------------------------------------------------------------------->t_campgn
--------------->tb_equipments ( table of objects )
---------------------------------------------------->t_equipments

Thanks in advance.

Edit 1 - I found a workaround to the problem, but what i wanted was to use the parent object t_account as a table column along with native oracle datatype columns.

Edited by: Ashish Roy on Apr 4, 2013 11:44 AM
  • 1. Re: Create table with object having multilevel nested objects
    damorgan Oracle ACE Director
    Currently Being Moderated
    A really really bad idea unless you have no interest in scalability, performance, and SQL that can be written and supported by a human being.
  • 2. Re: Create table with object having multilevel nested objects
    AshishRoy Newbie
    Currently Being Moderated
    Hello damorgan, I do get the supported by a human being part ( unluckly code is not in my scope, trying to add instrumentation to enable logging ). could you help me understand scalability, performance, and SQL ?. thanks! Anyways for my understanding, what would be a way to create a table that has a object column with multiple levels of nested tables.
  • 3. Re: Create table with object having multilevel nested objects
    rp0428 Guru
    Currently Being Moderated
    Post your DDL for the objects and table.
  • 4. Re: Create table with object having multilevel nested objects
    Peter vd Zwan Expert
    Currently Being Moderated
    Hi,

    See:
    Re: Help required in creating Table of Nested objects as a column
    for an exsample of a create table with nested tables.


    Regards,

    peter
  • 5. Re: Create table with object having multilevel nested objects
    damorgan Oracle ACE Director
    Currently Being Moderated
    Tom Kyte has written about this extensively and panned the use of object tables in essentially every book he's written so far that covers the topic.

    Tom's recommendation has always been, to the best of my recollection, relational tables and object views.

    Demos here:
    http://www.morganslibrary.org/reference/object_views.html
  • 6. Re: Create table with object having multilevel nested objects
    AshishRoy Newbie
    Currently Being Moderated
    A package.procedure is passing below object to Jdbc based call.

    CREATE OR REPLACE
    TYPE T_ACC AS OBJECT (
    acct VARCHAR2 (15),
    actind CHAR (1),
    actcntdte VARCHAR2 (10),
    services tb_services,
    equipments tb_equipments
    )

    CREATE OR REPLACE
    TYPE TB_SERVICES AS TABLE OF t_services

    CREATE OR REPLACE
    TYPE T_SERVICES AS OBJECT (
    vcd NUMBER (10),
    rate NUMBER (7, 2),
    status CHAR (1),
    v_campgn tb_campgn
    )

    CREATE OR REPLACE
    TYPE TB_CAMPGN AS TABLE OF t_campgn

    CREATE OR REPLACE TYPE T_CAMPGN AS OBJECT (
    rate NUMBER (7, 2),
    actv CHAR (1),
    dsc CHAR (32 BYTE),
    frmdte VARCHAR2 (10),
    todte VARCHAR2 (10),
    pcode NUMBER (3)
    )

    CREATE OR REPLACE
    TYPE TB_EQUIPMENTS AS TABLE OF t_equipments

    I wanted to enable logging for the data being passed to Java calls. so i solved the purpose via table below

    CREATE TABLE AUDIT_LOG_ACCOUNT
    (
    SL# NUMBER,
    RUN_DATE DATE,
    acct VARCHAR2 (15),
    actind CHAR (1),
    actcntdte VARCHAR2 (10),
    SERVICE# TB_SERVICES,
    EQUIP# TB_EQUIPMENTS
    )
    NESTED TABLE SERVICE# STORE AS SERVICE_TAB,
    NESTED TABLE EQUIP# STORE AS EQUIPMENT_TAB


    But what I really wanted to do was to do was
    CREATE TABLE AUDIT_LOG_ACCOUNT
    (
    SL# NUMBER,
    RUN_DATE DATE,
    T_ACC_P T_ACC,
    )

    but could not figure this out.

    Edited by: Ashish Roy on Apr 4, 2013 3:22 PM
  • 7. Re: Create table with object having multilevel nested objects
    AshishRoy Newbie
    Currently Being Moderated
    Hey Peter thanks a lot for the response, the question I have is different from the one posted on the other post. That is well documented by oracle documentation, in this case what i want to achieve is to have a table column as an object. This object has 2 nested tables and then one of these has one more nested table, have posted the DDL for these.
  • 8. Re: Create table with object having multilevel nested objects
    AshishRoy Newbie
    Currently Being Moderated
    yes, I do agree to your and Tom's view point. The demo makes a point of marrying relational views and objects but does not statistically support the made point.

    Your point on Scalability has definitely whetted my appetite to understand this accurately. I am aware that Tom over the time has responded with multiple posts, would you mind helping with a few of those references?

    (I know that tom had to give up his server and much of that data is now not available, wish Oracle had provided him with a section.)
  • 9. Re: Create table with object having multilevel nested objects
    rp0428 Guru
    Currently Being Moderated
    >
    Hey Peter thanks a lot for the response, the question I have is different from the one posted on the other post
    >
    No - your example is the SAME template as what Peter posted.

    This code works for me.
    CREATE TABLE AUDIT_LOG_ACCOUNT
    (
    SL# NUMBER,
    RUN_DATE DATE,
    T_ACC_P T_ACC
    )
    nested TABLE t_acc_p.services store AS nested_tb_services
            (
                nested TABLE v_campgn store AS nested_tb_campgn
            )
            return as value
    ;
    You didn't post the DDL for t_equipment but that is a different column and you would specify the other column similar to the above.
  • 10. Re: Create table with object having multilevel nested objects
    rp0428 Guru
    Currently Being Moderated
    >
    could you help me understand scalability, performance, and SQL ?.
    >
    SQL? The best way for you to understand that is to test it yourself.

    Create the table using the DDL I posted above.

    Now write the sql to insert data into that table and populate ALL table columns and ALL attributes of ALL object types.

    Was that easy for you? Most SQL programmers will NOT know how to write DML against object types like that. That type of programming is more common in C, C#, Java or the like but not in the database world.

    Scalability? The best way for you to understand that is to test it yourself.

    Now that you have some data in the table try to modify one of the object types to add/modify/delete an attribute.

    Was that easy for you? Or did you get some exceptions because you have a table that references the existing type? Ask yourself what needs to happen if you just do what you think is the easy part: add a new attribute. What has to happen to ALL of the existing data? The same thing that has to happen to an ordinary heap table when you add a new NOT NULL column that has no default: ALL rows have to be rewritten to add the new data.

    How do you BULK LOAD data into that table? Where will the bulk data come from? It won't be delimited files; they don't support nested data like that.

    Do you have, or have you used any tools that support bulk loading of nested data? The only common use case would be XML data.

    Have you ever tried to collect statistics on a table like yours? Go ahead and see what happens.

    Have you ever tried to index data in one of those nested tables that is in a table like yours? Go ahead and create an index or two. Post the results here of how easy you found it.

    Performance? You can't bulk load the tables efficiently. The actual nested data is stored in a different segment than the actual table data. That was your original problem - you didn't know how to even do that. Not picking on you - many, if not most, experienced SQL and PL/SQL programmers don't know how to do that. There is a good reason for that - most of the world doesn't do it that way. Those skills are not in demand because that functionality isn't very widespread.

    Care to take a guess as to how successful your org will be at maintaining objects and code like that?

    If the data was mostly read-only and ONLY accessed in small volumes (e.g. by primary key) then the 'all-in-one' package of data can be easier to pass around and work with. But that isn't what most real-world apps call for.

    Using relational tables allows for efficient access of 'onesies-twosies' using an index or bulk processing using full scans. The data is easier to access, update and maintain.
  • 11. Re: Create table with object having multilevel nested objects
    AshishRoy Newbie
    Currently Being Moderated
    you have appended t_acc_p.services in the nested declaration. Is that mandatory?
  • 12. Re: Create table with object having multilevel nested objects
    rp0428 Guru
    Currently Being Moderated
    >
    you have appended t_acc_p.services in the nested declaration. Is that mandatory?
    >
    Don't be afraid of breaking Oracle by actually trying things.

    If you look at the table definition do you see anything called 'services'?

    No? Netiher does Oracle. You have to tell Oracle where it is.

    remove the 't_acc_p' and see what happens.
  • 13. Re: Create table with object having multilevel nested objects
    AshishRoy Newbie
    Currently Being Moderated
    Thank you! This was very helpful, I'll set about exploring the different avenues put forth.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points