topic Re: How can we modify DB table dynamically in Application Development and Automation Discussions

How can we modify DB table dynamically

Former Member — Thu, 14 Jan 2016 10:51:40 GMT

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 ???

Re: How can we modify DB table dynamically

RaymondGiuseppi — Thu, 14 Jan 2016 11:10:39 GMT

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

Re: How can we modify DB table dynamically

shadab_maldar — Thu, 14 Jan 2016 11:34:50 GMT

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.

Re: How can we modify DB table dynamically

Former Member — Thu, 14 Jan 2016 11:40:00 GMT

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 ???

Re: How can we modify DB table dynamically

Former Member — Thu, 14 Jan 2016 11:54:01 GMT

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 ??

Re: How can we modify DB table dynamically

RaymondGiuseppi — Thu, 14 Jan 2016 12:26:43 GMT

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

Re: How can we modify DB table dynamically

shadab_maldar — Thu, 14 Jan 2016 12:27:33 GMT

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.

Re: How can we modify DB table dynamically

shadab_maldar — Wed, 20 Jan 2016 08:50:05 GMT

Hi Priya,

Did you got the solution for you issue??

Regards,

Shadab.

Re: How can we modify DB table dynamically

Former Member — Wed, 20 Jan 2016 11:59:29 GMT

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.

Re: How can we modify DB table dynamically

Former Member — Thu, 21 Jan 2016 04:23:09 GMT

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.

Re: How can we modify DB table dynamically

Chintu6august — Thu, 21 Jan 2016 06:30:29 GMT

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!!