SQL & PL/SQL

Hi!

I have a strange problem( bug?) in pl/sql with national characters.

Im my db, I have some tables with columns defined like nested table. Some nested tables defined like a collection of NVARCHAR2(4000). When I try to select this kind of column with a simple query in toad or sql*plus, there is no problem, I got the data.

See below, for the example:

CREATE TYPE C_DATA as TABLE of NVARCHAR2(4000);

CREATE TABLE MY_TABLE (
DATA C_DATA
)
NESTED TABLE DATA STORE AS NT_DATA;

select d.COLUMN_VALUE from my_table,table(data) d;

When I want to make the same query in pl/sql procedure, a compilaton error occurs: "PL/SQL: ORA-12714: invalid national character set specified".

Here is a sample code:

CREATE or replace PACKAGE TEST AS

FUNCTION try_this (
param NUMBER
) RETURN INTEGER;

END TEST;

CREATE or replace PACKAGE BODY TEST AS

FUNCTION try_this (
param NUMBER
) RETURN INTEGER IS

val NVARCHAR2(4000);

BEGIN
select d.COLUMN_VALUE into val FROM MY_TABLE, TABLE(DATA) d
where rownum=1;

return 1;

END try_this;

END TEST;

What can I do? What's wrong? My character settings are ok. (See below)

I already spent much time to try to find a workaround but nothing goes.

thanks for your help,
Tristan

See below for my NLS settings.

NLS settings:

NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CHARACTERSET UTF8
NLS_DATE_LANGUAGE AMERICAN
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET UTF8
NLS_RDBMS_VERSION 9.2.0.4.0