I am using Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit

and SQL Developer version Version 21.4.1.349

Build 349.1822

I have a schema level type as follows

CREATE OR REPLACE TYPE CHAR_TAB_20_TYPE IS TABLE OF VARCHAR2 (20);

The above type is the datatype of the column order_consignee_tab in the table table_1.

When I execute the below select statement i get the output in SQL Developer

as follows which is not displaying the values in the nested table.

SELECT selection_id,
       order_consignee_tab
  FROM table_1
 WHERE col_1 = '206' AND selection_id = 'TEST';

Output:

SELECTION_ID ORDER_CONSIGNEE_TAB
TEST WMSPRD.CHAR_TAB_20_TYPE()

Can somebody please tell me how to view the values of the nested table column order_consignee_tab.

CodePudding user response：

As you asked about SQL Developer, then follow steps on this screenshot:

1. double-click the result of the select statement
2. click the pencil icon
3. view the result

CodePudding user response：

You can JOIN to a table collection expression and then get the value from the COLUMN_VALUE pseudo-column:

SELECT selection_id,
       o.COLUMN_VALUE AS order_consignee
FROM   table_1 t
       CROSS JOIN TABLE(t.order_consignee_tab) o
WHERE  col_1 = '206'
AND    selection_id = 'TEST';

Which, for the sample data:

CREATE TABLE table_1 (
  selection_id VARCHAR2(4),
  col_1        VARCHAR2(3),
  order_consignee_tab CHAR_TAB_20_TYPE
) NESTED TABLE order_consignee_tab STORE AS table_1__order_consignee_tab;

INSERT INTO table_1 (selection_id, col_1, order_consignee_tab)
VALUES ('TEST', '206', CHAR_TAB_20_TYPE('ABC', 'DEF', 'GHI'));

Outputs:

SELECTION_ID	ORDER_CONSIGNEE
TEST	ABC
TEST	DEF
TEST	GHI

db<>fiddle here