4 Replies Latest reply: Nov 23, 2012 2:59 PM by AlbertoFaenza RSS

    Basic nested table problem

    528063
      Hello all, Please check the code below and let me know why at the end of the insertion the table is still empty (no records returned when I select * ). BTW, No errors show up when running and commit successfully.

      CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
      /
      CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
      NESTED TABLE col1 STORE AS col1_tab;

      INSERT INTO nested_table VALUES (1, my_tab_t('A'));
      INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
      INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));
      COMMIT;

      SELECT * FROM nested_table;


      Thanks!
        • 1. Re: Basic nested table problem
          Frank Kulash
          Hi,

          Your code works fine for me in Oracle
          10.1.0.2.0,
          10.2.0.5.0,
          11.1.0.6.0 and
          11.2.0.1.0.
          What version are you using?
          • 2. Re: Basic nested table problem
            Solomon Yakobson
            OMD wrote:
            why at the end of the insertion the table is still empty (no records returned when I select *
            Oracle version (exact)? I can't reproduce it:
            SQL> select  *
              2    from  v$version
              3  /
            
            BANNER
            --------------------------------------------------------------------------------
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
            PL/SQL Release 11.2.0.1.0 - Production
            CORE    11.2.0.1.0      Production
            TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
            NLSRTL Version 11.2.0.1.0 - Production
            
            SQL> CREATE OR REPLACE TYPE my_tab_t AS TABLE OF VARCHAR2(30);
              2  /
            
            Type created.
            
            SQL> CREATE TABLE nested_table (id NUMBER, col1 my_tab_t)
              2  NESTED TABLE col1 STORE AS col1_tab;
            
            Table created.
            
            SQL> 
            SQL> INSERT INTO nested_table VALUES (1, my_tab_t('A'));
            
            1 row created.
            
            SQL> INSERT INTO nested_table VALUES (2, my_tab_t('B', 'C'));
            
            1 row created.
            
            SQL> INSERT INTO nested_table VALUES (3, my_tab_t('D', 'E', 'F'));
            
            1 row created.
            
            SQL> COMMIT;
            
            Commit complete.
            
            SQL> 
            SQL> SELECT * FROM nested_table;
            
                    ID
            ----------
            COL1
            ------------------------------------------------------------------------------------------------------------------------------------
                     1
            MY_TAB_T('A')
            
                     2
            MY_TAB_T('B', 'C')
            
                     3
            MY_TAB_T('D', 'E', 'F')
            
            
            SQL> 
            SY.
            • 3. Re: Basic nested table problem
              AlbertoFaenza
              Your code is fine.

              Run in SQLplus setting echo on and post the output.
              Don't forget to mention your exact Oracle version.

              Regards.
              Al
              • 4. Re: Basic nested table problem
                528063
                Thanks all for the quick response, I used to run it through SQL Developer, now I tried to run the code at sqlplus, it worked fine! weirdo :)