Oracle: PLSQLAssociativeArray TimeStamp workaround

Oracle (at time of writing) doesn’t support an PLSQLAssociativeArray of type OracleDbType.TimeStamp.
So what you need is to use a standard string instead then convert it to a timestamp. For this you will need to set the timestamp format via:

ALTER SESSION SET NLS_TIMESTAMP_FORMAT = 'DD/MM/YYYY HH24:MI:SS'

Then in your OracleCommand CommandText you can use:

TO_TIMESTAMP(:t(i))

where t is an input OracleParameter of type OracleDbType.Varchar2 with CollectionType of OracleCollectionType.PLSQLAssociativeArray and the values are formatted as per the NLS_TIMESTAMP_FORMAT.

Advertisements

2 responses to “Oracle: PLSQLAssociativeArray TimeStamp workaround

  1. DD/MM/YYYY HH24:MI:SS.FFFFFF
    – Number of Fs depends on precision of TIMESTAMP for example a TIMESTAMP(6) would have 6 Fs.

  2. Pingback: Oracle: Using PLSQLAssociativeArray with ODP.NET | Tim's cycling blog

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s