external oracle database connection

[Former Member]

have you never used DBCON table to connect to an external oracle database?

in the con_env field of DBCON table I put the following connection string for oracle database:

dbname=GBNET user=tzta password=tzta host=192.168.0.10 port=1521

is it correct?

i have to configure sap system and oracle to work togheter?

now it seems to be that the sap system is knowing only sap db(ADABAS D)!

can anyone help me?

thank's.

Andrea

[Former Member]

Hi Andrea,

Please do a F1 on CONNECTION in ABAP Editor. I believe the documentation given there will help you solve the problem.

As I told earlier you donot use Native SQL statements to connect to external database.

You will say CONNECT and give the DBNAME as made in DBCON table, then you should have a table of similar structure and name in Datadictionary as far as I can understand that, and then you use your normal Open SQL.

The connection statement is only indicating to the workprocess which connection is to be used.

Regards,

Pavan

[Former Member]

Pavan,

this is my small ABAP Program:

REPORT ZDBASE .

DATA: tbr(50) type c.

EXEC SQL.

CONNECT TO 'GBNET'

ENDEXEC.

IF SY-SUBRC NE 0.

message s000(zf) with 'Imposible conectar a Gbnet'.

ENDIF.

EXEC SQL.

SET CONNECTION 'GBNET'

ENDEXEC.

EXEC SQL PERFORMING LOOP_OUTPUT.

SELECT T_DES

INTO :tbr

FROM twzt02_rol

ENDEXEC.

FORM LOOP_OUTPUT.

write: / tbr.

ENDFORM.

where GBNET is the connection name in DBCON table.

there is no help on CONNECTION or CONNECT in ABAP Editor!

how can i do a select statement on remote oracle database table with Open SQL?

Open SQL statements use current database connection "DEFAULT" that is R/3 DB (ADABAS D), while I want to read data from a remote oracle database table.

Andrea

[Former Member]

Hi Andrea,

Try the following code in your program.

The prerequisite for the same is as follows.

1. Create the tables in Datadictionary, with the same structure as you want to access in the external database.

CONNECTION ('GBNET').

after this statement, you can fire your normal select queries to access data from the external database.

Hope its clear, I dont think you will be using DBCON table in case of native SQL connect statement.

External DB Connections can be done in two ways

Open SQL

Native SQL

In case of Open SQL.

1. The DBCON table should be configured.

2. Declare tables of same type in Data dictionary as you want to access in the external system.

3. In the program use CONNECTION('conname') conname is the dbconnection name as given in DBCON table.

4. Use the normal Open SQL statements to access data.

In case of Native SQL.

1. Fire the CONNECT command giving all the parameters, it doesnt make use of the DBCON table.

Hope this is clear.

Regards,

Pavan

[Former Member]

Pavan,

executing this code in my ABAP program:

REPORT ZDBASE .

DATA: tbr(50) type c.

TABLES: ZTWZT02_ROL.

CONNECTION('GBNET').

SELECT T_DES INTO :tbr FROM ztwzt02_rol.

write: / tbr.

ENDSELECT.

I create the table ZTWZT02_ROL in ABAP Dictionary with the same structure of the external oracle table 'TWZT02_ROL' (the only difference is the table name because in abap must initial with character "Z").

I get this syntax error message:

The syntax for a method specification is "objref->method" or "class=>method".

After "GBNET", there must be a space or equivalent charecter (":",",",".").

Why?

seem to be wrong the statement CONNECTION!

Andrea

[Former Member]

Hi Andrea,

Try to put spaces after the first braket and before the 2. braket.

In Abap (except arthmetic expressions) you have to insert spaces between the brakets, becouse a braket is a reserved word.

Hope i could help you.

BR

Michael

[Former Member]

i try to put spaces like CONNECTION( 'GBNET' ) but i still have the same syntax error.

Andrea

[Former Member]

Please use CONNECTION ('GBNET') and let me know of the results. But i think you may not be able to access the table with that name, but try and see, I guess it should have the same name.

Regards,

Pavan

[Former Member]

I try CONNECTION ('GBNET') but i get this syntax error "Statement CONNECTION is not defined. Check your spelling."

Andrea

[Former Member]

Sorry about the confusion.

Plz use the following syntax.

SELECT * FROM <TABLE created> CONNECTION ('con name').

This syntax can be used with any OPEN SQL statement.

Pavan

Even I am learning external database connection now only, but as I dont have access to external database. Plz excuse any mistakes.

[Former Member]

I try this code

SELECT T_DES INTO :tbr FROM ztwzt02_rol CONNECTION ('GBNET').

write: / tbr.

ENDSELECT.

but i get this syntax error "Wrong expression CONNECTION in FROM clause. WHERE condition.

Andrea

[Former Member]

Which version of SAP are you using. I dont get any problem in execution of the code.

Pavan

[Former Member]

i'm using SAP WAS 6.10

Andrea

[Former Member]

I think the CONNECTION statement does not exist in ABAP.

The open sql select statement does not have the keyword CONNECTION.

Are you sure that I don't have to use native sql "CONNECT TO" statement to connect to an external database?

Did you never need to connect to an external database from an ABAP program?

Andrea

[Former Member]

hello,

when using syntax

SELECT * FROM <r3_table> CONNECTION ('con_name').

does <r3_table> created in sap system need to have the same name as external db table? if so, how is it possible to avoid conflict with sap db table names since customer namespace for objects starts with 'Z'?

--

best regards,

robert

[Former Member]

Hi Andrea,

'Connection' option is available only sincle WAS 6.20 !

Regards,

Venky Varadadesigan

SAP NetWeaver consulting.

[Former Member]

ok.

then how can i do an external database connection with WAS 6.10?

ANdrea

[Former Member]

I think you have to use the Native SQL, but then I dont think you will have to specify all the connection parameters subsequently in the SQL query itself, you cant make use of the values presnet in DBCON table. I think that is only for using external database through OPEN SQL not NATIVE SQL.

You might have to explore the external db connections from ADABAS.

Regards,

Pavan