by Need to suggestions re a strange problem I'm having in a program using OCI
calls to an Oracle 8 database.
The SQL statement looks something like this --
SELECT * FROM ADDRESS_TABLE WHERE ZIPCODE=:Zipcode
AND SOUNDEX_STREET_NAME=:Street AND HOUSE_NUMBER LIKE :House
All three of the columns are defined in the table as char(), not varchar(),
because leading and trailing spaces are significant.
The target :Zipcode is a 5-byte field, bound to the variable with a length
of 5, and containing a five-digit Zipcode. The column in the database is
also 5 characters.
The target for :Street is an 8-byte field, bound to the variable with a
length of 8; the data in that field consists of a 4-byte Soundex string and
4 spaces. The column in the database is char(8) and the field is space
filled.
The target for "House is a 6 byte field, bound to the variable with a length
of 5, and containing something like " 25%". The column in the database is
char(6). [The purpose of this statement is to find all of the addresses in
a specific Zipcode, on a specific street, and in a specific block, i.e., the
last two digits of the house number have been truncated and replaced by a
single %.]
When executed directly from SQL Worksheet, with single quotes surrounding
the data values, the statement works as planned. When executed from the OCI
call library, the statement returns NO_DATA. [The call library interface
works; if I truncate the statement to just the Zipcode part, everything
works fine, but returns rather a lot of records.]
Ideas???
TIA,
Rog
--