Converting SAP date type to Oracle date type.

pgwjb,
I think that method you describe looks fine. Your problem is in oracle side where you defined column as varchar2 cause varchar2 means filed size is flexible for changes and your date format is fixed (so you write and even give
example yyyymmdd format I instead of you define column as date where the same format supply any date format when you use full date by default there is no problem with views.
Wish you all luck and success you need to olve it.
Yours
oavidov

I need more information to answer your question...

How did you setup the DBLink to map SAP table? Is it direct link to a table or you have to use a view at the DB level to make this work?

In short... I have a way for you to do it if you are using a view... Also you would need admin access to the underlying databases or have someone do the steps for you...

pending for your answer...

pqwib,

I have read through your other post... congrat that you have gotten this far... Based on what you have stated (and my assumption of what you have done so far)...

You are using a view on the primary SAPDB to reference the a table from the second DB via DBlink, using the same name for the view as the name that you dropped that was generated by the SAP system... and because the table and  view have the same structure (assuming same field/column names) SAP was able to reference it...

The only thing that you are having problem with now is the date format is not matching up...

Because SAP is using VARCHAR2(8) with YYYYMMDD format... you can easily manipulate it via a conversion function...

Which in your select statement for the view... the field that contain the date... use the conversion function to convert it into YYYYMMDD format... example...

instead of using

select * from table@remoteDB

you need to manually specified each of the field, then for the date field... use the TO_CHAR function to do the conversion... Such as

SELECT field1, field2, to_char(date1, 'YYYYMMDD') date1, field3, .... from table@remoteDB

to_char(date1, 'YYYYMMDD') would give you the exact date format that varchar2(8) can accept...

If you still need help... just post the sql statement for the view... I will rewrite for you... :) ...

Hopes I have shed some light on this issue for you...

Knowledge,

Actually I have tried you suggestion above but the major
problem is that it turns the view into a read-only view.  You can't update the table from SAP with the view containing the TO_CHAR function.  What I have done and it is not optimal is to create two date fields on the Oracle table.  One is Varchar2(8) for SAP's use and one is an Oracle Date type.  I then create a trigger on the SAP date field to populate the Oracle date field.  I then can from an app connecting to the Oracle table update both fields.  Any OLAP activity from Oracle can then use the column with the DATE type.

Any thoughts on this puzzle?  Any recommendations welcomed.

Yes..The view would cause it to be read only... however, to resolve this, you can put 3 triggers against the view...

Trigger 1 for Inserting
Trigger 2 for Updating
Trigger 3 for Deleting

Each one of the trigger would be responsible for calling DDL commands (such as insert, update, delete) against the actual table on the remote DB

Knowledge,

The problem with that view is that the triggers need to be built on the SAP database.  My desire is too keep the work on that platform as light as possible.  The design I was hoping for was to have the Oracle date field mapped in SAP is such a way that it would be treated as a DAT field.  Either by using an element that corresponded to the Oracle DATE type or by using internal structures writing to an Char(8) field.

While it sounds complicated, the intent is to make it streamlined.

Thanks for the dialogue.