topic Re: INSERT with CONNECTION to an external Database. in Application Development and Automation Discussions

INSERT with CONNECTION to an external Database.

Former Member — Mon, 06 Apr 2015 21:58:07 GMT

Hi All,

I have to INSERT an internal table values to an external Oracle Database. The Solution I saw in Forums are with CONNECTION Addition in INSERT option with DBCON.

insert TABLE_NAME@SCHEMA CONNECTION 'PAYPROD'

from table gt_summary.

I am getting syntax error that "TABLE_NAME@SCHEMA", is unknown. It does not exist.

Can anyone help me an example with CONNECTION option in INSERT.

Regards,

Asha

Re: INSERT with CONNECTION to an external Database.

Former Member — Tue, 07 Apr 2015 02:41:20 GMT

Hi Asha,

Schema should identify in the tcode DBACOCKPIT, at here seems you need,

insert TABLE_NAME CONNECTION ('PAYPROD')

from table gt_summary.

Please go through this from Thomas, http://scn.sap.com/community/hana-in-memory/blog/2012/04/11/test

regards,

Archer

Re: INSERT with CONNECTION to an external Database.

raphael_almeida — Tue, 07 Apr 2015 02:56:17 GMT

I recommend you use the classes below to resolve your problem.

cl_sql_connection;
cl_sql_statement.

First of all, connect to the database using the cl_sql_connection class, in the same class can make connection ping, among other features. You must have configured on the SAP server TNSNAMES with data for connection to this docking station. Second thing to do is to configure SAP to read this connection, this can be done by DBCO transaction. To carry out connection tests, utilixar the DB6TRC transaction.

Connecting successfully to external database, you use the cl_sql_statement class, within this please enter your insert command.

After completing the insert, by the cl_sql_statement class you can call the commit work.

For learning purposes, I advise you to see the ADBC_DEMO program, everything said here and more is explained there.

Regards

Re: INSERT with CONNECTION to an external Database.

Former Member — Tue, 07 Apr 2015 16:49:32 GMT

Hi Archer,

The response was helpful.. I change the syntax as mentioned above.. I am still getting the Syntax error, telling "Table not defined in Dictionary or DATA Statement.". Below is the insert statement used. I am able to insert values as single records. But I have nearly 100,00 records to insert which cases performance...

INSERT opfa_walker_summary_temp_sap@opdev connection 'PAYPROD'

from table gt_summary.

opdev - Schema name..

PAYPROD - Server Connection mentioned in DBACOCKPIT

opfa_walker_summary_temp_sap - Table to get inserted.

Regards,

Asha

Re: INSERT with CONNECTION to an external Database.

Former Member — Tue, 07 Apr 2015 16:55:30 GMT

Hi Rapheal,

thank you for the response.. I went through the programs.. It is talking about INSERT into table VALUES (''').. I am looking for Insert multiple rows at the same time.. Something like

Insert into table values (select field1, field 2....). But the SELECT table belongs to SAP.

Regards,

Asha

Re: INSERT with CONNECTION to an external Database.

raphael_almeida — Wed, 08 Apr 2015 02:41:11 GMT

Hi Asha,

Oks, I understand, so I pointed him to read the ADBC_DEMO program in it insertion examples of multiple lines.

Regards,

Re: INSERT with CONNECTION to an external Database.

Former Member — Wed, 08 Apr 2015 06:13:03 GMT

This message was moderated.

Re: INSERT with CONNECTION to an external Database.

Former Member — Wed, 08 Apr 2015 22:50:09 GMT

Hi Archer,

The INSERT CONNECTION was working as expected for the Oracle Version 11i. But I need to insert into an Oracle Database with version 8... I am looking for a work around..

Thank you,

Asha

Re: INSERT with CONNECTION to an external Database.

VenkatRamesh_V — Thu, 09 Apr 2015 09:05:59 GMT

Hi,

Try,

Checking your connectivity in program ADBC_TEST_CONNECTION

Hope it helpful,

Regards,

Venkat.V

Re: INSERT with CONNECTION to an external Database.

Former Member — Fri, 10 Apr 2015 22:16:36 GMT

Thank you, every one... My DBA created a Connection link to that particular table in Oracle 8.0. Once that is done, the INSERT is working.

Regards,

Asha