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.
