7

I have a PL/SQL table-type/associative-array in Oracle

CREATE OR REPLACE TYPE STRING_TBL IS TABLE OF VARCHAR2(3000);

on which I can perform select queries like this

...
strings STRING_TBL;
...
SELECT * FROM TABLE(strings);

But what is the column name of that one column in the result set? That is, what would I need to put into <name_of_only_column> to make this query work:

SELECT rowid, p.<name_of_only_column>
FROM TABLE(strings) p;

Alternatively, if I can't do that, is there any way to alias that one column through a subselect in Oracle?

Dexter
  • 3,072
  • 5
  • 31
  • 32
  • You can check this example http://stackoverflow.com/questions/5165580/how-to-use-a-table-type-in-a-select-from-statement – Kadir Ardıç Sep 20 '13 at 07:42

2 Answers2

11

But what is the column name of that one column in the result set?

Pseudocolumn named column_value.

-- nested table schema object type
create type t_list as table of number


select column_value
  from table(T_List(1,2,3))

  COLUMN_VALUE
------------
           1 
           2 
           3

Note: In this case it's not allowed to use rowid pseudocolumn, simply because in-memory nested table does not have one.

Nick Krasnov
  • 26,886
  • 6
  • 61
  • 78
  • 1
    It's a pity that the [PL/SQL collection documentation](http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/collections.htm#LNPLS005) doesn't even mention `column_value` or anything about pseudocolumns. – Mr. Llama Dec 30 '14 at 22:16
3

It's column_value, the following should work:

SELECT rowid, p.column_value
FROM TABLE(strings) p;