Re: Unicode and Oracle

On 18 Nov 2005 09:09:46 -0800, ebarrett_at_metastorm.com wrote:

>I have been looking at what's involved in migrating our product to
>unicode. While most issues have been successfully resolved, I
>recently ran into a problem which raised further questions.
>
>There are a number of points in our stored procedures where direct SQL
>is employed. This is used where the table name is only known at
>runtime. So this involves usage of EXECUTE IMMEDIATE; also the OPEN
>... FOR construct for returning a cursor to a recordset.
>
>The difficulty arises when I attempt to run direct SQL which refers to
>a table name which is unicode. The reason it's a problem is that
>EXECUTE IMMEDIATE does not accept a unicode SQL command (ie N'SELECT *
>FROM ...'). Unfortunately, neither can I pass in the table name as a
>parameter with the USING clause - Oracle doesn't allow this for table
>names. Meaning that in effect any table name I pass in has to be
>coerced into an ANSI string. I'd prefer not to mandate Roman script
>to, say, a Chinese customer if I don't have to.
>
>All of which raised another question: does Oracle support unicode
>table or column names? I've done some searching on this and been
>unable to arrive at a definite conclusion.
>
>I'd be much obliged if someone could shed some light on this for me.
>The Oracle versions of interest are 9.2 and later.
>
>Thanks in advance,
>
>Ed Barrett
>Metastorm Inc.

You are aware of the difference between the National characterset and the characterset? The example you provide seems you want to use the National characterset for DML, which is very strange as that isn't the purpose of the National characterset.
In short, the characterset of the database determines which characters are allowed in SQL. The national characterset (when indicated, and when the affected column is a NVARCHAR2) indicates which characters can be stored in the table.
You would never be able to prefix any dynamic statement with N', as the national characterset isn't used in SQL statements.

--
Sybrand Bakker, Senior Oracle DBA