4 Replies Latest reply: Jul 22, 2013 3:25 PM by Ariean RSS

    Inserting Data into nested table

    Ariean

      I am exploring the differences between OBJECT & RECORD.

      As i am still in process of learning, I found that both are structures which basically groups elements of different datatypes or columns of different datatypes, one is used in SQL and other is used in PL/SQL, please correct me if I am wrong in my understanding.

       

      Below i am trying to insert data into an table of type object but i am unsuccessful can you please help.

       

      CREATE OR REPLACE type sam as OBJECT
      (
      v1 NUMBER,
      v2 VARCHAR2(20 CHAR)
      );
      
      ---Nested Table---
      create or replace type t_sam as table of sam;
      
      --Inserting data----
      insert into table(t_sam) values(sam(10,'Dsouza'));
      

       

      Error Message:

      Error starting at line 22 in command:
      insert into table(t_sam) values(sam(10,'Dsouza'))
      Error at Command Line:22 Column:13
      Error report:
      SQL Error: ORA-00903: invalid table name
      00903. 00000 -  "invalid table name"
      *Cause:    
      *Action:
      
        • 1. Re: Inserting Data into nested table
          Greg Spall

          You don't have a table creation there - you're missing a step.

           

          Here's a good sample over here:

           

          http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:410619303624

          • 2. Re: Inserting Data into nested table
            Ariean

            So only purpose of equivalent SQL types concept of nested tables is to use them as one of the data types while defining an actual table?

            And that nested table could be a record in SQL or an Object in PLSQL or just simple datatype(number,varchar etc)?

             

            Secondly is my understanding correct about OBJECT & RECORD?

            • 3. Re: Inserting Data into nested table
              Greg Spall

              Ariean wrote:

               

              So only purpose of equivalent SQL types concept of nested tables is to use them as one of the data types while defining an actual table?

              Sort of - you can definitely use them for more than just "defining an actual table". (I'm fairly certain you could pass a nested table into a procedure, for example - try it, though - I'm not 100% sure on that - it just "makes sense". If you can define a type, you can use it, pass it around, whatever.).

               

              Ariean wrote:

               

              And that nested table could be a record in SQL or an Object in PLSQL or just simple datatype(number,varchar etc)?

               

              Nested tables are just like any other custom data type. You can create a nested table of other data types. You can create a custom data type of nested tables.

               

              It could get stupidly .. er, stupid O_0

               

              CREATE TYPE o_myobj1 AS object ( id1   number, cdate1  date );
              
              CREATE TYPE t_mytype1 AS table of o_myobj1;
              
              CREATE TYPE o_myobj2 AS object ( id2   number,  dumb  t_mytype1 );
              
              CREATE TYPE t_dumber AS table of o_myobj2;
              

               

              O_0

              Ok, my brain's starting to hurt - I hope you get the idea

               

              Ariean wrote:

               

              Secondly is my understanding correct about OBJECT & RECORD?

              I can't think of any benefit of describing it another way.

              • 4. Re: Inserting Data into nested table
                Ariean

                Sort of - you can definitely use them for more than just "defining an actual table". (I'm fairly certain you could pass a nested table into a procedure, for example - try it, though - I'm not 100% sure on that - it just "makes sense". If you can define a type, you can use it, pass it around, whatever.).

                That is correct...this has been implemented/used in this article

                https://forums.oracle.com/thread/888365

                 

                Thanks.