-
Let me start by saying that I have not investigated this thoroughly, but...
There is a table in one database where all the columns are defined as NOT NULL. There is a package in another database that performs INSERTS on this table. This is done through a dblink and a synonym. Now, this INSERT is an INSERT...VALUES statement and one of the values is an explicit NULL into an explicitely NOT NULL column. The INSERT does not appear to fail. This code has been running successfully in production for a while (per one of the programmers). Looking at the table itself, all the rows for this particular column have a single space in them. Actually, many of the columns look like this for many of the rows.
Now,
- There are no defaults on any of the columns.
- There are no triggers on this table
- The NOT NULL constraints are all enabled
Yet, an explicit NULL inserted into a NOT NULL column *seems* to be converted to a space.
What am I missing here?
- Chris
-
chris, you seem to have covered all the bases; at this point i would start to doubt what is assumed to be happening. can you grab the SQL from destination database to be sure that the package said to be doing the work is what is truly being run?
that's all i can think of. d.
-
Chris,
Do you have CHAR fields that are automatically getting padded with spaces by Oracle?
-John
-
John - Forgot to mention that part - VARCHAR2 fields - sorry :) - good catch though
DBAtrix - Agreed - just don't have the time to really rip it apart at the moment and everything *seemed* to be as they said. I checked the table definition, I looked for triggers and defaults, I saw the INSERT statement with the NULL value and I saw the data with the spaces. The only thing I can think of is that the statement they showed me is not the one being run, or it ran against a different copy of thee table, etc. but the developer that showed me it is quite sharp, so that didn't seem likely (although still possible, of course :) ).
Perplexing...
Any other thoughts out there?
- Chris
-
Chris,
You could put a trigger on the table to help you investigate the problem.
Make an 'audit' table with column names like sessionid, username, maybe some other fields like the ones in v$session, statement_type (insert or update), and rowid (which will contain the rowid of the table being audited)
Then put an insert trigger on the table and an update trigger on the table...have your triggers populate this 'audit' table.
Then sit back and wait. :-) Combine this with some session
level traces and you should have your answer muy pronto.
-John
