Using Dynamic Sql to work around PL/SQL bugs

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

Processing….

END 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)'

USING p_question_tab_i;

The above example works correctly…

question_tab TABLE OF QUESTION_OBJ
is
SELECT_TYPE VARCHAR2(10)
DISPLAY_TEXT NVARCHAR2(4000)
PROMPT_TYPE_CODE VARCHAR2(50)

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.