Re: Oracle ODBC newline character translation

Martin v. Weymarn wrote:

> 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