How to write a Timestamp in an Oracle table?

[former_member219991]

Hi,

I am pretty new to SAP IdM and therefore have a very basic question.

I am using SAP IdM 7.2 SP8 on Oracle.

I was creating a job where I calculated some values from the database with a 'From Database' path.

Then I wanted to store these values in a temporary table together with the timestamp when these were stored/calculated.

Doing this in the Destination-Tab of the 'From Database'-path I got following error message...

java.sql.SQLException: Missing IN or OUT parameter at index::2

I did several tests and created a little test which recreates the problem...

1. Create a Job with a 'From Database'-path

2. In the source statement, insert...

select

  'Test' AS Message,

  systimestamp AS TempDate

from dual;

3. In the destination tab add a temp Table and two columns. Since I want to add a date/timestamp-value I create a column of type 'DATE' here.

4. After running the Job the error appears. (Here in german)

Doing some additional tests I am able to add the date as a varchar2 if I convert the TempDate using a to_char()-function.

Thus it seems like I misusing the Timestamp/Date data type.

Do you have recommendations how I can add the Timestamp as a Date/Timestamp value to my table?

Kind Regards, Andreas

[Former Member · Accepted solution]

Hi,

I really never got this to work properly and most of what follows is just my own rant caused by the annoyance of not getting this seemingly simple thing to work and are strictly my own theories not based on facts or actual knowledge of how Oracle or JDBC works, nor representing my employer in any way. Anyhow 🙂

Its possible that cause of the problem is that the timestamp is "destroyed" by NLS/JDBC when transferred from the database to the client. This is also one of the issues that varies a bit from version to version of the jdbc driver. Also the DATE datatype does not include fractional seconds, so you need to create the column as TIMESTAMP if you want that.

The real fun thing with Oracle (or their jdbc driver) is that you can read a timestamp in the source and jdbc/NLS will convert it according to your locality setting, but you can't write what you read from Oracle back to Oracle.

Just the same way you can use to_char to get a timestamp in a nice enough format, but when writing it to_date may not be able to parse the date back when using the exact same conversion mask...

Example:

     select TO_CHAR(current_timestamp,'YYYYMMDD HH24:MI:SS.FFTZHTZM') from dual;

Gives: 20140314 12:41:03.320000+0100. But both

     select TO_DATE('20140314 12:41:03.320000+0100','YYYYMMDD HH24:MI:SS.FFTZHTZM') from dual;

and

     select TO_TIMESTAMP('20140314 12:41:03.320000+0100','YYYYMMDD HH24:MI:SS.FFTZHTZM') from dual;

give errors.

Rather than argue and fight with it I usually just end up using a To Database pass, check SQL Updating and write the Create Table and update/insert statements myself. Alternativly you can create the table with a trigger that automatically adds the timestamp on inserts/updates.

1st To Database pass has no source, creates the temp table:

The next pass has the source to read the data and insert it into the temp table:

Then just use the current_timestamp function in the insert statement to keep it local and unconverted in the database engine...

Br

Chris

Message was edited by: Per Krabsetsve

[former_member219991 · Accepted solution]

Hi Chris,

thank you for the explanation. This is the workaround I ended up with but I thougt, that I did something wrong or have missed something because of being new.

So it's just the way it is.

Thanks for your broad explanation,

Andreas

[Former Member]

Hi Andreas,

First thing I see in your source tab SQL statement is that it ends with ";", you should remove them, because for IdM this is not correct statement.

Kind Regards,

Simona

[Former Member]

Hi Andreas,

Did you try to take the system time stamp like this.

This is how i do if I want to take the system time stamp.

My backend is SQL. I hope it is same for oracle too !!

All the best !!

~ Krishna.

[terovirta]

Try removing the semicolon at the end of your query..

[former_member219991]

Hi Tero,

thanks for your reply.

I did remove the the semicolon at the end of my query.

With the semicolon I got an ORA-00911 invalid character error.

I did forgot to create a new screenshot of the source statement afterwards.

Thus the actual error screenshot (the fourth one) was created after I removed it.

Andreas

[terovirta]

I don't have an Oracle-instance to verify this but didn't the sysdate contain also time? Is the error message about trying to insert datetime to date?

Based on my notes I have always stored the dates in Oracle as varchar and casted them to dates with TO_DATE and to string with TO_CHAR. The product itself stores the dates as varchar with datetime-attributes.

regards, Tero

[former_member219991]

Hi Tero,

you are right, the value contains date and time values (you can see the value of TEMPDATE in the fourth screenshot).

This is the value you get from Oracle when calling sysdate/systimestamp.

Since there was no TIMESTAMP datatype in the Destination-Tab to choose, I expected to save the value of the Oracle functions in Date.

So what you are saying is that my assumption is incorrect and that I can save date/timestamp values in the SAP IdM 'DATE'

Thus the DATE datatype in the Destionation-Tab is not a SAP IdM internal datatype which is translated to the database architecture underneath.