CODEWITHSUNDEEP
oracle-databaseoracle11g
XING
XING

Reputation: 9886

How to select column value from a nested table

I created 1 object.

create type tab_billing as object(invoice_no number, 
                                    customername varchar2(100)
                                  );

Now i created a table with the object as a column.

CREATE TABLE tab1 (col1 number,COL2 tab_billing);

Is there anyway I can ONLY select invoice_no from the tab1.

select col2 from tab1;

Is givng me both invoice_no and customername. Substr function is not working here.

Upvotes: 3

Views: 3643

Answers (2)

Alex Poole
Alex Poole

Reputation: 191265

You can query the column value's object field directly, but to avoid confusing the object name resolution steps you have to supply and use a table alias:

select t1.col2.invoice_no from tab1 t1;

This is mentioned in the documentation:

To avoid inner capture and similar problems resolving references, Oracle Database requires you to use a table alias to qualify any dot-notational reference to subprograms or attributes of objects.

Qualifying the column with the the table name isn't enough; using select tab1.col2.invoice_no from tab1 gets ORA-00904. You have to use a table alias - although, slightly bizarrely, it still works if the alias is the same as the table name, so select tab1.col2.invoice_no from tab1 tab1 (i.e. aliasing tab1 as tab1, which is normally redundant) works too.

Quick demo:

create type tab_billing as object(invoice_no number, 
                                    customername varchar2(100)
                                  );
/

Type TAB_BILLING compiled

CREATE TABLE tab1 (col1 number,COL2 tab_billing);

Table TAB1 created.

insert into tab1 values (1, tab_billing(42, 'Test'));

1 row inserted.

select t1.col2.invoice_no from tab1 t1;

                        COL2.INVOICE_NO
---------------------------------------
                                     42

Upvotes: 5

Aleksej
Aleksej

Reputation: 22949

You can use TREAT:

SQL> create type tab_billing as object(invoice_no number,
  2                                      customername varchar2(100)
  3                                    );
  4  /

Type created.

SQL> CREATE TABLE tab1 (col1 number,COL2 tab_billing);

Table created.

SQL> insert into tab1 values (1, tab_billing(10, 'ten')) ;

1 row created.

SQL> select col1,
  2         TREAT(col2 AS tab_billing).invoice_no as invoice_no,
  3         TREAT(col2 AS tab_billing).customername as customername
  4  from tab1;

  COL1 INVOICE_NO CUSTOMERNAME
------ ---------- --------------------
     1         10 ten

Upvotes: 2

Related Questions