PostgreSQL Discussion - DBI-Link, Oracle, database encoding

Author

DBI-Link, Oracle, database encoding

Hannes Dorbath

2006-10-25, 8:21 am

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_a
merica.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

Albe Laurenz

2006-10-26, 12:13 am

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_a
merica.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

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

David Fetter

2006-10-26, 12:13 am

On Wed, Oct 25, 2006 at 03:47:20PM +0200, Albe Laurenz wrote:
> Hannes Dorbath wrote:
>
> 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/...bi-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!

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql
.org so that your
message can get through to the mailing list cleanly

Hannes Dorbath

2006-10-26, 7:18 pm

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_AM
ERICA.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

2006-10-27, 7:26 pm

On Fri, Oct 27, 2006 at 09:05:44AM +0200, Albe Laurenz wrote:
> David Fetter wrote:
>
> I will try again when the need arises and/or when I find time :^)

Great :)

>
> 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!

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

Sponsored Links