Since time immemorial, we were told by PeopleSoft that all numeric, character or required fields in their applications are created as NOT NULL columns in the database. And so we confidently repeat the mantra and build our SQL accordingly.
But it is not completely true!
Long Character fields in PeopleSoft that are not defined as 'required' in the Application Designer are nullable in the database. Long Character field are usually created as LONG columns in the database unless their length is defined as between 1 and 2000 characters, in which case they are created as VARCHAR2 columns.
So a Long Character field of between 1 and 2000 characters becomes a nullable VARCHAR2 column in the database (it has taken me 10 years to find this, and it's yet another piece of errata in my book!).
The edit and key options in PeopleTools are restricted on a long field, but nonetheless it opens some interesting possibilities. Including sparse indexes on nullable VARCHAR2 columns, where you identify rows for a query with non-null flags, and set the flag to null afterwards, thus removing it from the index.
Subscribe to:
Post Comments
(
Atom
)
@go-faster.co.uk on BlueSky
Posts
2 comments :
Setting a numeric field as the System ID field in the Record Properties causes it to be created without the NOT NULL constraint.
Any thoughts on how this could be expanded to any number field?
- Charlie
cnbenson@yahoo.com
Charlie, you have spotted a special case. It is a specific exception to the general rule that all numeric and character columns are always NOT NULL. As far as I know, it cannot be extended to other numeric columns. However, if your concern is 'ORA-01400: cannot insert NULL into...' errors, then you could set a column default value, even if it is only 0 (see http://blog.psftdba.com/2007/05/column-default-values-in-peopletools.html).
Post a Comment