Get Timestamp value for Date column in SQL Developer.

Most of the times when we look at Oracle tables from SQL Developer we find that the date column not showing the timestamp. A simple way to get the timestamp is using CAST function.

select cast(last_update_date as timestamp) from table_name.

Also there is a mroe easier way to see the timestamp in the settings of SQL Developer.

Tools -> Preferences -> Database -> NLS Parameters
Change Date Format to DD-MON-RRRR HH24:MI:SS

:)

SQL Loader Failing to load more than 255 characters.

This was one of my observations.

We had a sample dataload file defined as :

,mesg_type CHAR "ltrim(rtrim(:mesg_type))"
,mesg_priority CHAR "ltrim(rtrim(:mesg_priority))"
,mesg_description CHAR "ltrim(rtrim(:mesg_description))"


The program was failing to load the data when the length of the field is more than 254 characters.

Issue:
NOTE: The default data type in SQL*Loader is CHAR(255). To load character fields longer than 255 characters, code the type and length in your control file. By doing this, Oracle will allocate a big enough buffer to hold the entire column, thus eliminating potential "Field in data file exceeds maximum length" errors. Example:

Fix:
,mesg_description CHAR(4000) "ltrim(rtrim(:mesg_description))"