External Oracle Databse connection from SAP

[Former Member]

Dear all,

I know that this question has been asked before, but as i am facing some primitive issues so i am again posting my query.

My requirement is to connect to an external ORACLE db from SAP and read data from that server. That ORACLE db is located in a different m/c altogether. I was trying to connect by making settings in DBCON -

CON NAME= ORA_TEST

DBMS= ORA

USER NAME= administrator

PASSWORD= (some pwd)

CON ENV= ????? (what is to be put here)

And I know that we have to make some corrections in tnsnames.ora file, but I am unable to find that file in SAP system. Where can I find this file ???

Do we have to make one RFC connection as the SAP and ORACLE servers are in diff m/c and then do the connection, but this is just my guess??

And if any RFC is at all required then what should be the parameters for that connection.

Kindly help me in resolving the issue.

1. CON ENV ?

2. tnsnames.ora ?

3. Any RFC needed ?

Points will definately be rewarded.

Thanks,

Saurabh.

[Former Member]

done

[Former Member]

I missed to inform that we are having MS SQL as our default SAP database and we are trying to connect to external ORACLE db

Kindly assist.

[markus_doehr2]

don´t duplicate posts:

Markus

[Former Member]

Hi there

1. con env is the identifier of the name you put in the tnsnames.ora, for example mydb.world

2. the tnsnames.ora is either in $ORACLE_HOME/network/admin or in $DIR_PROFILE/oracle

3. no way, no RFC involved

Besides that, it is not necessary to directly edit the table DBCON, you should use transaction DBCO. And the schema_user's name in the remote database is really administrator ?

Best regards

Michael

[Former Member]

No, the user-id name is not really "administrator", just for example i kept it. It can be scott-tiger combination or any other user defined credentials.

Thanks Michael for the quick response.

On more query, Do we have to make any change in listener.ora file ?? because at some places on the net while searching for the solution I came across it.

And does anybody know about making the connectivity using SQL.NET ??

Regards,

Saurabh.

Edited by: Saurabh Chauhan on Aug 6, 2008 9:22 AM

[Former Member]

On more query, Do we have to make any change in listener.ora file ?? because at some places on the net while searching for the solution I came across it.

No, you don't have to. Your local listener (the one for the SAP database) is not involved for the db connect.

And does anybody know about making the connectivity using SQL.NET ??

Not completely sure what you mean here, but the db connect with DBCO is in fact a [SQL Net|http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/dist_pro.htm#i7918] connection.

Best regards, Michael

[Former Member]

Thanks Michael,

Will do the respective settings and will try to connect.

Regards,

Saurabh.

[Former Member]

Hi,

I have done the required settings everywhere but the connection is failing.

The tnsname.ora file is located in my Oracle client directory and the settings done there are -

LEVEL2 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST =

192.167.40.54)(PORT = 1521))

)

(CONNECT_DATA =

(SERVICE_NAME = level2)

)

)

in DBCON i have made the following entry -

1. DB Connection : NEWORA

2. DBMS : ORA

3. User Name : User Name external oracle database

4. DB Password : Password external oracle database

5. Conn Info : level2

and my ABAP code looks like -

DATA: BEGIN OF wa,
ncli(20) TYPE c,
num(20) TYPE c,
END OF wa.

DATA: dbs TYPE dbcon-con_name.
DATA: con(20) TYPE c.
DATA : ls_wa LIKE wa.

dbs = 'NEWORA'. "DB Connection in DBCO

EXEC SQL.
connect to :dbs
ENDEXEC.

EXEC SQL.
GET CONNECTION :dbs
ENDEXEC.

WRITE : dbs.

EXEC SQL PERFORMING loop_output.
select bundle_cnt, job_id from GHCBRMRT.RML_BUNDLE into :ls_wa
ENDEXEC.


FORM loop_output.
WRITE: / ls_wa-ncli,
ls_wa-num.

now my dilemma is how SAP will recognize in DBCON what really "level2" means ?? as the settings are there in ORACLE directory and not inside SAP system.

the dump error msg that I am getting is -

Runtime Errors DBIF_DSQL2_CONNECTERR

Except. CX_SY_NATIVE_SQL_ERROR

Date and Time 13.08.2008 10:20:47

Short text

Error setting up a secondary database connection

What happened?

Connection to database system not possible with

identifier "NEWORA".

Missing Handling of System Exception

Program YY_NEW_CONN

Kindly help in this regard as where I am going wrong.