Saturday, September 22, 2007

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.

3 comments:

Incindium said...

This bug is fixed in Oracle patch
10.2.0.4 for anyone else who has run into this issue as well.

Alexander Schmidt said...

Thanks for a tip! Since sometimes it takes time to roll out the patchset, this is a nice workaround.

Anonymous said...

Brilliant - we were also effected by bug 2965256 and this helped lots

Labels