| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Running Function in System Package through
I have a UTF8 database (db1) which has a db link to a US7ASCII database
(db2) to access some Big5 Chinese data. I know it is wrong to have BIG5
data in a US7ASCII database, but this is historical. Anyway, I can fool
oracle to think the data is valid by setting NLS_LANG to
AMERICAN_AMERICA.US7ASCII on the client.
The problem is that db1 (natually) starts with NLS_LANG=AMERICAN_AMERICA.UTF8. As a result, when accessing db2, Oracle does character conversion and the data become rubbish. I tried to use utl_raw to do some conversion on remote database and have some progress:
(running from db1)
select SYS.UTL_RAW.CAST_TO_RAW_at_etet(mydata) IN_BIG5,
SYS.UTL_RAW.CONVERT_at_ETET(
SYS.UTL_RAW.CAST_TO_RAW_at_ETET(mydata),
'AMERICAN_AMERICA.UTF8', 'AMERICAN_AMERICA.ZHT16BIG5') IN_UTF8
from mytable_at_db2
/
IN_BIG5 IN_UTF8
-------- --------
A445 E4B99D
So far so good, as the Chinese character with BIG5 code 'A445' is 'E4B99D' in UTF8.
The problem is that when I want to convert the raw back to UTF8 in the local database, something strange happened...
(running from db1)
select SYS.UTL_RAW.CAST_TO_RAW_at_db2(mydata) IN_BIG5,
SYS.UTL_RAW.CAST_TO_VARCHAR2( SYS.UTL_RAW.CONVERT_at_db2( SYS.UTL_RAW.CAST_TO_RAW_at_db2(mydata),
IN_BIG5 IN_UTF8
-------- --------
2445 $E
It seems that all UTL_RAW using the converted data, which corrupt the BIG5 data. Note that the IN_BIG5 column is still 'called SYS.UTL_RAW.CAST_TO_RAW_at_db2(mydata)' but the result is different.
Is this a bug? Is it impossible to execute (some?) system packages both in remote and local database in a single SQL? Besides creating a view on remote database and (some one will say) fix the US7ASCII database, is there any solution?
Some more information:
1) I tried to add/remove the 'SYS.' and '@db2' through out the UTL_RAW
calls and even added '@db1' for the call I want to execute locally. It
seems whenever any UTL_RAW call is run locally, all is run locally.
2) Database versions (as displayed when login sqlplus)
db1:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
db2:
Oracle8i Release 8.1.7.4.0 - Production
JServer Release 8.1.7.4.0 - Production
3) I tried to create a package/function with the same name in both db
and there is no problem running a function in the desired database.
SELECT MYPACK.MYFUNC(3) A, MYPACK.MYFUNC_at_DB2(3) B
FROM DUAL;
A B
-------- --------
15 30
(MYPACK.MYFUNC is defined as 5 x argument in db1 and 10 x argument
in db2.)
Received on Tue Jan 11 2005 - 02:49:26 CST
 |
 |