Outbound integration to Oracle

[Former Member]

What are my options for copying a subset of the data that is contained within HANA to Oracle.

I considered SDI and SDA, but found that SDI can not write to SDA's virtual tables in SPS9 and in SPS10, there are limitations in the SDA layer that cause difficulty "We inherit the limitations from the SDA layer because it supports only update/delete statements with constants. But in the task we need more complex SQLs, e.g. update/delete based on the data of another table and things like that." according to another post in this forum.

SDI appears to be able to call a SQL script procedure - but is there any way to write to Oracle from SQL script? We are currently on SPS 9, so SDA does not appear to help, because as I understand it, SPS9 doesn't support inserts into virtual tables.

Can SDI call a JAVA procedure? If so, maybe I can use JAVA JDBC to connect to Oracle and perform the operations needed.

What other options do I have.  I see MANY articles about how to get data into HANA from Oracle, but not many about how to copy data from HANA to Oracle.

Thank you!

[henrique_pinto]

I don't suppose you'll have many options besides external ETL tools like Data Services or 3rd party ones (Informatica, Data Stage etc.). HANA's SDI relies either on SDA on the source tables or on log replication from the source DBs, since it's do used on getting data into HANA.

Do notice however that your main issue won't be the technical solution but license. Unless the data you're trying to access is non-SAP data from the start, the only HANA license that includes an unrestricted data usage rights is HANA Enterprise. All other license types will have restrictions on how/to whom you can share the data from that HANA box. For example, suppose you have a Hana runtime license for the business suite. If you run ERP on HANA and export ERP data from HANA to Oracle, only SAP named users should allowed to consume that data, even on top of Oracle. It's not a matter of technology but of data origination and data usage rights. And it's not specific to Hana, it'd be the same even if you had ERP on Oracle or AnyDB runtime license.

[Former Member]

But I wonder if there is a way to work around SDA's limitations to get it to work.

[lbreddemann]

Hi Robert,

as with virtually all integration technologies around DBMS the idea for SDA, SDI etc. is to get data into SAP HANA.

For inter-DBMS-data-transfer you typically employ an ETL tool, such as SAP DataServices.

But what's true for SAP HANA works also for Oracle in this case.

Oracle provides a feature called DB LINK that allows you to integrate also "foreign" DBMS.

Back in the days I wrote up a little how to on that for Oracle reading from a MaxDB instance:

Would be surprised to learn that this would't work with SAP HANA.

- Lars