How can we modify DB table dynamically

[Former Member]

Hi All,

I need to modify DB table from Selection Screen.

i.e. If DB table - MARA is entered in Selection Screen.

I want -

MODIFY FROM (dynamic tab i.e. MARA) WHERE....

How can I fetch DB Table name dynamically ???

[RaymondGiuseppi]

Press F1 on MODIFY statement, you almost use the current syntax.

But, don't update standard tables with open-sql statement, not to mention dynamic statements, look for BAPI, transactions (and BDC), IDOC.

Regards,

Raymond

[Former Member]

Hi Raymond,

Thanks for reply.

I understand the importance of std. SAP Tables,

But this time, requirement is bit diff & it's interface with non-SAP then I need to work on SAP Dev.

so, I am looking for dynamic option for this purpose.

can you please help me on this -

In DB Table if I need to work on MODIFY, DELETE things -

i.e. DELETE from DBTAB.

where my DBTAB is list of 100 tables in one itab, & I want to delete reccords of this all 100 DB tables, how can I do ???

[RaymondGiuseppi]

If you want to delete every records of those tables, don't delete each record with the dynamic option for DELETE statement, same dbtab syntax, but use following FM in sequence (performance)

• DB_STORAGE_SAVE " save current attributes of database table
• DD_DROP_TABLE " delete database table
• DD_GET_NAMETAB " read nametab
• DD_CREATE_TABLE " recreate database table

For other requirements:

• OPEN-SQL requirements, press F1 on open-sql statement look for dynamic options like dbtab syntax or sql cond.
• dynamic internal tables, use scn or sap search tool, or for ddic structure look at syntax of CREATE DATA statement.

Regards,

Raymond

[shadab_maldar]

Hi Priya,

I suggest not to modify any database table here, Instead I will show you, how to make dynamic internal table and work area for a given table in parameter of a selection screen.

You can make use of it to get your requirement done (Try on Z tables). You can fill the internal table/ work area and modify the Z tables.

Parameters: p_table like dd02L-tabname.

Data: dref type ref to data.

FIELD-SYMBOLS : <fs_data> type any,

                               <fs_datatab> type standard table.

CREATE DATA dref type standard table of (p_table).

ASSIGN dref->* to <fs_datatab>.

CREATE DATA dref type (p_table).

ASSIGN dref->* <fs_data>.

Let me know if you are facing any issues.

Regards,

Shadab.

[Former Member]

Hi Shadab,

Even I try it with field symbol.

But how It will work with my DB statements i.e. MODIFY or DELETE ??

DELETE FROM <FS_TAB> CLIENT SPECIFIED WHERE......

Is it ??? - This is giving error 'Dict. struct. <fs_tab> is not active does not exist'

Can you please specify how I can use <fs_tab> with DELETE statement ??

[shadab_maldar]

Hi Priya,

As I explained in above post fill internal table <fs_datatab> than

loop at <fs_datatab> ASSIGNING <fs_data>.

     modify (p_table) from <fs_data>.

endloop.

Or you can use

modify (p_table) from table <fs_datatab>.

I think this should work.

Regards,

Shadab.

[shadab_maldar]

Hi Priya,

Did you got the solution for you issue??

Regards,

Shadab.

[Former Member]

Possible without field symbol too

Take internal table- itab with all your required DB tables as column (data element - DD02L-tabname).

LOOP AT itab INTO wa.

DELETE/MODIFY from (wa) WHERE

COMMIT WORK.

ENDLOOP.

[Former Member]

Try this:

TYPE-POOLS : abap.

FIELD-SYMBOLS: <dyn_table> TYPE STANDARD TABLE,

                <table_out> TYPE STANDARD TABLE,

                <dyn_wa>,

                <dyn_field>.

DATA: dy_table TYPE REF TO data,

     dy_line  TYPE REF TO data,

     xfc TYPE lvc_s_fcat,

     ifc TYPE lvc_t_fcat.

PARAMETERS: p_table(23) TYPE c.

START-OF-SELECTION.

   PERFORM get_structure.

   PERFORM create_dynamic_itab.

*******Creates a dynamic internal table*********

   PERFORM get_data.

*Insert Modify Table Logic Here

*MODIFY <table> FROM <work-area/table> WHERE.

FORM get_structure.

     DATA : ref_table_des TYPE REF TO cl_abap_structdescr.

     DATA : idetails TYPE abap_compdescr_tab,

            xdetails TYPE abap_compdescr,

            v_index TYPE i.

    ref_table_des ?= cl_abap_typedescr=>describe_by_name( p_table ).

    idetails[] = ref_table_des->components[].

    v_index = 2.

   LOOP AT idetails INTO xdetails.

     READ TABLE idetails INTO xdetails INDEX v_index.

     IF sy-subrc EQ 0.

       CLEAR xfc.

       xfc-fieldname = xdetails-name.

       xfc-scrtext_l = xdetails-name.

       xfc-inttype = xdetails-type_kind.

       xfc-intlen = xdetails-length.

       xfc-decimals = xdetails-decimals.

       APPEND xfc TO ifc.

       ADD 1 TO v_index.

     ENDIF.

   ENDLOOP.

ENDFORM.

FORM create_dynamic_itab.

* Create dynamic internal table and assign to FS

   CALL METHOD cl_alv_table_create=>create_dynamic_table

     EXPORTING

       it_fieldcatalog  = ifc

       i_length_in_byte = 'X'

     IMPORTING

       ep_table         = dy_table.

   ASSIGN dy_table->* TO <dyn_table>.

* Create dynamic work area and assign to FS

   CREATE DATA dy_line LIKE LINE OF <dyn_table>.

   ASSIGN dy_line->* TO <dyn_wa>.

ENDFORM.

FORM get_data.

* Select Data from table.

   SELECT * INTO CORRESPONDING FIELDS OF TABLE <dyn_table>

              FROM (p_table).

   IF sy-subrc EQ 0.

     SORT <dyn_table>.

   ENDIF.

ENDFORM.

[Chintu6august]

Hi,

This program will take Table name and input file path as Input. Dynamically programs will cerate the internal table as name given for
table name. Data from file will be store in the internal table, which
will update the database. And generate the error file for dispaly
Note: input file format will be same as table name.


REPORT  ysb_update_table LINE-SIZE 120 LINE-COUNT 65

                         NO STANDARD PAGE HEADING.

TABLES: dd02l.

DATA: n TYPE i.

DATA dref TYPE REF TO data.

FIELD-SYMBOLS <tab> TYPE table.

FIELD-SYMBOLS <wa_tab>.

FIELD-SYMBOLS <er_tab> TYPE table.

DATA : su_count TYPE i VALUE 0.

DATA : er_count TYPE i VALUE 0.

DATA: cursor_field(40),

      call_field(40)    VALUE 'Click : Call SE16 Screen'.



*Selection Screen

SELECTION-SCREEN BEGIN OF BLOCK b1 WITH FRAME.

*Table name

PARAMETERS: p_table LIKE dd02l-tabname OBLIGATORY.

*Input File name

PARAMETERS p_file TYPE rlgrap-filename OBLIGATORY.

SELECTION-SCREEN END OF BLOCK b1.



*at Selection screen for check table name in Database

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_table.

  PERFORM fr_check_table_exists.

*at selection screen for check input file path

AT SELECTION-SCREEN ON VALUE-REQUEST FOR p_file.

  CALL FUNCTION 'KD_GET_FILENAME_ON_F4'

    CHANGING

      file_name     = p_file

    EXCEPTIONS

      mask_too_long = 1

      OTHERS        = 2.

  IF sy-subrc <> 0.

  ENDIF.





TOP-OF-PAGE.

*===========

  PERFORM fr_top_of_page.





START-OF-SELECTION.

*==================



*Create Internal Table

  PERFORM fr_create_table.

*Upload file into internal table

  PERFORM fr_upload_table.

*Update the Database

  IF NOT <tab>[] IS INITIAL.

    LOOP AT <tab> INTO <wa_tab>.



      MODIFY (p_table) FROM <wa_tab>.

      IF sy-subrc EQ 0.

        su_count = su_count + 1.

      ELSE.

        er_count = er_count + 1.

        APPEND <wa_tab> TO <er_tab>.

      ENDIF.

    ENDLOOP.

  ENDIF.



*Dispaly

  IF NOT <tab>[] IS INITIAL.

*   Total No. of Records in file'.

    DESCRIBE TABLE <tab> LINES n.

    WRITE : /2 'Total number of records in the file' COLOR COL_HEADING INTENSIFIED.

    WRITE : 40 n.

*   Success Record Update

    WRITE : /2 'Total Number of records Updated' COLOR COL_HEADING INTENSIFIED.

    WRITE : 40 su_count.

*   UnSuccess Recoerd Update

    WRITE : /2 'Record not Updated' COLOR COL_HEADING INTENSIFIED.

    WRITE : 40 er_count.

  ENDIF.



*For at line selection screen.

Skip 2.

WRITE: / call_field.



*Display : Error file

  IF NOT <er_tab> IS INITIAL.

    WRITE : /2 'List for record not Updated' COLOR COL_HEADING INTENSIFIED.

    LOOP AT <er_tab> INTO <wa_tab>.

      WRITE /5 <wa_tab>.

    ENDLOOP.

  ENDIF.





AT LINE-SELECTION.

*=================

  GET CURSOR FIELD cursor_field.



  CASE cursor_field.

    WHEN 'CALL_FIELD'.

      SET PARAMETER ID 'DTB' FIELD p_table.

      CALL TRANSACTION 'SE16' AND SKIP FIRST SCREEN.

  ENDCASE.



*&---------------------------------------------------------------------*

*&      Form  fr_check_table_exists

*&---------------------------------------------------------------------*

*  check table name in Database

*----------------------------------------------------------------------*

FORM fr_check_table_exists .

  SELECT SINGLE tabname FROM dd02l

  INTO CORRESPONDING FIELDS OF dd02l

  WHERE tabname = p_table.



  CHECK syst-subrc NE 0.

  MESSAGE e402(mo) WITH p_table.

ENDFORM.                    " fr_check_table_exists



*&---------------------------------------------------------------------*

*&      Form  fr_create_table

*&---------------------------------------------------------------------*

* create dynamic internal table and work area

*----------------------------------------------------------------------*

FORM fr_create_table .

*Create dynamic Internal table

  CREATE DATA dref TYPE STANDARD TABLE OF (p_table)

  WITH NON-UNIQUE DEFAULT KEY.

  ASSIGN dref->* TO <tab>.



*Create dynamic work area

  CREATE DATA dref LIKE LINE OF <tab>.

  ASSIGN dref->* TO <wa_tab>.



*Create dynamic Internal table for error file

  CREATE DATA dref TYPE STANDARD TABLE OF (p_table)

  WITH NON-UNIQUE DEFAULT KEY.

  ASSIGN dref->* TO <er_tab>.


ENDFORM.                    " fr_create_table



*&---------------------------------------------------------------------*

*&      Form  fr_upload_table

*&---------------------------------------------------------------------*

*  upload the table into the internal table

*----------------------------------------------------------------------*

form fr_upload_table .

  DATA:  v_filename TYPE string.



  v_filename  = p_file.



  CALL FUNCTION 'GUI_UPLOAD'

    EXPORTING

      filename                = v_filename

      filetype                = 'ASC'

      has_field_separator     = 'X'

    TABLES

      data_tab                = <tab>

    EXCEPTIONS

      file_open_error         = 1

      file_read_error         = 2

      no_batch                = 3

      gui_refuse_filetransfer = 4

      invalid_type            = 5

      no_authority            = 6

      unknown_error           = 7

      bad_data_format         = 8

      header_not_allowed      = 9

      separator_not_allowed   = 10

      header_too_long         = 11

      unknown_dp_error        = 12

      access_denied           = 13

      dp_out_of_memory        = 14

      disk_full               = 15

      dp_timeout              = 16

      OTHERS                  = 17.

  IF sy-subrc <> 0.

    MESSAGE e000(zpp) WITH 'Error In Uploading File'.

    RETURN.

  ENDIF.

endform.                    " fr_upload_table

*&---------------------------------------------------------------------*

*&      Form  fr_top_of_page

*&---------------------------------------------------------------------*

* Report Header

*----------------------------------------------------------------------*

FORM fr_top_of_page .



  WRITE:/50 'DATABASE TABLE MODIFY USING FILE'.

  WRITE:/45 'Table Update - ', p_table.

  ULINE.

  WRITE:/1  sy-vline,

         2 'Username:',

        12  sy-uname,

        70 'Program Name :',

        85  sy-repid,

       120  sy-vline.

  ULINE.

  WRITE:/1   sy-vline,

         2  'Date    :',

         12  sy-datum,

         70  'Client       :',

         85  sy-mandt,

         120 sy-vline.

  ULINE.

ENDFORM.                    " fr_top_of_page

thanks!!