Re: Unicode and Oracle

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.

According to point 6 in
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements008.htm#sthref817 it is perfectly ok to use Unicode characters for a table name as long as you refer to it in double quotation marks.

The table names must be in the database character set and not in the national character set, however.

So it should be:

EXECUTE IMMEDIATE 'SELECT * FROM "MYSCHEMA"."MYUNICODETABLENAME"' and not

EXECUTE IMMEDIATE N'SELECT * FROM MYSCHEMA.MYUNICODETABLENAME' Yours,
Laurenz Albe Received on Mon Nov 28 2005 - 02:18:36 CST