abap insert oracle,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.

"捕获异常

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：

Function module：ZMM_SIPS_ROOM

调用funtion module 的程序： ZLJC_NATIVESQL3