topic Re: external oracle database connection in Application Development and Automation Discussions

external oracle database connection

Former Member — Tue, 04 May 2004 12:30:25 GMT

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

Re: external oracle database connection

Former Member — Wed, 05 May 2004 05:05:20 GMT

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

Re: external oracle database connection

Former Member — Wed, 05 May 2004 13:28:25 GMT

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

Re: external oracle database connection

Former Member — Wed, 05 May 2004 20:14:28 GMT

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

Re: external oracle database connection

Former Member — Thu, 06 May 2004 10:24:18 GMT

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

Re: external oracle database connection

Former Member — Thu, 06 May 2004 10:39:09 GMT

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.

Michael

Re: external oracle database connection

Former Member — Thu, 06 May 2004 14:01:31 GMT

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

Andrea

Re: external oracle database connection

Former Member — Thu, 06 May 2004 16:42:28 GMT

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

Re: external oracle database connection

Former Member — Thu, 06 May 2004 16:59:19 GMT

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

Andrea

Re: external oracle database connection

Former Member — Thu, 06 May 2004 17:45:46 GMT

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.

Re: external oracle database connection

Former Member — Thu, 06 May 2004 17:54:40 GMT

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

Re: external oracle database connection

Former Member — Thu, 06 May 2004 18:03:02 GMT

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

Pavan

Re: external oracle database connection

Former Member — Thu, 06 May 2004 19:31:50 GMT

i'm using SAP WAS 6.10

Andrea

Re: external oracle database connection

Former Member — Fri, 07 May 2004 15:32:07 GMT

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

Re: external oracle database connection

Former Member — Fri, 07 May 2004 18:13:07 GMT

Hi Andrea,

'Connection' option is available only sincle WAS 6.20 !

Regards,

Venky Varadadesigan

SAP NetWeaver consulting.

Re: external oracle database connection

Former Member — Fri, 07 May 2004 19:44:01 GMT

ok.

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

ANdrea

Re: external oracle database connection

Former Member — Sat, 08 May 2004 14:00:45 GMT

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

Re: external oracle database connection

Former Member — Wed, 17 Aug 2005 10:55:09 GMT

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