Oracle ODBC newline character translation
Martin v. Weymarn
I am having a problem with the translation of newline/return
characters in my returned data. Our company is using a web-based
application in which users enter data that gets written to an Oracle
database. I am querying that database directly through Excel using an
ODBC connection.
Special characters are coming through just fine so I think I am on the
right track regarding the character set. However at the end of each
paragraph Excel shows a small rectangle character (don't know the
code). Is there some way to filter these out, preferrable through a
setting in the ODBC driver? I have not been able to find any
information regarding the Translation Options setting in the driver.
I am running Oracle ODBC version 8.01.78.00 to connect to an Oracle
7.3 database running on a UNIX server. Excel version is 2000. In the
registry, NLS-LANG is set to AMERICAN_AMERICA.WE8ISO8859P15.
Unfortunately I did not find any solution to this problem on the web
or the newsgroups, that's why I am asking here...
Thank you for your help,
Martin.
Billy Verreynne
> I am having a problem with the translation of newline/return
> characters in my returned data.
Whatever the user types in character wise, is send to Oracle and thus stored
by Oracle.
When pulling the data out, those special characters need to be interpreted
by the client in order to display the data in the correct format.
I doubt that messing around with NLS language settings will solve this.
Irrespective of that setting, special characters can be entered and will be
stored if send to Oracle as part of the data for a row. The question is how
do you handle displaying them.
The first option is to filter out special characters before the
insert/update in Oracle. That can be done via a trigger, PL/SQL package or
in the client/CGI itself.
The second option is to handle those characters at display time. This can be
done using the TRANSLATE function, e.g.
SELECT
TRANSLATE( column1, CHR(10), ' ') /* replace a char(10) with a space */
from foo
The best way to handle this type of thing, is in the database itself.
Implementing a GIGO prevention filter of sorts. Be wary though of
performance overheads.
--
Billy
Joel Garry
> Hi all,
>
> I am having a problem with the translation of newline/return
> characters in my returned data. Our company is using a web-based
> application in which users enter data that gets written to an Oracle
> database. I am querying that database directly through Excel using an
> ODBC connection.
>
> Special characters are coming through just fine so I think I am on the
> right track regarding the character set. However at the end of each
> paragraph Excel shows a small rectangle character (don't know the
> code). Is there some way to filter these out, preferrable through a
> setting in the ODBC driver? I have not been able to find any
> information regarding the Translation Options setting in the driver.
To add to what Billy said, you can figure out what that character is
by using od (octal debugger) in several ways. Probably the easiest
way is get on the server and spool the selected data to a file, and
run od -c on the file (hopefully it comes out there - if none of these
methods work, the character is being added by ODBC or Excel). Other
ways are to run it on an export file, or on the actual oracle data
file itself (use a backup copy). The general trick is to pipe the
output into more, and then search (use the slash) for known text data
near the character. You'll be searching for the text with some extra
spaces between the characters, play around with a small text file
first to see how it works.
>
>
> I am running Oracle ODBC version 8.01.78.00 to connect to an Oracle
> 7.3 database running on a UNIX server. Excel version is 2000. In the
> registry, NLS-LANG is set to AMERICAN_AMERICA.WE8ISO8859P15.
>
> Unfortunately I did not find any solution to this problem on the web
> or the newsgroups, that's why I am asking here...
>
> Thank you for your help,
> Martin.
jg
--
@home is bogus.
Never meet your heroes.
Frank
I think both previous posters are wrong. The moment uou mentioned the
little rectangle I thought Unix to Windows. 2 lines further down
you confirm this.
It's a DOS thing: It relies on a CR/LF for an end-of-line, where unix
uses CR (of LF - the chr(10) anyway). If you do a
replace(chr(10), chr(10)||chr(13)) you will see a major change.
On WindBlows, you can replace them (copy rectangle, search, press
enter on all found, and use F3 to find next).
To prove the point: after all rectangles are gone, you owill get a found
character at the end of every line.
--
Regards, Frank van Bortel
Joel Garry
I don't disagree, but why would ODBC _do_ this?
jg
--
@home.com is bogus.
http://www.signonsandiego.com/news/uniontrib/fri/news/news_1n4audit.html