.
***************Data Declarations*****************************
SELECTION-SCREEN BEGIN OF BLOCK blk1 WITH FRAME TITLE text-002.
PARAMETERS: tab_name LIKE dntab-tabname.
SELECTION-SCREEN END OF BLOCK blk1 .
SELECTION-SCREEN BEGIN OF BLOCK blk WITH FRAME TITLE text-001.
PARAMETERS :p1 RADIOBUTTON GROUP opr.
PARAMETERS :p2 RADIOBUTTON GROUP opr.
PARAMETERS :p3 RADIOBUTTON GROUP opr.
SELECTION-SCREEN END OF BLOCK blk .
TYPES: BEGIN OF tab_type,
fcode LIKE rsmpe-func,
END OF tab_type.
DATA: tab TYPE STANDARD TABLE OF tab_type WITH
NON-UNIQUE DEFAULT KEY INITIAL SIZE 10,
wa_tab TYPE tab_type.
DATA itab(72) OCCURS 0.
DATA flag.
***************************************************************
DATA: BEGIN OF dict OCCURS 0,
tabname TYPE dntab-tabname,
fieldname TYPE dntab-fieldname,
langu TYPE dntab-langu,
position TYPE dntab-position,
offset TYPE dntab-offset,
keyflag TYPE dntab-keyflag,
fieldtext TYPE dntab-fieldtext,
datatype TYPE dntab-datatype,
inttype TYPE dntab-inttype,
ddlen TYPE dntab-ddlen,
intlen TYPE dntab-intlen,
decimals TYPE dntab-decimals,
checktable TYPE dntab-checktable,
reftable TYPE dntab-reftable,
reffield TYPE dntab-reffield,
precfield TYPE dntab-precfield,
logflag TYPE dntab-logflag,
memoryid TYPE dntab-memoryid,
lowercase TYPE dntab-lowercase,
sign TYPE dntab-sign,
valexi TYPE dntab-valexi,
noforkey TYPE dntab-noforkey,
convexit TYPE dntab-convexit,
set(60),
whr(60),
relop(3),
END OF dict.
START-OF-SELECTION.
CALL FUNCTION 'NAMETAB_GET'
EXPORTING
langu = sy-langu
tabname = tab_name
TABLES
nametab = dict.
DELETE dict WHERE fieldname = 'MANDT'.
IF p2 = 'X'.
CLEAR tab.
MOVE 'DELETE' TO wa_tab-fcode.
APPEND wa_tab TO tab.
MOVE 'UPLOAD' TO wa_tab-fcode.
APPEND wa_tab TO tab.
SET PF-STATUS 'FLD_DYN' EXCLUDING tab.
WRITE:/4(223) sy-uline.
WRITE :/4 sy-vline,
5 'FIELD NAME ',34 sy-vline,
35 'FIELD TEXT',94 sy-vline,
95 'Rel Op.',102 sy-vline,
103 'Condition / Clause',164 sy-vline,
165 'Update Field value',226 sy-vline.
WRITE:/4(223) sy-uline.
LOOP AT dict.
WRITE :/4 sy-vline,
5 dict-fieldname ,34 sy-vline,
35 dict-fieldtext,94 sy-vline.
WRITE : 95 dict-relop INPUT ON,102 sy-vline.
WRITE 103(60) dict-whr INPUT ON .
WRITE : 164 sy-vline,
165(60) dict-set INPUT ON,226 sy-vline.
WRITE:/4(223) sy-uline.
ENDLOOP.
WRITE:/4(223) sy-uline.
ENDIF.
IF p1 = 'X'.
CLEAR tab.
MOVE 'SELECT' TO wa_tab-fcode.
APPEND wa_tab TO tab.
MOVE 'DESELECT' TO wa_tab-fcode.
APPEND wa_tab TO tab.
MOVE 'UPDATE' TO wa_tab-fcode.
APPEND wa_tab TO tab.
MOVE 'DELETE' TO wa_tab-fcode.
APPEND wa_tab TO tab.
SET PF-STATUS 'FLD_DYN' EXCLUDING tab.
WRITE:/4(111) sy-uline.
WRITE :/4 sy-vline,
5 'FIELD NAME ',34 sy-vline,
35 'FIELD TEXT',94 sy-vline,
95 'Insert Field',114 sy-vline.
WRITE:/4(111) sy-uline.
LOOP AT dict.
CLEAR dict-inttype.
CLEAR dict-logflag.
WRITE :/4 sy-vline,
5 dict-fieldname ,34 sy-vline,
35 dict-fieldtext,94 sy-vline.
IF dict-keyflag = 'X'.
dict-logflag = 'X'.
WRITE 95 dict-logflag AS CHECKBOX INPUT OFF.
ELSE.
WRITE 95 dict-logflag AS CHECKBOX.
ENDIF.
WRITE : 114 sy-vline.
CLEAR dict-noforkey.
MODIFY dict TRANSPORTING noforkey.
ENDLOOP.
WRITE:/4(111) sy-uline.
ENDIF.
IF p3 = 'X'.
CLEAR tab.
MOVE 'UPDATE' TO wa_tab-fcode.
APPEND wa_tab TO tab.
MOVE 'UPLOAD' TO wa_tab-fcode.
APPEND wa_tab TO tab.
SET PF-STATUS 'FLD_DYN' EXCLUDING tab.
WRITE:/4(161) sy-uline.
WRITE :/4 sy-vline,
5 'FIELD NAME ',34 sy-vline,
35 'FIELD TEXT',94 sy-vline,
95 'Rel Op.',102 sy-vline,
103 'Condition / Clause',164 sy-vline.
WRITE:/4(161) sy-uline.
LOOP AT dict.
WRITE :/4 sy-vline,
5 dict-fieldname ,34 sy-vline,
35 dict-fieldtext,94 sy-vline.
WRITE : 95 dict-relop INPUT ON,102 sy-vline.
WRITE 103(60) dict-whr INPUT ON .
WRITE : 164 sy-vline.
ENDLOOP.
WRITE:/4(161) sy-uline.
ENDIF.
END-OF-SELECTION.
AT USER-COMMAND.
CASE sy-ucomm.
WHEN 'UPLOAD'.
PERFORM read_list.
WHEN 'DESELECT'.
CLEAR flag.
flag = 'D'.
WHEN 'SELECT'.
CLEAR flag.
WHEN 'UPDATE'.
PERFORM read_listu.
WHEN 'DELETE'.
PERFORM read_listd.
WHEN 'CANCEL'.
LEAVE SCREEN.
WHEN 'EXIT'.
LEAVE SCREEN.
ENDCASE.
----
***INCLUDE ZUTIL_UPDATE_FORMS .
----
&----
*& Form READ_LIST
&----
text
----
--> p1 text
<-- p2 text
----
FORM read_list.
DATA dict_wa LIKE dntab.
DATA answer.
DATA zname(8).
DATA wa(72).
DATA count TYPE i.
CLEAR itab[].
DATA metadata LIKE dntab.
APPEND 'PROGRAM SUBPOOL.' TO itab.
APPEND 'FORM DATA_UPLOAD.' TO itab.
CONCATENATE 'TABLES' tab_name '.' INTO wa SEPARATED BY space.
APPEND wa TO itab.
APPEND 'DATA: BEGIN OF IT_UPLOAD OCCURS 0,' TO itab.
CLEAR count.
CLEAR dict.
DO.
count = count + 1.
CLEAR dict_wa.
READ LINE count FIELD VALUE dict-fieldname
INTO dict_wa-fieldname.
READ LINE count FIELD VALUE dict-logflag
INTO dict_wa-logflag.
IF sy-subrc NE 0.
EXIT.
ELSE.
IF dict_wa-logflag = 'X'.
IF sy-subrc EQ 0 AND NOT dict-convexit IS INITIAL.
dict-noforkey ='X'.
MODIFY dict INDEX sy-tabix FROM dict TRANSPORTING noforkey.
ENDIF.
READ TABLE dict INTO metadata WITH KEY
fieldname = dict_wa-fieldname.
IF sy-subrc EQ 0.
SHIFT metadata-ddlen LEFT DELETING LEADING '0'.
CONCATENATE metadata-fieldname '('
metadata-ddlen ')' ',' INTO wa.
APPEND wa TO itab.
ENDIF.
ENDIF.
ENDIF.
ENDDO.
APPEND 'END OF IT_UPLOAD.' TO itab.
DELETE dict WHERE noforkey NE 'X'.
APPEND 'CALL FUNCTION ''UPLOAD''' TO itab.
APPEND 'EXPORTING' TO itab.
APPEND 'filename = ''C:\''' TO itab.
APPEND 'filetype = ''DAT''' TO itab.
APPEND 'TABLES' TO itab.
APPEND 'DATA_TAB = IT_UPLOAD.' TO itab.
APPEND 'LOOP AT IT_UPLOAD.' TO itab.
LOOP AT dict.
CONCATENATE 'CONVERSION_EXIT_'
dict-convexit '_INPUT' INTO wa.
CONCATENATE 'CALL FUNCTION' ''''wa'''' INTO wa SEPARATED BY space.
APPEND wa TO itab.
APPEND 'EXPORTING' TO itab.
CONCATENATE 'INPUT = ' ' IT_UPLOAD-' dict-fieldname INTO wa.
APPEND wa TO itab.
APPEND 'IMPORTING' TO itab.
CONCATENATE 'OUTPUT = ' ' IT_UPLOAD-' dict-fieldname '.' INTO wa.
APPEND wa TO itab.
ENDLOOP.
CONCATENATE 'MOVE-CORRESPONDING IT_UPLOAD TO'
tab_name '.'
INTO wa SEPARATED BY space.
APPEND wa TO itab.
CONCATENATE 'INSERT' tab_name 'FROM' tab_name '.' INTO wa
SEPARATED BY space.
APPEND wa TO itab.
APPEND 'COMMIT WORK.' TO itab.
APPEND 'ENDLOOP.' TO itab.
APPEND 'ENDFORM.' TO itab.
CALL FUNCTION 'LC_POPUP_TO_CONFIRM_STEP_JNA'
EXPORTING
textline1 = 'Inserts Records into DB'
titel = 'Confirm Operations on DB'
IMPORTING
answer = answer.
IF answer = 'J'.
GENERATE SUBROUTINE POOL itab NAME zname.
PERFORM data_upload IN PROGRAM (zname).
ELSE.
WRITE:/ 'Check your parameters'.
REFRESH itab[].
ENDIF.
ENDFORM. " READ_LIST
&----
*& Form READ_LISTU
&----
text
----
--> p1 text
<-- p2 text
----
FORM read_listu.
DATA cnt(2).
DATA answer.
DATA dict_wa LIKE dict.
DATA zname(8).
DATA:BEGIN OF whr_clause OCCURS 0,
fname(30),
fval(60),
relop(8),
END OF whr_clause.
DATA:BEGIN OF set_clause OCCURS 0,
fname(30),
fval(60),
END OF set_clause.
DATA wa(72).
DATA count LIKE sy-index VALUE '4'.
CLEAR itab[].
CLEAR whr_clause[].
CLEAR set_clause[].
APPEND 'PROGRAM SUBPOOL.' TO itab.
APPEND 'FORM DATA_UPLOAD.' TO itab.
APPEND 'DATA ANSWER.' TO itab.
APPEND 'DATA WA(72).' TO itab.
APPEND 'DATA DBCNT(10).' TO itab.
CONCATENATE 'TABLES' tab_name '.' INTO wa SEPARATED BY space.
APPEND wa TO itab.
DO.
READ LINE count FIELD VALUE dict-fieldname
INTO dict_wa-fieldname.
READ LINE count FIELD VALUE
dict-whr INTO dict_wa-whr.
READ LINE count FIELD VALUE dict-relop
INTO dict_wa-relop.
READ LINE count FIELD VALUE dict-set
INTO dict_wa-set.
CONDENSE dict_wa-whr NO-GAPS.
CONDENSE dict_wa-set NO-GAPS.
CONDENSE dict_wa-relop NO-GAPS.
IF sy-subrc NE 0.
EXIT.
ELSE.
IF NOT dict_wa-whr IS INITIAL.
CLEAR whr_clause.
whr_clause-fname = dict_wa-fieldname.
whr_clause-fval = dict_wa-whr.
whr_clause-relop = dict_wa-relop.
APPEND whr_clause.
ENDIF.
READ TABLE dict WITH KEY fieldname = dict_wa-fieldname.
IF NOT dict_wa-set IS INITIAL.
CLEAR set_clause.
set_clause-fname = dict_wa-fieldname.
set_clause-fval = dict_wa-set.
APPEND set_clause.
ENDIF.
IF dict-keyflag = 'X'
AND dict_wa-whr IS INITIAL
AND flag IS INITIAL.
MESSAGE e031 WITH 'Constraint on Key Field' dict_wa-fieldname
'Cannot be blank.'.
EXIT.
ENDIF.
ENDIF.
count = count + '2'.
ENDDO.
CONCATENATE 'UPDATE' tab_name INTO wa
SEPARATED BY space.
APPEND wa TO itab.
APPEND 'SET' TO itab.
LOOP AT set_clause.
CONCATENATE set_clause-fname ' = '
''''set_clause-fval'''' INTO wa.
APPEND wa TO itab.
ENDLOOP.
APPEND 'WHERE' TO itab.
DESCRIBE TABLE whr_clause.
CLEAR cnt.
LOOP AT whr_clause.
cnt = cnt + 1.
CONCATENATE whr_clause-fname
'$' whr_clause-relop '$'
''''whr_clause-fval'''' INTO wa.
REPLACE '$' WITH space INTO wa.
REPLACE '$' WITH space INTO wa.
APPEND wa TO itab.
IF cnt NE sy-tfill.
APPEND 'AND' TO itab.
ENDIF.
ENDLOOP.
APPEND '.' TO itab.
APPEND 'DBCNT = SY-DBCNT.' TO itab.
APPEND 'CONCATENATE ''Update'' DBCNT ''Records of DB''' TO itab.
APPEND 'INTO WA SEPARATED BY SPACE.' TO itab.
APPEND 'CALL FUNCTION ''LC_POPUP_TO_CONFIRM_STEP_JNA''' TO itab.
APPEND 'EXPORTING' TO itab.
APPEND 'TEXTLINE1 = WA' TO itab.
APPEND 'TITEL = ''Confirm Operations on DB''' TO itab.
APPEND 'IMPORTING' TO itab.
APPEND 'ANSWER = ANSWER.' TO itab.
APPEND 'IF ANSWER = ''J''.' TO itab.
APPEND 'COMMIT WORK.' TO itab.
APPEND 'ELSE.' TO itab.
APPEND 'ROLLBACK WORK.' TO itab.
APPEND 'ENDIF.' TO itab.
APPEND 'ENDFORM.' TO itab.
CALL FUNCTION 'LC_POPUP_TO_CONFIRM_STEP_JNA'
EXPORTING
textline1 = 'Updates Records of DB'
titel = 'Confirm Operations on DB'
IMPORTING
answer = answer.
IF answer = 'J'.
GENERATE SUBROUTINE POOL itab NAME zname.
IF sy-subrc EQ 0.
PERFORM data_upload IN PROGRAM (zname).
ELSE.
WRITE:/ 'Check your parameters'.
CLEAR ITAB[].
ENDIF.
ENDIF.
ENDFORM. " READ_LISTU
&----
*& Form READ_LISTD
&----
text
----
--> p1 text
<-- p2 text
----
FORM read_listd.
DATA dict_wa LIKE dict.
DATA zname(8).
DATA:BEGIN OF whr_clause OCCURS 0,
fname(30),
fval(60),
relop(8),
END OF whr_clause.
DATA wa(72).
DATA cnt(2).
DATA answer.
CLEAR itab[].
CLEAR whr_clause[].
APPEND 'PROGRAM SUBPOOL.' TO itab.
APPEND 'FORM DATA_UPLOAD.' TO itab.
APPEND 'DATA ANSWER.' TO itab.
APPEND 'DATA WA(72).' TO itab.
APPEND 'DATA DBCNT(10).' TO itab.
CONCATENATE 'TABLES' tab_name '.' INTO wa SEPARATED BY space.
APPEND wa TO itab.
DO.
READ LINE sy-index FIELD VALUE dict-fieldname
INTO dict_wa-fieldname.
READ LINE sy-index FIELD VALUE
dict-whr INTO dict_wa-whr.
READ LINE sy-index FIELD VALUE dict-relop
INTO dict_wa-relop.
CONDENSE dict_wa-whr NO-GAPS.
CONDENSE dict_wa-set NO-GAPS.
CONDENSE dict_wa-relop NO-GAPS.
IF sy-subrc NE 0.
EXIT.
ELSE.
IF NOT dict_wa-whr IS INITIAL.
CLEAR whr_clause.
whr_clause-fname = dict_wa-fieldname.
whr_clause-fval = dict_wa-whr.
whr_clause-relop = dict_wa-relop.
APPEND whr_clause.
ENDIF.
READ TABLE dict WITH KEY fieldname = dict_wa-fieldname.
IF dict-keyflag = 'X'.
IF dict-keyflag = 'X'
AND dict_wa-whr IS INITIAL
AND flag IS INITIAL.
MESSAGE e031 WITH 'Constraint on Key Field' dict_wa-fieldname
'Cannot be blank.'.
EXIT.
ENDIF.
ENDIF.
ENDIF.
CLEAR dict.
CLEAR dict_wa.
ENDDO.
CONCATENATE 'DELETE FROM' tab_name INTO wa
SEPARATED BY space.
APPEND wa TO itab.
APPEND 'WHERE' TO itab.
DESCRIBE TABLE whr_clause.
CLEAR cnt.
LOOP AT whr_clause.
cnt = cnt + 1.
CONCATENATE whr_clause-fname
'$' whr_clause-relop '$'
''''whr_clause-fval'''' INTO wa.
REPLACE '$' WITH space INTO wa.
REPLACE '$' WITH space INTO wa.
APPEND wa TO itab.
IF cnt NE sy-tfill.
APPEND 'AND' TO itab.
ENDIF.
ENDLOOP.
APPEND '.' TO itab.
APPEND 'DBCNT = SY-DBCNT.' TO itab.
APPEND 'CONCATENATE ''Delete'' DBCNT ''Records of DB''' TO itab.
APPEND 'INTO WA SEPARATED BY SPACE.' TO itab.
APPEND 'CALL FUNCTION ''LC_POPUP_TO_CONFIRM_STEP_JNA''' TO itab.
APPEND 'EXPORTING' TO itab.
APPEND 'TEXTLINE1 = WA' TO itab.
APPEND 'TITEL = ''Confirm Operations on DB''' TO itab.
APPEND 'IMPORTING' TO itab.
APPEND 'ANSWER = ANSWER.' TO itab.
APPEND 'IF ANSWER = ''J''.' TO itab.
APPEND 'COMMIT WORK.' TO itab.
APPEND 'ELSE.' TO itab.
APPEND 'ROLLBACK WORK.' TO itab.
APPEND 'ENDIF.' TO itab.
APPEND 'ENDFORM.' TO itab.
CALL FUNCTION 'LC_POPUP_TO_CONFIRM_STEP_JNA'
EXPORTING
textline1 = 'Deletes Records from DB'
titel = 'Confirm Operations on DB'
IMPORTING
answer = answer.
IF answer = 'J'.
GENERATE SUBROUTINE POOL itab NAME zname.
PERFORM data_upload IN PROGRAM (zname).
ELSE.
WRITE:/ 'Check your parameters'.
CLEAR ITAB[].
ENDIF.
ENDFORM. " READ_LISTD
Manual
Step-1: Create the report Z_UPD_INS_DEL_TABLE in the SE38 Editor and copy the code sample.
Step-2: Search for SET PF-STATUS ‘FLD_DYN’ in the source code and create the PF-STATUS by double clicking ‘FLD_DYN’ as shown below.
Step-3: Create the text elements as shown below.
Step-4: Compile and activate the report. Now the report is ready for execution. We will illustrate the functionality with a sample table ZDEMOEMPTAB of ABAP Data Dictionary for simplicity.
Step-5: Specify the table name ZDEMOEMPTAB and choose the option ‘Insert Records’ in the initial screen of tool as shown below and click F8.
Step-6: Select the fields for which you need to insert values in the records as shown below.
Step-7: Prepare the tab delimited file with fields in the same order as the fields chosen in Step-6.
Step-8: Click ‘Upload’ button as shown below which will pop up a window asking for the file for uploading. Select the test file which is created in Step-7.
Step-9: Verify the data in ABAP Data Dictionary.
Step-10: Now we will demonstrate the update operation on the same records. Choose the ‘Update Records’ option in the Initial Screen.
Step-11: Next Screen gives the option of entering the where clause and the set clause to perform the update statement on the table. Employee record 106088 is updated with the name ‘ABAP PUNTER’.
Where Clause: Feed the Employee Number 106088 in the Employee Id column and give the relational operator as “=”.Relational operators ‘<,>’ can also be used in the Where Clause.
Set Clause: Scroll right and input the name ‘ABAP PUNTER’ in the Employee Name field and click ‘Commit’.
Step-12: Data will be updated in the database and it can be verified through SE11 transaction as shown below.
Step-13: Now we will demonstrate the last option, delete operation on the same records. Choose the ‘Delete Records’ option in the Initial Screen.
Step-14: Next Screen gives the option of entering the where clause for deleting all the records which are created in the above steps.
To delete all the records we give the where clause as “Delete all the records with the employee no less than 106091’.Click Delete .All the 3 records will be deleted from the data base which can be verified through SE11 transaction.
Safety Measures: Check has been made in the program for checking whether all the key fields have been specified in the where clause. However the check can be deselected if needed as shown below.
Limitations
Tool has to be used carefully as it directly performs operations on the database.
It is purely database level operation so it doesn’t provide any data consistency checks.
It is powerful tool but dangerous too if it is used without understanding the functionality.
Way Forward
Generic codes like this can be exposed as a web service which can be consumed by developers across the globe.
7 Comments
