Embedded Carriage Returns in Oracle Database

[hnhranch]

I am inputing data through Oracle Forms 6.0 into an Oracle table with a varchar2(4000) length field. The users are hitting a the <ENTER> key in the field when they enter data and the form saves the field with a hard carriage return on the table. When the users report against the field through Microsoft Access they get a box symbol in place of the carriage return. Any idea how to fix the problem either on the form side, the table side, or the Access side?? Thanks for any and all help!!

 

[sem]

The problem is probably in wrong NLS_LANGUAGE. Does your Access application reside on the same box as Forms and use the same settings? English letters have mainly the similar codes in all major encodings but this is not the case for &quot;additional&quot; characters so you may have problems due to character conversion/displaying.

 

[hnhranch]

Yes, the Access and Forms do reside on the same box. But what is the solution to character conversion/displaying on the Access side?? Is there a Find/Replace for embedded carriage returns? I have tried but had no success with the Access Find/Replace. I have used VBA and was thinking that it may be the solution? What about a conversion on the Forms side? Thanks for your reply and I look forward to hearing your response.

 

[sem]

My asumption was false, there was nothing wrong with charsets.
You may eliminate cariage returns in pre-insert trigger by replace, but the formatting will suffer.
The real problem is that Oracle uses linefeed character ASCII 10 (x0A), but Microsoft also adds carriage return ASCII 13 (x0D). So in any case as I suppose you can not get the same output/input for both tools and have to make some conversions during insert or display. The further depends on whether you need to save the data entered by Forms user or just let him prepare information for Access user. Of course if your Acces user does not edit data you may query and display replace(YOUR_COLUMN, chr(10), chr(13)||chr(10)) instead of YOUR_COLUMN.

 

[hnhranch]

Yes, if I replace with say nulls or spaces the formatting side will suffer. The users are using forms as a means of CRUD and Access as a reporting tool. They link to the oracle table and run reports, queries and forms from Access for emailing and faxing. Would you suggest editing the data on the Access with a query?? How? An update query in Access?? Thanks again for the information....

 

[sem]

I think the answer is in my previous posting: just base your report not on table but rather on query. If your table is called MY_TAB and column is MY_COL you may create a query in Access or a view on server side as

select
replace(MY_COL, chr(10), chr(13)||chr(10)) MY_COL
from MY_TAB

Of course this query will not be updatable.