on 06-18-2010 11:26 AM
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.
> 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
72 | |
11 | |
10 | |
7 | |
6 | |
6 | |
6 | |
6 | |
5 | |
5 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.