Using Open SQL (ABAP-SQL) with a DB-Link

[Former Member]

Hi,

Has anybody made experience using Open SQL (ABAP) for writing data to a remote database with a DB-Link as described in SAP-note 25383 (Creating remote table in ABAP DD, then dropping table on SAP-DB and creating a synonym to DB-Link) ?

I'm especially interested in using the 'INSERT dbtab FROM TABLE itab' construct on the remote DB. Anybody knows if this will work?

Thanks,

Ulrich Meier

[Former Member]

Hi Ulrich,

1. Open SQL

will not work with the concept of DB Link.

regards,

amit m.

[Former Member]

So SAP-Note 25383 is not correct?

[Former Member]

Hi again,

1. sorry for the previous haste reply.

2. i read the note number 25383

3. what i could find out that :

we can do it using these steps.

a) make a DB Link

b) Make a synonym attached to the remote table thru DB link.

[ we have had used DATABASE LINK in oracle

in our legacy systems and it works fantastic]

4. But Who will do all these steps a, b?

thru abap dictionary it is not directly possible.

5. One has to open the Oracle database

directly

(thru SQL navigator, or SQL Plus)

and execute the steps A, B.

6. But it is not recommended.

(Directly touching the database is not recommended

by SAP.)

7. Finally one can achieve what u required,

but there are some constraints.

regards,

amit m.

Message was edited by: Amit Mittal

[Former Member]

Hello Amit,

Here's the quote from SAP-note 25383:

<i>"...

A Database link can be created on the R/3 database by using the following Oracle command:

CREATE PUBLIC DATABASE LINK <db_link>

CONNECT TO <user> IDENTIFIED BY <password>

USING '<connect_string>';

For the <connect_string> refer to the Oracle documentation.

There are two possible alternatives for accessing a remote table from the R/3 System:

1. With ABAP Open SQL

For this, the description of the remote table and its fields in the R/3 data dictionary is required. In the releases smaller than 3.x, you can create and activate a table description in the data dictionary without the table also having to be created on the R/3 database at the same time. However, as of Release 3.x, the table is implicitly created on the R/3 database during activation. In this case, the created table must be explicitly deleted again by using the database utility.

Instead of the table, you now create a synonym under the table name used in the R/3 data dictionary for the remote table, thus:

CREATE SYNONYM <table_name>

FOR [<user>.]<table_name>@<db_link>;

This synonym enables you to make full use of the ABAP Open SQL language range when accessing the remote table.

..."</i>

We already have a DB-Link in use but that uses only 'Native SQL' (also described in note 25383 (2nd possibility)). But due to performance issues I want to try the 'INSERT dbtab FROM TABLE itab' construct which is only available in 'Open SQL'.

Regards,

Ulrich

[Former Member]

Hi again,

1. i hope u have read my second reply.

2. I cannot say that the note no. is wrong.

It may be possible.

3. But it is not at all advisable practically.

4. the following statments in the note

are mysterious to me :

Instead of the table, you now create a synonym under the table name

used in the R/3 data dictionary for the remote table, thus:

CREATE SYNONYM <table_name>

FOR [<user>.]<table_name>@<db_link>;

This synonym enables you to make full use of the ABAP Open SQL

language range when accessing the remote table.

4. It tries to say that creating a synboym in database

directly , can be used in open sql.

But it does not have any reference in ABAP dictionary

, then how can we use in open sql.

( i have worked with synonym using it as a table name in legacy system,

but in practical, in r/3 database,

a synonum does not have any link

with abap data dictionary.

so at run time, if we write

select * from TABLEXYx

(where tablexyx is the synonym)

then how will ABAP know, it is a table.

it willl give syntax error saying

that TABLEXYZ does not exist in Database dictionary)

5. Native sql is another solution.

6. Another solution is DBCON.

(Secondar databse connection)

for ur requirement.

regards,

amit m.

Message was edited by: Amit Mittal

[Former Member]

Hi again,

1. i understood the sap note.

(if we do those 3-4 steps,

we can 100% access the remote table

via open SQL, just as U want to access

using the INSERT statement)

2. u said that u already have the db link

created in sap r/3 database !!!

(Good ! who created the link ?

if u created then ok,

if basis team created, then u may

require their help again )

3. If yes, then first of all u need to

create the same table (remote table which u want to

use in ur sql)

create the table with the same name, same fields,

same field length in ur abap dictionary !.

4. If that is created, let me know.

regards,

amit m.

Message was edited by: Amit Mittal

Message was edited by: Amit Mittal

[Former Member]

Hello again,

I did all steps mentioned before but got an ORA-00947 error when performing the INSERT-statement. However further studies make me think that even if I would get this to work in Open SQL it will not solve the performance issues I'm experiencing, now. There seems to be a problem with the remote DB or DB-Link itself.

So I will switch back to Native SQL.

Nevertheless I thank you, Amit, for your great support!

Regards,

Ulrich