Skip to Content
author's profile photo Former Member
Former Member

Datatime format for Oracle Stored procedure in Receiver Adapter

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

5 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Oct 12, 2007 at 05:58 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 12, 2007 at 06:02 AM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Oct 17, 2007 at 04:02 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

  • author's profile photo Former Member
    Former Member
    Posted on Oct 18, 2007 at 04:04 PM

    Hi Monika,

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

    Regards,

    Younus

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Oct 19, 2007 at 04:37 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.