Stored Procedure stage cuts the time part from Date paramete

[evee1]

I have a stored procedure (Oracle) that has a DATE input parameter. The parameter represents the last process time and it must contain both date and time.
I'm using STP stage to call this stored procedure and have a problem with passing full date and time portions.
When I pass a Date (26,6) field to it, Datastage seems to be truncating the time part and SP executes as if only the date was passed as a parameter (which is wrong).
If I pass Timestamp field then I get the error:

Code: Select all

ORA-01861: literal does not match format string ORA-06512: at line 1

As a workaround, I could create another Oracle SP that will take a VARCHAR2 parameter (and possible a date/time format), that would convert the string to date and then call my existing SP. Then I could just call it with a VarChar parameter and problem solved.
However, I would prefer though to this issue inside Datastage rather than creating any stubs in Oracle. Is there any way to achieve this?

[ray.wurlod]

Try calling it a Timestamp(6) within DataStage.

Unlike every other database known to modern man, a DATE in Oracle is actually a timestamp - that is, it has a time component.

Oracle is..... different.

You might also experiment with Data Elements, though these in my experience are most useful with ODBC connections.

[SURA]

I guess it is because of Date data type. Can't you use TIMESTAMP in Oracle?

DS User

[evee1]

I guess it is because of Date data type. Can't you use TIMESTAMP in Oracle?

I know it's because of Date. No, I can't use Timestamp in Oracle. It is an existing SP and it's used by others in its current form.

Try calling it a Timestamp(6) within DataStage.

Tried it. Still getting the same error.
I will experiment with the Date Elements.

[evee1]

I can't see a way to apply Data Elements anywhere in the STP stage .

[evee1]

I ended up with writing wrapper stored procedures for all the existing SPS that take or return DATE parameter. They take two VARCHAR2 parameters, date string and date format, and convert they into DATE and call the existing SPs with this date. It works fine now.