Selecting Column from Oracle User Defined Type (Nested Table)

this is how to select a single column from a multi-column nested table.

CREATE OR REPLACE TYPE question_obj AS OBJECT
(QUESTIONSEQ VARCHAR2(10),
QUESTION_LABEL VARCHAR2 (200 BYTE),
TYPESEQ NUMBER (2),
COMPULSORY NUMBER (1),
MAX_ANSWER NUMBER (2),
GROUPSEQ NUMBER (2),
ORDER_NO NUMBER (2))
/


CREATE OR REPLACE TYPE question_tbl is table of question_obj;
/



DECLARE
l_questions   question_tbl;
BEGIN
-- this following statement would populate the nested table with data
-- from a table
SELECT question_obj (q.questionseq,
                   q.question_label,
                   q.typeseq,
                   q.compulsory,
                   q.max_answer,
                   q.groupseq,
                   q.order_no)
BULK COLLECT
INTO l_questions
FROM QUESTION q
WHERE q.surveyseq = '2010050195';

-- now let's demonstrate how to select from a nested table
FOR r IN (SELECT q.questionseq
               FROM TABLE (l_questions) q
              WHERE q.questionseq IS NOT NULL)
LOOP
 DBMS_OUTPUT.put_line (r.questionseq);
END LOOP;
END;

Note: You can NOT use sql againts PLSQL table, that is you declare the type in PLSQL declaration or package specification

It's been a while...

It's been a long time since i write in this blog.

When i was helping my colleague with PLSQL, he suggested me to keep a blog of things that I have done or learned so that i could always easily refer back to it. What he said reminded me that i have this blog, waiting for me to write again.

I think it'd be useful to keep a blog about this than relying on search engine and trying to find article that suits your use case. Human brain is fantastic, you would easily remember in the back of your head about things you had done before though you may have forgotten how you did it.
That's where the blog will come in and help you.