A coworker encountered this problem. We search the Internet for a solution, but nothing was found. I am hoping that by posting this, others having the same problem will quickly find a solution. Furthermore, this example illustrates a more general principle which is that sometimes things work in SQL which do not work in PL/SQL and a simple solution is to use dynamic sql.
This problem is encountered when trying to access an NVARCHAR2 using a TABLE function in PL/SQL.
ORA-12714: invalid national character set specified
Originally written as:
FOR c IN ( SELECT * FROM TABLE (p_question_tab_i) ) LOOP
Raises error: ORA-12714: invalid national character set specified
Then re-written using dynamic SQL:
OPEN que_cursor FOR 'SELECT * FROM TABLE (:question_tab)'
The above example works correctly…question_tab TABLE OF QUESTION_OBJ
Technically, this is not a PL/SQL bug. It is, however, a feature that is supported in SQL but not PL/SQL. This often happens with new SQL features where PL/SQL has not 'caught up' yet.