Just because you can do something, doesn’t necessarily mean that you should. Case in point, database object names. Yes, you can call a table in Oracle pretty much anything you want, including ‘TABLE.’ Using quotes allows you to do some pretty silly things like use reserved words, mixed case names, and worse.
On a call today someone mentioned that they found it odd that you could have a table and an index share a name. Yes, you CAN do that. But maybe you could try not to do that?. Just for readability sake, try a little harder maybe?
Here’s a fun one, a table called ‘INDEX.’ Which has a column named ‘INDEX.’ Which has an index called ‘INDEX.’ You laugh, but I have seen some crazy stuff out there. I shouldn’t have been surprised, but I was to find that SQL Developer fully handled this scenario. You see, even our testers have to spend precious time making sure that the stuff you CAN do but shouldn’t, actually works. I can only imagine what kind of fun the parser folks have with this kind of stuff.
Don’t try this at home, kids!
What weird stuff have you been asked to support?
Here’s another fun example from the field. A very large organization had decided to store the lookup values for every ID in every table they were referenced so as not to make it ‘hard’ for their support staff to join them with the appropriate parent tables. Imagine SCOTT.EMP with a ‘DeptName’ field that accompanied the ‘DEPTNO’ field. I think that’s what known as -1 Normal Form.
I’m sure you can top me. Let’s hear it!
6 Comments
Hi Jeff,
oh yes, ACCESS->Oracle via ODBC, just say no! I think that document is still on the Toad files area. I’d forgotten about that one. shudder!
Cheers,
Norm.
I once got this from a vendor:
alter table x
add constraint x_col_chk check (col in (‘Y’,’N’,NULL));
The NULL bit renders the whole thing broken, and ANYTHING gets accepted as being valid.
Gruesome details of why the above is a major fail at http://toadfororacle.com/thread.jspa?threadID=29681 start at the top and scroll down. (Stating the obvious again!)
Cheers,
Norm.
How many horror stories start or end with NULL? Has to be one of the least understood concepts in the relational database world.
Thanks for sharing Norm, but this isn’t as bad as your ACCESS > Oracle Explain Plan you published a few years back!
I had once a customer with a 9i table called RECYCLE_BIN, which was not a protected name at that time, but ancient versions sql developer did not like it
https://forums.oracle.com/forums/thread.jspa?threadID=419271
Yesterday also I saw a table with a column called “NUMBER”.
I also remember a column “age” in german (ALTER) that caused issues
Your customer invented the RECYCLE BIN! He should be applauded 🙂
Funny, I saw “number” too, before coming here from the “did you see it” tweet: https://forums.oracle.com/forums/thread.jspa?messageID=10608864&tstart=0