Laurenz Albe wrote:
> 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
Which is a bad idea...
Once a table "MyUniCodeTablename" is created you rmust always refer to
it as "MyUniCodeTablename" - case and quotes included.
--
Regards,
Frank van Bortel
Top-posting is one way to shut me up...
Received on Tue Nov 29 2005 - 14:43:25 CST
