How to get table from nested table [PL/SQL]

Question

I want get table from nested table.

Create table type:

CREATE OR REPLACE TYPE Name_list_t AS TABLE OF VARCHAR2(50)

Alter my table adding column nested table:

ALTER TABLE MOVIES ADD Movi_name_list Name_list_t NESTED TABLE Movi_name_list STORE AS Movi_name_list

Add table to nested table:

Declare
extractNames Name_list_t;
Begin
(...) some code...
update movies set movi_name_list=extractNames where movi_id=id_movie;
End;

Inserting table work great. Finally, I want get table from nested table and I don't know how. I try two ways but no successful:

select movi_name_list into extractNames from movies where movi_id=1; 
extractNames := select movi_name_list from movies;

Thank you for your help.

`select column_value bulk collect into extractNames from table(select movi_name_list from movies where movi_id=1)` — Egor Skriptunoff, Nov 01 '13 at 20:57
@EgorSkriptunoff You don't have to do it like this, you can just select the nested table into a variable. — Przemyslaw Kruglej, Nov 01 '13 at 21:01

score 0 · Answer 1 · edited May 23 '17 at 10:32

0

Select from @Egor Skriptunoff work!

Solution:

select column_value bulk collect into extractNames from table(select movi_name_list from movies where movi_id=1)

My select cast incorrectly, I guess.

edited May 23 '17 at 10:32

Community

answered Nov 03 '13 at 15:36

estradowiec

1 Answers1