6 Replies Latest reply on Dec 7, 2019 9:04 AM by Denes Kubicek

    Formating Code in SQL Developer

    Denes Kubicek

      This is a small package that prints out a grid. The result I am getting by formating it via SQL Developer is quite strange. I am using the absolute standard. Version is 19.1:

       

       

       

       

      CREATE OR REPLACE PACKAGE BODY "MATRIX_MANAGEMENT_UTIL_PKG" AS

       

       

          c_region_start   VARCHAR2(4000) := '<div class="t-Report-tableWrap"> ' || '<table class="t-Report-report"> ' || '<tbody>';

          c_region_end     VARCHAR2(4000) := ' </tbody> ' || '</table> ' || '</div> '; 

       

       

         PROCEDURE print_table (

              p_message OUT VARCHAR2

          ) IS

       

       

              v_row                VARCHAR2(4000) := '<tr>'; 

              v_count_col          NUMBER := 0;

              v_count_row          NUMBER := 0;

              v_count_cell         NUMBER := 0;

              v_sum_col_id         NUMBER;

              v_row_limit_failed   VARCHAR2(1) := 'N';

          BEGIN

              htp.p(c_region_start); 

            /*this is for horizontal header*/

              FOR t_col IN (

                  SELECT

                      id,

                      name,

                      CASE

                          WHEN name IS NOT NULL THEN

                              '#80808075'

                          ELSE

                              NULL

                      END background,

                      type

                  FROM

                      t_matrix_columns b

                  ORDER BY

                      id

              ) 

            /*this order by is very important !!! */ LOOP

                  v_count_col := v_count_col + 1;

                  IF t_col.type IS NOT NULL THEN

                      v_sum_col_id := t_col.id;

                  END IF;

                  v_row := v_row || '<td ' || ' class="t-Report-colHead" id="' || t_col.id || '" align="center" style="background-color:'

                  || t_col.background || '"' || ' headers="SUBSTANZ"> ' || t_col.name || '</td>';

       

       

              END LOOP;

       

       

              v_row := v_row || '</tr><tr cotoi>'; 

       

       

            /*end of horizontal header*/

              FOR t_row IN (

                  SELECT

                      id,

                      name,

                      limit

                  FROM

                      t_matrix_rows

                  ORDER BY

                      id ASC

              ) LOOP

                  v_count_row := v_count_row + 1;

                  v_row := v_row || '<td class="t-Report-cell" style="font-weight:bold !important" ' || 'align="center" id="' || TO_CHAR

                  (t_row.id) || '"><div style="width:110px; ' || 'display:block; white-space:pre-wrap;height:32px">' || t_row.name || '</br> (Limit: '

                  || TO_CHAR(t_row.limit, 'FM999G999G999G999G990D00') || ')' || '</div></td>';

       

       

                  htp.p(v_row);

                  v_row := NULL;

                  FOR t_cell 

               /*this loop goes right - behaelter*/ IN (

                      SELECT

                          id,

                          value,

                          col_id

                      FROM

                          t_matrix_management

                      WHERE

                          row_id = t_row.id

                      ORDER BY

                          col_id

                  ) LOOP

                      v_count_cell := v_count_cell + 1;

                      IF t_row.limit < to_number(t_cell.value, 'FM999G999G999G999G990D00') AND t_cell.col_id = v_sum_col_id THEN

                          v_row_limit_failed := 'Y';

                      END IF;

       

       

                      v_row := v_row || '<td ' || ' class="t-Report-cell" id="SUBMAN" align="right"' || ' headers="' || TO_CHAR(t_row.

       

       

                      id) || '">' || apex_item.text(p_idx => 1, p_value => t_cell.value, p_size => 10, p_maxlength => 20, p_attributes

       

       

                      => 'class="t-Report-cell" data-row-id="' || t_row.id || '" data-id="' || t_cell.id || '" data-sum-col-id = "' ||

       

       

                      v_sum_col_id ||

                          CASE

                              WHEN t_cell.col_id != v_sum_col_id THEN

                                  '" onchange="setVal(this);"'

                              ELSE

                                  '" disabled="disabled"'

                          END

      ||

                          CASE

                              WHEN v_row_limit_failed = 'Y' THEN

                                  'style ="background-color:#ffcccb;text-decoration: line-through;text-align:right"'

                              ELSE

                                  'style ="text-align:right"'

                          END, p_item_id =>

                          CASE

                              WHEN t_cell.col_id != v_sum_col_id THEN

                                  'CELL' || t_row.id || t_cell.col_id

                              ELSE

                                  'SUM' || t_row.id

                          END

                      );

       

       

                      htp.p(v_row);

                      v_row := NULL;

                  END LOOP;

       

       

                  v_row := v_row || '</tr>';

                  htp.p(v_row);

                  v_count_cell := 0;

                  v_row_limit_failed := 'N';

              END LOOP;

       

       

              htp.p(c_region_end);

          EXCEPTION

              WHEN OTHERS THEN

                  p_message := 'Error : ' || sqlerrm || ' / ' || dbms_utility.format_error_backtrace;

                  htp.p('Error : ' || sqlerrm || ' / ' || dbms_utility.format_error_backtrace);

          END print_table;

       

       

      END matrix_management_util_pkg;