Oracle extension oddity

Last modified date

Comments: 0

Today I was working on some sql for Oracle and connecting to the database with the PHP4 OCI extension. I’ve done this many times before, but today ran in to a little oddity that I thought was worth mentioning (mainly so I don’t forget and fall in to the trap again!)…

More often than not I’m pulling back fields and using their default name or in addition to the ‘as’ keyword to rename it, such as:

t2.email_address as email
name_table t1,
email_table t2
where = t2.user_id(+)[/sql]

And when the dataset is pulled back with OCIFetchStatement the field names are always in uppercase. I personally prefer lowercase so often use the array_change_key_case() function, but in this instance I wasn’t and just accessing the fields in uppercase, such as $results[‘EMAIL’].

Then I changed my sql to have a case statement which looked something like:

t2.email_address like ‘’
or t2.email_address like ‘’
or t2.email_address like ‘’

end “email”[/sql]

And suddenly the email addresses weren’t showing on the site. I ran the sql, and it was bringing back the email, but for some reason they just weren’t displaying when they should.

Turns out that using the double-quote around email_address was forcing the Oracle extension to return the field name in exactly the same way I had requested it – all lower case. I changed that to “EMAIL” and all was right with the world again.

Something for me to bear in mind for the future.


Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.