I am using a myscript.cgi, written in Perl, to query an Oracle table,
and display a few fields in a browser. One field is of the type LONG
or CLOB, depending on which Oracle Server I am querying. If I run the
"select myfield from mytable" command from a Windows Oracle client, I
can see the field correctly. If I run the same command within 'sqlplus
user/password' session within FreeBSD, the text of the CLOB field is
not shown correctly.

I think the problem is two-fold. I am now passing (hopefully) the
correct ora_charset to DBI->connect ("db-details", "user",
"password" , {ora_charset => correct_set}). But the browser still
would not show the field correctly. I wonder whether I need to specify
a parameter like 'ora_charset' to the 'encoding type' for the html
page, and whether some library like 'use utf-8' is available which
will help solve the issue. If there is any CLPerlM forum FAQ which
addresses the issue, please do point me to it, and I shall be
thankful.

------------------

dn.perl (AT) gmail (DOT) com wrote:
When generating the HTML page, myscript.cgi needs to print the correct
content-type header with charset=UTF-8.

--
Gunnar Hjalmarsson
Email: http://www.gunnar.cc/cgi-bin/contact.pl

On Feb 17, 3:04 am, dn.p... (AT) gmail (DOT) com wrote:
I added the correct content-type header with charset=UTF-8. But that
wasn't enough. I was told I should add ora_charset parameter to
database-connect call.
my $dbh = DBI->connect("DBI:Oracle:{SID}", "{UID}", "{PWD}",
{ora_charset=>'AL32UTF8'}) || die "Could not connect to the database";

Even that wasn't enough.
Then I set env-variable NLS_LANG, and that did the trick.

#! /usr/local/bin/perl

$ENV{'NLS_LANG'}='AMERICAN_AMERICA.UTF8';
$ENV{'ORACLE_HOME'}='oracle-home';
$ENV{'TNS_ADMIN'}='tns-admin';

print "Content-type: text/html; charset=utf-8\n\n";

use DBI;

my $dbh = DBI->connect("DBI:Oracle:{SID}", "{UID}", "{PWD}",
{ora_charset=>'AL32UTF8'}) || die "Could not connect to the database";
$dbh->{LongReadLen} = 50000;
$dbh->{LongTruncOk} = 1;


However my code still isn't working smoothly. There is a database
field of the type CLOB. On the server I am using, my code works for
CLOB fields with length upto 1554 but fails for a CLOB field with
length=1568. I bumped up LONGREADLEN from 20,000 to 50000 but no luck.
sql_str = select nvl(lf.fld01, 'No Content') from mytable m, longfld
lf
where m.longid = lf.longid ;
prepare, execute, etc etc ...
while (my @row = fetchrow_array ...)

This fetchrow_array call fails when CLOB field fld01 has length=1568,
and I get the error:
ORA-01406: fetched column value was truncated (DBD: ORA-01406 error on
field 1 of 1, ora_type 112 ....

Yet the same code works on another server for CLOB fields of length
1568 and above. A field of length 1946 was correctly read by the perl
script on that server.

My issue must be with system settings or env variables. What could be
going wrong here?

Eventually I want this cgi script to work as an FAQ Finder from a web
browser.

Thanks in advance.