Threaded Mode | Linear Mode

sandeep · (This post was last modified: 11-19-2009 09:08 PM by sandeep.)

Current Timestamp CURRENT_TIMESTAMP

Code:

SELECT CURRENT_TIMESTAMP FROM dual;

Loading current timestamps

The Loader can insert values into columns with a timestamp data type based on a reading of the time-of-day clock. For example, the STARTDATE and ENDDATE of an offer in WebSphere Commerce can have values based on the time at which the offer is inserted into the table. To support this functionality, the Loader package uses the MLTIME table to keep the timestamp instances. The schema for this table is as follows:

Code:

table MLTIME

    ( 

    INSTANCEID BIGINT not null,

    MLTIMESTAMP TIMESTAMP

    )

1. Modify the values of the properties specified in the new Loader customizer property file.

The name of the table and its columns can be customized by changing the following properties in the Loader customizer property file:

Code:

TimestampTableName = MLTIME

        TimestampIdColumn = INSTANCEID

        TimestampValueColumn = MLTIMESTAMP

The input data for specifying current-timestamp values are based on timestamp string patterns. The following masks are used for specifying the durations for the timestamp:

%D for days
%M for months
%Y for years
%H for hours
%m for minutes
%s for seconds

You can customize current-timestamp formats by modifying or adding masks in the Loader customizer property file. The following input masks are provided:

Code:

InputCurrentTimestampFormat.1 = CURRENT TIMESTAMP

        InputCurrentTimestampFormat.2 = CURRENT TIMESTAMP %D DAYS

        InputCurrentTimestampFormat.3 = CURRENT TIMESTAMP %D DAYS %M MONTHS

        InputCurrentTimestampFormat.4 = CURRENT TIMESTAMP %D DAYS %M MONTHS %Y YEARS

        InputCurrentTimestampFormat.5 = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS

        InputCurrentTimestampFormat.6 = SYSDATE

        InputCurrentTimestampFormat.7 = ADDDAYS(SYSDATE,%D)

        InputCurrentTimestampFormat.8 = ADDDAYS(ADDMONTHS(SYSDATE,%M),%D) 

        InputCurrentTimestampFormat.9 = ADDDAYS(ADDMONTHS(ADDYEARS(SYSDATE,%Y),%M),%D)

Input data for the current timestamp is matched with the specified patterns. If the data matches a specified input pattern, that pattern is used to parse the input data and the Loader converts the data into the appropriate output format before inserting it into the database. New patterns can be added to the above list provided the subscript numbers are ordered sequentially.

2. Set the target output formats for specifying current timestamps:
1. CurrentTimestampFormat.Load is used when the Loader is operating in load or import mode.
2. CurrentTimestampFormat.JDBC is used when the Loader uses JDBC to insert, update, or delete values in the database.

The default target patterns in the Loader are as follows:

Code:

CurrentTimestampFormat.Load = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS 

          %h HOURS %m MINUTES %s SECONDS 

        CurrentTimestampFormat.JDBC = CURRENT TIMESTAMP %Y YEARS %M MONTHS %D DAYS

          %h HOURS %m MINUTES %s SECONDS

When you customize the CurrentTimestampFormat.Load and CurrentTimestampFormat.JDBC properties, you should make sure that the syntax of the resulting statement is valid for the given database management system.

The CurrentTimestampLiteral property is used by the Loader to make an early determination of whether the value for the timestamp column is in a current-timestamp format, thus avoiding expensive computations to determine that the value is not a string representation of timestamp.

CurrentTimestampLiteral = CURRENT TIMESTAMP

Possibly Related Threads...
Thread:		Author	Replies:	Views:	Last Post
	determine the trace file for each Oracle Database process	nbadmin	0	32	11-06-2009 04:45 AM Last Post: nbadmin
	Package and Upload Diagnostic Data to Oracle Support	nbadmin	0	94	11-06-2009 04:36 AM Last Post: nbadmin
	ADR in an Oracle Real Application Clusters Environment	nbadmin	0	45	11-06-2009 04:25 AM Last Post: nbadmin
	Oracle Database Fault Diagnosability Infrastructure	nbadmin	0	62	11-06-2009 04:16 AM Last Post: nbadmin
	Controlling When Oracle Database Writes to Trace Files	nbadmin	0	33	11-06-2009 04:08 AM Last Post: nbadmin
	Oracle Database Background Processes	sreekanth	0	59	11-06-2009 02:09 AM Last Post: sreekanth
	Configuring Oracle Database for Shared Server	sreekanth	0	32	11-06-2009 01:49 AM Last Post: sreekanth
	stop and start the components in an Oracle home while installing a patch	yoga	0	114	11-06-2009 01:32 AM Last Post: yoga
	Stopping and Restarting Oracle Restart for Maintenance Operations	yoga	0	24	11-06-2009 01:30 AM Last Post: yoga
	Starting and Stopping Components Managed by Oracle Restart with SRVCTL	yoga	0	32	11-06-2009 01:29 AM Last Post: yoga