Обсуждение: DBI-Link, Oracle, database encoding
After some trouble we managed to get up DBI-Link between PG 8.1.5 and Oracle 9i. Oracle is on LATIN1 (I think) and the PG database runs on UTF8. We have some encoding problems with it. We tried setting NLS_LANG='american_america.AL32UTF8' for Oracle, which works for SqlPlus, but not DBD::Oracle (as it seems to me). Has anyone experience with such a setup? My knowledge on Oracle is limited. Thanks for any hint. -- Regards, Hannes Dorbath
Hannes Dorbath wrote: > After some trouble we managed to get up DBI-Link between PG 8.1.5 and > Oracle 9i. > > Oracle is on LATIN1 (I think) and the PG database runs on > UTF8. We have > some encoding problems with it. > > We tried setting NLS_LANG='american_america.AL32UTF8' for > Oracle, which > works for SqlPlus, but not DBD::Oracle (as it seems to me). > > Has anyone experience with such a setup? My knowledge on > Oracle is limited. I know about Oracle, but have not yet managed to setup DBI-Link. NLS_LANG need to be set in the environment of the process using the Oracle client. In the case of a stored procedure (as in DBI-Link) this is the PostgreSQL server. So make sure that the postmaster process has NLS_LANG set appropriately in its environment. The second problem is the correct value for NLS_LANG. The codepage in NLS_LANG must be the codepage used by the application that accesses Oracle. It should NOT be set to the database codepage. I don't know what codepage the PL/Perl program that is DBI-Link uses, but my guess is that it is the database codepage. So if your database is UTF8, use AL32UTF8. If your database is LATIN1, use WE8ISO8859P1. If your database is LATIN9, use WE8ISO8859P15. Maybe somebody else with more insight into DBI-Link than me can provide better information. Yours, Laurenz Albe
On Wed, Oct 25, 2006 at 03:47:20PM +0200, Albe Laurenz wrote: > Hannes Dorbath wrote: > > After some trouble we managed to get up DBI-Link between PG 8.1.5 > > and Oracle 9i. > > > > Oracle is on LATIN1 (I think) and the PG database runs on UTF8. We > > have some encoding problems with it. > > > > We tried setting NLS_LANG='american_america.AL32UTF8' for Oracle, > > which works for SqlPlus, but not DBD::Oracle (as it seems to me). > > > > Has anyone experience with such a setup? My knowledge on Oracle is > > limited. > > I know about Oracle, but have not yet managed to setup DBI-Link. Try it again :) http://pgfoundry.org/projects/dbi-link/ If you are using DBI-Link, please sign up for its mailing list on pgfoundry. http://lists.pgfoundry.org/mailman/listinfo/dbi-link-general > NLS_LANG need to be set in the environment of the process using > the Oracle client. Is there some way to set this in the connection string for DBI? > In the case of a stored procedure (as in DBI-Link) this is the > PostgreSQL server. So make sure that the postmaster process has > NLS_LANG set appropriately in its environment. DBI-Link 2.0beta1 provides some infrastructure for setting environment variables. Any suggestions would be welcome. > The second problem is the correct value for NLS_LANG. > The codepage in NLS_LANG must be the codepage used by the > application that accesses Oracle. It should NOT be set to the > database codepage. > > I don't know what codepage the PL/Perl program that is > DBI-Link uses, but my guess is that it is the database codepage. > > So if your database is UTF8, use AL32UTF8. > If your database is LATIN1, use WE8ISO8859P1. > If your database is LATIN9, use WE8ISO8859P15. > > Maybe somebody else with more insight into DBI-Link > than me can provide better information. I'm the author. I hope I have a little ;) Cheers, David. -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!
I have it working fine now. Seems PG indeed did not have access to the env vars, because of the init script I was using. export NLS_LANG=AMERICAN_AMERICA.AL32UTF8 export NLS_NCHAR=AL32UTF8 pg_ctl restart fixed it for me. > If you are using DBI-Link, please sign up for its mailing list on > pgfoundry. I will, though it works fine for me now I have some more questions :) > DBI-Link 2.0beta1 provides some infrastructure for setting environment > variables. Any suggestions would be welcome. Did I miss it in the Readme or is it not documented? Anyway, thanks for creating this piece of software. It saved me days of work and some ugly hacks. -- Regards, Hannes Dorbath
David Fetter wrote: >> I know about Oracle, but have not yet managed to setup DBI-Link. > > Try it again :) > http://pgfoundry.org/projects/dbi-link/ I will try again when the need arises and/or when I find time :^) >> NLS_LANG need to be set in the environment of the process using >> the Oracle client. > > Is there some way to set this in the connection string for DBI? You, the author of DBI-Link, know more about DBI than I do. My guess: No. You cannot pass the desired codepage to Oracle client at connect time. The only possible way to tell Oracle client what codepage you want is with the environment variable NLS_LANG. Also, you cannot change the client codepage during a session. Don't ask me why they designed it that way. Yours, Laurenz Albe
On Fri, Oct 27, 2006 at 09:05:44AM +0200, Albe Laurenz wrote: > David Fetter wrote: > >> I know about Oracle, but have not yet managed to setup DBI-Link. > > > > Try it again :) > > http://pgfoundry.org/projects/dbi-link/ > > I will try again when the need arises and/or when I find time :^) Great :) > >> NLS_LANG need to be set in the environment of the process using > >> the Oracle client. > > > > Is there some way to set this in the connection string for DBI? > > You, the author of DBI-Link, know more about DBI than I do. Not necessarily. This is a Perl and Oracle issue. DBI-Link just uses DBI's facilities without further elaboration. > My guess: No. > You cannot pass the desired codepage to Oracle client at connect > time. Good to know. > The only possible way to tell Oracle client what codepage you want > is with the environment variable NLS_LANG. Also, you cannot change > the client codepage during a session. Don't ask me why they designed > it that way. I'm sure it seemed like a good idea at the time ;) Cheers, D -- David Fetter <david@fetter.org> http://fetter.org/ phone: +1 415 235 3778 AIM: dfetter666 Skype: davidfetter Remember to vote!