Moving Internal Table data from SAP to external Oracle Database

[Former Member]

Hi Experts,

I have a requirement of inserting a large amount of data from SAP to an external Oracle Database table. The data is stored in an internal table in an ABAP program I have written and I would like to execute a similar function to the ABAP statement INSERT <dbtab> FROM TABLE <internal table> to do a bulk insert into the Oracle database instead of millions of single insert statements.

I have attempted using the CL_SQL* classes as well as EXEC SQL commands to pass the internal table to a stored procedure that I have written in the external database that would receive the internal database as an array and then execute the PL/SQL statement FORALL.... However, it seems that the data type of an internal table is not supported as a parameter that can be passed from SAP to an external database. ST11 says "unknown ABAP type : 5"

Do you know of any method I could use to pass an internal table to the external Oracle database. Or do you know how I can determine how exactly SAP executes INSERT <dbtab> FROM TABLE <internal table> in an Oracle database so that I could mimick the logic in our external database.

[lbreddemann · Accepted solution]

> I have attempted using the CL_SQL* classes as well as EXEC SQL commands to pass the internal table to a stored procedure that I have written in the external database that would receive the internal database as an array and then execute the PL/SQL statement FORALL.... However, it seems that the data type of an internal table is not supported as a parameter that can be passed from SAP to an external database. ST11 says "unknown ABAP type : 5"

>

> Do you know of any method I could use to pass an internal table to the external Oracle database. Or do you know how I can determine how exactly SAP executes INSERT <dbtab> FROM TABLE <internal table> in an Oracle database so that I could mimick the logic in our external database.

Well, the easiest way would be to make use of the db multiconnect (see [documentation|http://help.sap.com/abapdocu_70/en/ABENOPENSQL_MULTICONNECT.htm] and/or notes #339092 - DB MultiConnect with Oracle as secondary database, #323151 u2013 Multiple DB Connections with Native SQL.

Just switch the connection for the insert statement like this:

INSERT <dbtab> FROM TABLE <i_tab> CONNECTION (<name>)

Apart from that you need to make sure that the data types of the columns from your internal tables matches those in the target database.

regads,

Lars

[Former Member]

Thanks for both replies, very much appreciated. Both worked exactly as described.

I decided to go with the INSERT CONNECTION option, as it turned out to be a simpler solution for my requirements.

Much appreciated.

[volker_borowski2]

Hi,

I would insert to a local table first, to fully complete your application logic.

Set up the target as a DB_LINK then and a call of

FM db_execute_sql with

"insert into dblink_to_remote_tab select * from local_table_filled_before"

should do the job.

Volker