topic Re: Insert into remote table - ORA-02019 in Application Development and Automation Discussions

Insert into remote table - ORA-02019

Former Member — Tue, 01 Jul 2014 13:36:38 GMT

Hello,

I need to insert some data from SAP program to remote database table. Both SAP and remote tables are Oracle.

There is db link 'CONN' created with username 'conn_user'. SAP user which starts the program is different than 'conn_user'.

Insert statement gives me: The error 2019 occurred in the current database connection "DEFAULT".
After googling, I suppose the problem is how to connect to the remote database with user different than SAP's one, but I still have no clue about what is wrong in my code.

Here is the code:

EXEC SQL.

CONNECT TO 'CONN' as 'conn_user'

ENDEXEC.

EXEC SQL.

insert into TABLE1@CONN ( company_code )

values ( '1000' )

ENDEXEC.

EXEC SQL.

commit

ENDEXEC.

EXEC SQL.

DISCONNECT 'CONN'

ENDEXEC.

Thanks in advance.

Re: Insert into remote table - ORA-02019

raphael_almeida — Tue, 01 Jul 2014 14:54:08 GMT

Hi Natasa

Why you pass the connection on insert? The statement above make connection.

Other point you can connect in this form:

DATA v_conn LIKE DBCON-CON_NAME VALUE 'YOURCONN'.

EXEC SQL.

CONNECT TO :v_conn

ENDEXEC.

Other point, why do you try to use ADBC ?

Regards

Re: Insert into remote table - ORA-02019

Former Member — Wed, 02 Jul 2014 08:00:14 GMT

Hi Raphael,

Actually, the problem is the statement

CONNECT TO 'CONN' as 'conn_user'

which doesn't make the connection.

After that statement, sy-subrc is 4. Even when I read CON_NAME and USER_NAME from table DBCON, there is no change. I don't know what is wrong.

And what is ADBC?

Regards

Re: Insert into remote table - ORA-02019

Former Member — Wed, 02 Jul 2014 09:09:27 GMT

Hi,

Once the DB connection is establish between SAP and a remote DB, no need of user id and password during runtime.

Below code works for connecting remote DB

EXEC SQL.

CONNECT TO :lv_dbcon

ENDEXEC.

If you want test DB connection, go to SE38 / SA38 transaction and give ADBC_TEST_CONNECTION and test the DB Connection by providing DB Connection name in the selection screen.

Regards

Re: Insert into remote table - ORA-02019

Former Member — Wed, 02 Jul 2014 09:47:22 GMT

Thank you Chandra.

Test of DB connection gave me an error:

ORA-12154: TNS:could not resolve the connect identifier specified.

I have contacted BC team.

Regards

Re: Insert into remote table - ORA-02019

Former Member — Wed, 02 Jul 2014 10:03:31 GMT

Hi,

ORA-12154 is the error indicates that there is some thing wrong in TNS entry. Ask your BASIS team to check that correct TNS entry placed in SAP or not.

Regards,

Chandra

Re: Insert into remote table - ORA-02019

Former Member — Wed, 02 Jul 2014 10:35:44 GMT

Yes, I know what is that error and I informed BC team about that.

Thank you.

Regards,

Re: Insert into remote table - ORA-02019

raphael_almeida — Wed, 02 Jul 2014 12:21:47 GMT

Natasa,

You have this connection inserted on DBCO ?

For connection with other databases you need:

The client configured on SAP Server (For Oracle - Oracle Client, For MSSQL - ODBC Entry, etc...);
This connection inserted on transaction DBCO and tested on transaction DB6TRC;

With this, how i said, you need pass the connection description (created on DBCO) in EXEC SQL ou ADBC Classes, how example below:

Example with EXEC SQL:

DATA v_conn LIKE DBCON-CON_NAME VALUE 'CONNECTIONDESCRIPTION'.

EXEC SQL.

CONNECT TO :v_conn

ENDEXEC.

Example with ADBC:

DATA: og_connect type ref to cl_sql_connection,

v_conn LIKE DBCON-CON_NAME VALUE 'CONNECTIONDESCRIPTION'.

create object og_connect.
og_connect = cl_sql_connection=>get_connection( v_conn ).

Adding, on SAP have a demo program called ADBC_DEMO, which have more examples.

Regards,

Re: Insert into remote table - ORA-02019

Former Member — Wed, 23 Jul 2014 11:43:25 GMT

Sorry for late reply.

Finally, customer BC team solved the problem with tnsnames.

Thank you for helpful instructions.

Regards,

Natasa