This discussion is archived
4 Replies Latest reply: Nov 23, 2012 12:59 PM by AlbertoFaenza RSS

Basic nested table problem

528063 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 :)

Legend

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