Morning all,
Perhaps... however SQL Plus does handle it and provides the
data. I leave the philosophical ramifications of whether or
not that's right/wrong up to others to discuss and simply
point out Oracle (at least the database and SQL*Plus tool)
handles it 
SQLPlus is, I believe, written in C and so should suffer from the NULL
character - chr(0) - terminating the string. However, I am of the
opinion that what SQLPlus does behind the scenes in some OCI sort of
way, is not to retrieve a VARCHR2 or CHAR as a simple C string but as an
array of bytes the length of which is known and defined by the length
indicator (for VARCHAR2) or the column definition (for CHAR). I assume
that when passing said data around, the length is also passed - at least
in SQL*Plus anyway.
So a VARCHAR2(100) with a data length of 17, containing any number of
chr(0) will be returned as an array of bytes 17 in length and not as a
string of length 17 because the string would be terminated at the first
chr(0) as C strings (and I use the term loosely) normally are.
I rather suspect the fact that a C string is actually just an array of
bytes, terminated by a chr(0), that chr(0) is not the problem. It's more
likely to be the routines used to display/print/send said array of bytes
back to the application that is at fault.
If the routine takes a plain array of bytes as input then it will,
unless given a length to work with, stop after the first chr(0).
In the definition for the (scalar) OCIDefineXXXXX functions, there are
two parameters of note:
-
Valuep which is a pointer to a buffer to hold the result of a SELECT,
for example.
-
Rlenp which is a pointer to a variable to receive the length of the
returned data. So, technically, a chr(0) in the middle of a VARCHAR2
won't cause truncation of the data.
Now the "lazy" developer could just copy the data from the buffer
without considering the length and potentially truncate the result at
the first chr(0) - possibly using strcpy() which terminates after
copying the chr(0).
However, if the developer considers the length of the returned data and
copies that many bytes then chr(0) will also be copied. Using memcpy()
or memmove() for example, copies a given number of bytes regardless of
any chr(0) in their midst.
Unfortunately, what happens after the OCI system returns the data to the
calling (OCI Components?) is anyone's guess.
My suspicions fall on either how the OCI component developer(s) did what
they did or how Delphi handles a chr(0) in a byte stream.
Hope I didn't bore you all too much!
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
And I'm sorry that you have nulls