Dear gurus
i found a very strange problem. in our scenario, data is read from SAP R3 tables and populated into an oracle db by using native sql. the connection of the oracle db is maintained in DBCO.
the problem is:
if the program is triggered by the background job, the oracle db won't be updated occationally even data should be populated there. however the sy-subrc value right after the native sql is still 0.
on the other hand, if the program is triggerred in online mode, problem has never occurred.
here is the code.
TRY .
EXEC SQL.
CONNECT TO :ZCON " ZCOM is maintained in the DBCO
ENDEXEC.
LOOP AT it_zmm. " this is the internal table. value of this table is designed to be populated into oracle db
CLEAR zcount.
EXEC SQL.
OPEN dbcur FOR
SELECT count(*)
as zcount
from MATWZADSDATABARGAIN
where DEPT_CODE = '41311016140100001'
and TAB_YEAR = TRIM(:TAB_YEAR)
and TAB_MONTH = TRIM(:TAB_MONTH)
and TAB_DAY = TRIM(:TAB_DAY)
and ORDERCODE = trim(:it_zmm-EBELN)
and ORDERITEM = trim(:it_zmm-EBELP)
ENDEXEC.
DO.
EXEC SQL.
FETCH NEXT dbcur INTO
:ZCOUNT
ENDEXEC.
IF sy-subrc <> 0.
EXIT.
ENDIF.
ENDDO.
EXEC SQL.
CLOSE dbcur
ENDEXEC.
CONCATENATE sy-datum sy-uzeit INTO z_ins_time.
IF zcount > 0.
EXEC SQL.
UPDATE MATWZADSDATABARGAIN set
PROJECTTYPE = trim(:it_zmm-ZXMLX),
PRJCODE = trim(:it_zmm-PSPID),
PROJECTNAME = trim(:it_zmm-POST1),
PURMODE = trim(:it_zmm-ZPUTYP_TEXT),
BIDBATCH = trim(:it_zmm-ZZBJHBH),
where DEPT_CODE = '41311016140100001'
and TAB_YEAR = TRIM(:TAB_YEAR)
and TAB_MONTH = TRIM(:TAB_MONTH)
and TAB_DAY = TRIM(:TAB_DAY)
and ORDERCODE = trim(:it_zmm-EBELN)
and ORDERITEM = trim(:it_zmm-EBELP)
ENDEXEC.
IF sy-subrc <> 0.
p_subrc = sy-subrc.
EXIT.
ELSE.
ADD 1 TO zlines.
ENDIF.
it_zmm-instime = z_ins_time.
MODIFY it_zmm.
COMMIT WORK.
ELSE.
EXEC SQL.
insert into MATWZADSDATABARGAIN(
DEPT_CODE,"
TAB_YEAR,
TAB_MONTH,
TAB_DAY,
ORDERCODE,
ORDERITEM,
PROJECTTYPE,"
PRJCODE,
PROJECTNAME,"
PURMODE,"
BIDBATCH,"
) values
(
trim('41311016140100001'),
trim(:TAB_YEAR),
trim(:TAB_MONTH),
trim(:TAB_DAY),
trim(:it_zmm-EBELN),
trim(:it_zmm-EBELP),
trim(:it_zmm-ZXMLX),
trim(:it_zmm-PSPID),
trim(:it_zmm-POST1),
trim(:it_zmm-ZPUTYP_TEXT),
trim(:it_zmm-ZZBJHBH)
)
ENDEXEC.
IF sy-subrc <> 0.
p_subrc = sy-subrc.
EXIT.
ELSE.
ADD 1 TO zlines.
ENDIF.
it_zmm-instime = z_ins_time.
MODIFY it_zmm.
COMMIT WORK.
ENDIF.
ENDLOOP.
IF p_subrc = 0.
EXEC SQL.
COMMIT WORK " Can i use commit wor and rollback work here for native sql? how to make data consist?
ENDEXEC.
ELSE.
EXEC SQL.
ROLLBACK WORK
ENDEXEC.
ENDIF.
EXEC SQL.
DISCONNECT :zCON
ENDEXEC.
CATCH cx_sy_native_sql_error INTO exc_ref.
"get the message
ls_result = exc_ref->get_text( ).
CATCH cx_sql_exception INTO sqlerr_ref.
"get the message
IF sqlerr_ref->db_error = 'X'.
ls_result = sqlerr_ref->sql_message.
ELSE.
ls_result = sqlerr_ref->internal_error.
ENDIF.
ENDTRY.
the problems are:
1. data can not be updated into the oracle db sometimes in background job.
2. if problem 1 occurs, the p_subrc is still 0, where it should be 4.
Bluesky
