SAP连接外部ORACLE数据库

SAP连接外部ORACLE数据库 
1.先在SAP底层ORACLE数据库编辑TNS文件,一般由BASIS配置完成.配置完成后我们可以用事务码:AL11查看配置是否正确,路径:DIR_ORAHOME->network->admin->tnsnames.ora查看对应的TNS是否配置正确,如:

ORADB04.world = 
(DESCRIPTION = 
(ADDRESS_LIST = 
(ADDRESS = 
(PROTOCOL = TCP) 
(HOST = XXX.XXX.XXX.XXX) 
(PORT = 1521) 
) 
) 
(CONNECT_DATA = 
(SERVICE_NAME = XXXX) 
) 
)

2.在SAP中用DBCO配置,如下:

 

 

 

3.编写ABAP程序实现SAP访问外部ORACLE数据库,并插入数据。 
有两个例子： 

例子1： 
REPORT zljc_nativesql2 . 
DATA: p_dbname(10) VALUE ‘SIPS’. 
DATA: l_sql_error TYPE REF TO cx_sy_native_sql_error, 
l_error_text TYPE string.

TYPES: BEGIN OF ty_room, 
room_id(30), 
room_name(100), 
room_people(10), 
room_desc(100), 
END OF ty_room.

DATA: gt_room TYPE TABLE OF ty_room , 
gs_room TYPE ty_room.

gs_room-room_id = ‘no-201’. 
gs_room-room_name = ‘风波亭’. 
gs_room-room_people = ‘5’. 
gs_room-room_desc = ‘岳武穆遗书’. 
APPEND gs_room TO gt_room.

gs_room-room_id = ‘no-202’. 
gs_room-room_name = ‘威虎山’. 
gs_room-room_people = ‘5’. 
gs_room-room_desc = ‘座山雕老巢’. 
APPEND gs_room TO gt_room.

gs_room-room_id = ‘no-203’. 
gs_room-room_name = ‘白宫’. 
gs_room-room_people = ‘29’. 
gs_room-room_desc = ‘美国总统府’. 
APPEND gs_room TO gt_room.

gs_room-room_id = ‘no-204’. 
gs_room-room_name = ‘鸟巢’. 
gs_room-room_people = ‘5’. 
gs_room-room_desc = ‘奥运会主场馆’. 
APPEND gs_room TO gt_room.

gs_room-room_id = ‘no-205’. 
gs_room-room_name = ‘凤仪亭’. 
gs_room-room_people = ‘5’. 
gs_room-room_desc = ‘貂蝉和吕布’. 
APPEND gs_room TO gt_room.

gs_room-room_id = ‘no-206’. 
gs_room-room_name = ‘伪皇宫’. 
gs_room-room_people = ‘5’. 
gs_room-room_desc = ‘伪满皇帝’. 
APPEND gs_room TO gt_room.

TRY. 
EXEC SQL. 
CONNECT TO :p_dbname 
ENDEXEC.

CATCH cx_sy_native_sql_error INTO l_sql_error. 
CALL METHOD l_sql_error->get_text 
RECEIVING 
result = l_error_text. 
WRITE: AT /1 l_error_text. 
ENDTRY. 
IF sy-subrc <> 0. 
WRITE: /1 ‘连接到数据库失败:’, l_error_text ,’,请联系管理员!’.

STOP. 
ENDIF.

TRY. 
LOOP AT gt_room INTO gs_room. 
EXEC SQL. 
insert into ljc_room 
( room_id, 
room_name, 
room_people, 
room_desc ) 
values(:gs_room-room_id, 
:gs_room-room_name, 
:gs_room-room_people, 
:gs_room-room_desc)

   ENDEXEC.
 ENDLOOP.
 "捕获异常
1
2
3
CATCH cx_sy_native_sql_error INTO l_sql_error. 
l_error_text = l_sql_error->get_text( ). 
ENDTRY. 
“*操作Oracle数据库时，异常处理 
IF NOT l_error_text IS INITIAL. “如果捕获到异常，记录日志，回滚 
CLEAR l_error_text. 
EXEC SQL. 
rollback 
ENDEXEC. 
ELSE. ” 如果无异常，提交插入数据 
EXEC SQL. 
commit 
ENDEXEC. 
ENDIF.

EXEC SQL. 
DISCONNECT :p_dbname 
ENDEXEC.

 

举例  2

DATA: gw_dbs TYPE dbcon-con_name VALUE 'Z_ORACLE'.

TRY.
EXEC SQL.
connect to :gw_dbs
ENDEXEC.

CATCH cx_sy_native_sql_error INTO g_obj_sqldb.
CALL METHOD g_obj_sqldb->get_text
RECEIVING
result = g_error_text.
ENDTRY.
IF g_error_text is INITIAL.
EXEC SQL.
SET CONNECTION 'Z_ORACLE'
ENDEXEC.
TRY .
EXEC SQL PERFORMING loop_output.
SELECT *
FROM hfm_sap_gl_balances
INTO :wa
ENDEXEC.
CATCH cx_sy_native_sql_error INTO g_obj_sqldb.
CALL METHOD g_obj_sqldb->get_text
RECEIVING
result = g_error_text.
ENDTRY.
ELSE.
MESSAGE s000 WITH g_error_text DISPLAY LIKE 'E'.
LEAVE LIST-PROCESSING.
ENDIF.

FORM loop_output.
APPEND gs_balances TO gt_balances.
ENDFORM.