I was trying to create a trigger in Oracle last night with cfquery and it wouldn't work. Oracle kept reporting that the trigger was invalid. If I copied the same exact SQL into Oracle directly, it would work. When I would look at the invalid trigger in Oracle and copy the SQL from there into Oracle, it would fix the trigger, but still I couldn't get it to work from there.
When I checked the error, I found the following: PLS-00103: Encountered the symbol "" when expecting one of the following:
I couldn't figure out what the problem was. Fortunately, someone else figured it out for me: http://www.splendidcrm.com/Blog/tabid/70/EntryID/14/Default.aspx
As they describe, a Windows carriage return doesn't work in Oracle. Oddly, it worked for in my CREATE TABLE statement but not in my CREATE TRIGGER statement. I am guessing this is because the carriage return is significant in the syntax for a trigger, but not for a table.
Anyway, I removed all of the carriage returns in my code and replaced them with #chr(10)# and that did the trick.
Does this mean that DataMgr is about to support Oracle? We'll see...