Datatime format for Oracle Stored procedure in Receiver Adapter

[Former Member]

Hi,

I have to insert Datetime in the database using Stored Procedure.

Do we have to do any transformation for this, does xi support datatime format which is in accordance with Oracle Datatime format. If yes, please give the format.

Do we have to do any settings in the configuration of Receiver JDBC adapter to achieve this.

We are working SAP PI 7.0

Please provide me with your inputs.

Thanks,

Younus

[Former Member]

Hi Monika,

During mapping time is passed, only after insertion it shows 00:00:00.

I have checked in adapter monitoring too, the values which is sent is 03-SEP-2007 12:13:14 , but when we check in DB is 03-SEP-2000 00:00:00.

I hope now your clear with my problem.

Waiting for a solution reply.

Thanks in advance,

Younus

[Former Member]

Hi,

Did you try to run your SP in Orcle itself manually with following data time values? Is it inserting date-time sucessfully. Run SP manually with given parameters and check..Probably there could be problem with SP only.

Nilesh

[Former Member]

Hi

Check your stored Proc. and also see whether the field of the DB is actually allowing insertion of these kind of values by trying manually.

Thanks

[Former Member]

Hi Monika,

Yes, you have got it right, this is after the explicit mapping. Any pointers on how to solve the issue.

Regards,

Younus

[Former Member]

Hi all,

Actually I'have to insert date and time for a date field in Oracle database.

Value which is going to the receiver jdbc adapter is of this format dd-MMM- yyyy HH:mm:ss

The insertion is happening but the time part in the database is 03-Mar-2007 00:00:00, where as i want it to be like 03-Mar-2007 11:11:11

Do we have to do any config settings in the receiver jdbc adapter or need to use any other udf's or functions.

Please help me out.

thanks in advance,

Younus

[Former Member]

Hi

You mean to say the value is going to the JDBC Receiver in the same format it accepts but the time fields are not getting inserted.

Just try doing a explicit graphical mapping for the same, in order to check.

Correct me if i am wrong.

Thanks

[Former Member]

Hi

No particular settings are required.Only check your mappings.If 00:00:00 is coming that means the receiver format is same as that of the sender.But somewhere in the transformation, things are going wrong.

I hope you must have followed the same steps

1. Date Trans Properties -

---

> Format of Source Date, now click on question mark with hand symbol (F4 help) you will get new window.

2. In new window under Date&Time Format: write your date format and just besides that in the other box write HHmmss.

3. Now choose your Date Separator from the drop down box.

4. Under Time Separator select from drop down box “:” symbol

5. Under Date&Time Separator keep a space.

Now press Create Pattern button.

Mapping:

Target field -

---

> DateTrans -

---

> source field.

Also whether during the mapping itself the time field doesnt show or while getting inserted into the DB.

Thanks

[Former Member]

Younus,

All you have to do is check what is the date time format expected by your Oracle db and in XI you can format any format of date to the format required by Oracle in Mapping using Date transformation function. In the data type you can select string as xsd:format.

@ Prathiba,

You can use the std function Date transformation to achieve this, you dont need a UDF.

Regards,

Jai Shankar

Message was edited by:

Jai Shankar

[Former Member]

Yes, I know that we can do this using the Date transform, but i don't the target Datetime format.

It is the standard format.. Can any one give the exact format for the Datetime in Oracle DB.

Thanks in Advance,

Younus

[Former Member]

Hi,

UDF for conversion of sap format to oracle format is

String b="";

try

{

java.text.SimpleDateFormat sdf= new java.text.SimpleDateFormat("yyyy-MM-dd");

java.util.Date date = sdf.parse(a, new java.text.ParsePosition(0));

java.text.SimpleDateFormat sdf1= new java.text.SimpleDateFormat("dd-MMM-yy");

StringBuffer sb = new StringBuffer();

sdf1.format(date, sb, new java.text.FieldPosition(0));

b=sb.toString();

}

catch(Exception e)

{

e.printStackTrace();

}

return b;

Recently i did the scenario in which i converted the date..pls see if it helps

regards,

Pratibha