Re: Oracle 7 -> Oracle 8 + 8th bit problem and NLS_LANG

Georges,
I don't think hextoraw approach will work. In fact I don't think this can be done using pl/sql only.

Though you are trying to synchronize the two databases. The Big5 data being present in us7ascii database .. is just plain wrong! Though you probably already know that.

Anyway, data synchronization can probably be done using sqlldr/exp+imp or using scripting languages like perl. But with pl/sql + db links you will probably not be able to do this.
I might be wrong, but I don't think NLS_LANG setting do anything for database links (i.e. in the way you are using it). The problem is that if you do something like this:
insert into o8table as select * from o7table_at_o7;

Oracle will automatically convert the data from us7ascii to Big5. If you then
apply the convert ... which does not work since the conversion from us7ascii to Big5 shouldn't have taken place in the first place.

You would probably only be able to do this using sqlldr, exp+imp ... or use a scripting language (which will spool/dump the data from us7 .. into a flat file
... using a nls_setting of us7ascii ... after which another script will insert the data
into the O8 database .. using nls_setting of Big5).

Is there a reason why you *have* to use pl/sql to do this. Why make life difficult when there might be an easier way. Ok .. how about external procedures ... i.e. write an external procedure which calls a perl script to do the deed?

I actually have a similar situation like yours (Though I don't have to sync any data ...
its just that I do have a database us7ascci with we8 characters in it ... if I get a chance ...
I'll do some experiments with the data ... BTW: we *are* soon converting that db to
utf8 charset).

Anurag

"Georges Ko" <gko_at_gko.net> wrote in message news:m31y8iwq4n.fsf_at_symbiose.dnsalias.net...
> "Anurag Varma" <avdbi_at_hotmail.com> wrote:
>
> > set nls_lang on oracle7 server to american_america.us7ascii
> > ... spool out the data .. or do a direct export (This way
> > .. again, I would not expect oracle to do attempt a charset
> > conversion). set nls_lang on oracle8 machine to
> > american_america.zht16big5 ... load the data using sqlldr ....
> > or import the data.
>
> The problem is that the task I have to do is to synchronize
> between these two databases so I would need something that works in a
> SQL statement in a store procedure.
>
> > * If this does not work then probably either the data in oracle7 is
> > in some other charset ....
>
> Yeah, the data in Oracle 7 is really Big5:
>
> SQL> select rawtohex(data) from custom_at_oracle7db
>
> RAWTOHEX
> --------
> A5DFB0EA
>
> After insertion into Oracle 8, the 8th bit is stripped. I tried
> to convert it back to Big5 string by adding a hextoraw(), but of
> course, it doesn't work...
>
> > Sorry ... I couldn't be of more help.
>
> Thanks for trying !
> --
> Georges Ko (Taipei, Taiwan) 2002-08-29 gko_at_gko.net / ICQ:
8719684
> Cycle 78, year 19 (Ren-Wu), month 7 (Wu-Shen), day 21
(Ji-Si) Received on Thu Aug 29 2002 - 00:12:41 CDT