begin
 
declare
    s varchar2(32767) := null;
begin
s:=s||'ng from second'||unistr('\000a')||
'  * and applies a thin border to fix missing borders with merged cells.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE fix_borders'||unistr('\000a')||
'  AS'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    FOR i IN 2..g_xlsx_options.display_column_count LOOP'||unistr('\000a')||
'    /* strange fix for borders... */'||unistr('\000a')||
'      xlsx_builder_pkg.cell( p_col => i'||unistr('\000a')||
'                           , p_row => g_current_disp_row'||unistr('\000a')||
'                           , p_value => to_char(NULL)'||unistr('\000a')||
'                          ';

s:=s||' , p_borderId => xlsx_builder_pkg.get_border(''thin'', ''thin'', ''thin'', ''thin'')'||unistr('\000a')||
'                           , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                           );'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'  END fix_borders;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Prints the filter definitions to the spreadsheet.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_filter_header'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_condition_display VARCHAR2(4100);'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    FOR rec IN (SELECT condition_type,'||unistr('\000a')||
'         ';

s:=s||'              cond.condition_name,'||unistr('\000a')||
'                       condition_column_name,'||unistr('\000a')||
'                       cond.condition_operator,'||unistr('\000a')||
'                       cond.condition_expression,'||unistr('\000a')||
'                       cond.condition_expression2,'||unistr('\000a')||
'                       cond.condition_display,'||unistr('\000a')||
'                       r.base_report_id, r.report_id'||unistr('\000a')||
'                  FROM apex_application_page_ir_cond cond JOIN apex_ap';

s:=s||'plication_page_ir_rpt r'||unistr('\000a')||
'                         ON r.application_id = cond.application_id'||unistr('\000a')||
'                        AND r.page_id = cond.page_id'||unistr('\000a')||
'                        AND r.report_id = cond.report_id'||unistr('\000a')||
'                 WHERE cond.application_id = g_apex_ir_info.application_id'||unistr('\000a')||
'                   AND cond.page_id = g_apex_ir_info.page_id'||unistr('\000a')||
'                   AND r.base_report_id = g_apex_ir_info.base_r';

s:=s||'eport_id'||unistr('\000a')||
'                   AND r.session_id = g_apex_ir_info.session_id'||unistr('\000a')||
'                   AND cond.condition_type IN (''Search'', ''Filter'')'||unistr('\000a')||
'                   AND cond.condition_enabled = ''Yes'''||unistr('\000a')||
'                )'||unistr('\000a')||
'    LOOP'||unistr('\000a')||
'      IF rec.condition_type = ''Search'' OR'||unistr('\000a')||
'         (rec.condition_type = ''Filter'' AND rec.condition_column_name IS NULL)'||unistr('\000a')||
'      THEN'||unistr('\000a')||
'        l_condition_display := rec.condition_na';

s:=s||'me;'||unistr('\000a')||
'      ELSE'||unistr('\000a')||
'        -- Filters for removed columns can still exist, skip if column not defined in IR'||unistr('\000a')||
'        IF g_report_cols.EXISTS(rec.condition_column_name) THEN'||unistr('\000a')||
'          l_condition_display := display_for_condition( p_column_name => rec.condition_column_name'||unistr('\000a')||
'                                                      , p_display => rec.condition_display'||unistr('\000a')||
'                                          ';

s:=s||'            , p_operator => rec.condition_operator'||unistr('\000a')||
'                                                      , p_exp_1 => rec.condition_expression'||unistr('\000a')||
'                                                      , p_exp_2 => rec.condition_expression2'||unistr('\000a')||
'                                                      );'||unistr('\000a')||
'        ELSE'||unistr('\000a')||
'          CONTINUE; -- skip filter if column isn''t existing anymore'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'      END ';

s:=s||'IF;'||unistr('\000a')||
'      xlsx_builder_pkg.mergecells( p_tl_col => 1'||unistr('\000a')||
'                                 , p_tl_row => g_current_disp_row'||unistr('\000a')||
'                                 , p_br_col => g_xlsx_options.display_column_count'||unistr('\000a')||
'                                 , p_br_row => g_current_disp_row'||unistr('\000a')||
'                                 , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'      xlsx_builder_pkg.cell( ';

s:=s||'p_col => 1'||unistr('\000a')||
'                           , p_row => g_current_disp_row'||unistr('\000a')||
'                           , p_value => l_condition_display'||unistr('\000a')||
'                           , p_fillId => xlsx_builder_pkg.get_fill( p_patternType => ''solid'''||unistr('\000a')||
'                                                                  , p_fgRGB => ''FFF8DC'''||unistr('\000a')||
'                                                                  )'||unistr('\000a')||
'                       ';

s:=s||'    , p_alignment => xlsx_builder_pkg.get_alignment( p_vertical => ''center'''||unistr('\000a')||
'                                                                          , p_horizontal => ''center'''||unistr('\000a')||
'                                                                          )'||unistr('\000a')||
'                           , p_borderId => xlsx_builder_pkg.get_border(''thin'', ''thin'', ''thin'', ''thin'')'||unistr('\000a')||
'                           , p_sheet => g_xl';

s:=s||'sx_options.sheet );'||unistr('\000a')||
'      fix_borders;'||unistr('\000a')||
'      g_current_disp_row := g_current_disp_row + 1;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'  END print_filter_header;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Prints the header parts.'||unistr('\000a')||
'  * Title, filters and highlights'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_header'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_cur_hl_name VARCHAR2(30);'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    IF g_xlsx_options.show_title THEN'||unistr('\000a')||
'      xlsx_builder_pkg.mergecells( p_tl_col => 1'||unistr('\000a')||
'                                 , p';

s:=s||'_tl_row => g_current_disp_row'||unistr('\000a')||
'                                 , p_br_col => g_xlsx_options.display_column_count'||unistr('\000a')||
'                                 , p_br_row => g_current_disp_row'||unistr('\000a')||
'                                 , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'      xlsx_builder_pkg.cell( p_col => 1'||unistr('\000a')||
'                           , p_row => g_current_disp_row'||unistr('\000a')||
'                     ';

s:=s||'      , p_value => g_apex_ir_info.report_title'||unistr('\000a')||
'                           , p_fontId => xlsx_builder_pkg.get_font( p_name => g_xlsx_options.default_font'||unistr('\000a')||
'                                                                  , p_fontsize => 14'||unistr('\000a')||
'                                                                  , p_bold => TRUE'||unistr('\000a')||
'                                                                  )'||unistr('\000a')||
'           ';

s:=s||'                , p_fillId => xlsx_builder_pkg.get_fill( p_patterntype => ''solid'''||unistr('\000a')||
'                                                                  , p_fgRGB => ''FFF8DC'''||unistr('\000a')||
'                                                                  )'||unistr('\000a')||
'                          , p_alignment => xlsx_builder_pkg.get_alignment( p_vertical => ''center'''||unistr('\000a')||
'                                                                ';

s:=s||'         , p_horizontal => ''center'''||unistr('\000a')||
'                                                                         )'||unistr('\000a')||
'                          , p_borderId => xlsx_builder_pkg.get_border(''thin'', ''thin'', ''thin'', ''thin'')'||unistr('\000a')||
'                          , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                          );'||unistr('\000a')||
'      fix_borders;'||unistr('\000a')||
'      g_current_disp_row := g_current_disp_row + 1;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    IF g_xlsx_o';

s:=s||'ptions.show_filters THEN'||unistr('\000a')||
'      print_filter_header;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    IF g_xlsx_options.show_highlights THEN'||unistr('\000a')||
'      l_cur_hl_name := g_row_highlights.FIRST();'||unistr('\000a')||
'      WHILE (l_cur_hl_name IS NOT NULL) LOOP'||unistr('\000a')||
'        xlsx_builder_pkg.mergecells( p_tl_col => 1'||unistr('\000a')||
'                                   , p_tl_row => g_current_disp_row'||unistr('\000a')||
'                                   , p_br_col => g_xlsx_options.display_column_c';

s:=s||'ount'||unistr('\000a')||
'                                   , p_br_row => g_current_disp_row'||unistr('\000a')||
'                                   , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                   );'||unistr('\000a')||
'        xlsx_builder_pkg.cell( p_col => 1'||unistr('\000a')||
'                             , p_row => g_current_disp_row'||unistr('\000a')||
'                             , p_value => g_row_highlights(l_cur_hl_name).highlight_name'||unistr('\000a')||
'                             ,';

s:=s||' p_fontId => xlsx_builder_pkg.get_font( p_name => g_xlsx_options.default_font'||unistr('\000a')||
'                                                                    , p_rgb => g_row_highlights(l_cur_hl_name).font_color'||unistr('\000a')||
'                                                                    )'||unistr('\000a')||
'                             , p_fillId => xlsx_builder_pkg.get_fill( p_patternType => ''solid'''||unistr('\000a')||
'                                   ';

s:=s||'                                 , p_fgRGB => g_row_highlights(l_cur_hl_name).bg_color'||unistr('\000a')||
'                                                                    )'||unistr('\000a')||
'                             , p_alignment => xlsx_builder_pkg.get_alignment( p_vertical => ''center'''||unistr('\000a')||
'                                                                            , p_horizontal => ''center'''||unistr('\000a')||
'                                       ';

s:=s||'                                     )'||unistr('\000a')||
'                             , p_borderId => xlsx_builder_pkg.get_border(''thin'', ''thin'', ''thin'', ''thin'')'||unistr('\000a')||
'                             , p_sheet => g_xlsx_options.sheet );'||unistr('\000a')||
'        fix_borders;'||unistr('\000a')||
'        g_current_disp_row := g_current_disp_row + 1;'||unistr('\000a')||
'        l_cur_hl_name := g_row_highlights.next(l_cur_hl_name);'||unistr('\000a')||
'      END LOOP;'||unistr('\000a')||
'      l_cur_hl_name := g_col_highlig';

s:=s||'hts.FIRST();'||unistr('\000a')||
'      WHILE (l_cur_hl_name IS NOT NULL) LOOP'||unistr('\000a')||
'        xlsx_builder_pkg.mergecells( p_tl_col => 1'||unistr('\000a')||
'                                   , p_tl_row => g_current_disp_row'||unistr('\000a')||
'                                   , p_br_col => g_xlsx_options.display_column_count'||unistr('\000a')||
'                                   , p_br_row => g_current_disp_row'||unistr('\000a')||
'                                   , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
' ';

s:=s||'                                  );'||unistr('\000a')||
'        xlsx_builder_pkg.cell( p_col => 1'||unistr('\000a')||
'                             , p_row => g_current_disp_row'||unistr('\000a')||
'                             , p_value => g_col_highlights(l_cur_hl_name).highlight_name'||unistr('\000a')||
'                             , p_fontId => xlsx_builder_pkg.get_font( p_name => g_xlsx_options.default_font'||unistr('\000a')||
'                                                                 ';

s:=s||'   , p_rgb => g_col_highlights(l_cur_hl_name).font_color'||unistr('\000a')||
'                                                                    )'||unistr('\000a')||
'                             , p_fillId => xlsx_builder_pkg.get_fill( p_patternType => ''solid'''||unistr('\000a')||
'                                                                    , p_fgRGB => g_col_highlights(l_cur_hl_name).bg_color'||unistr('\000a')||
'                                                        ';

s:=s||'            )'||unistr('\000a')||
'                             , p_alignment => xlsx_builder_pkg.get_alignment( p_vertical => ''center'''||unistr('\000a')||
'                                                                            , p_horizontal => ''center'''||unistr('\000a')||
'                                                                            )'||unistr('\000a')||
'                             , p_borderId => xlsx_builder_pkg.get_border(''thin'', ''thin'', ''thin'', ''thin'')';

s:=s||''||unistr('\000a')||
'                             , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                             );'||unistr('\000a')||
'        fix_borders;'||unistr('\000a')||
'        g_current_disp_row := g_current_disp_row + 1;'||unistr('\000a')||
'        l_cur_hl_name := g_col_highlights.next(l_cur_hl_name);'||unistr('\000a')||
'      END LOOP;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    g_current_disp_row := g_current_disp_row + 1; --add additional row'||unistr('\000a')||
'  END print_header;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Prepare the cursor and pull column i';

s:=s||'nformation from it.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE prepare_cursor'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_desc_tab dbms_sql.desc_tab2;'||unistr('\000a')||
'    l_cur_col_highlight apexir_xlsx_types_pkg.t_apex_ir_highlight;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    IF g_apex_ir_info.view_mode = c_ir_group_by_view THEN'||unistr('\000a')||
'      g_apex_ir_info.final_sql := ''SELECT * FROM ( SELECT '''||unistr('\000a')||
'                                || g_apex_ir_info.group_by_cols'||unistr('\000a')||
'                                || g_apex_ir_info.g';

s:=s||'roup_by_funcs || '' FROM ( '' '||unistr('\000a')||
'                                || g_apex_ir_info.report_definition.sql_query ||'' ) '' '||unistr('\000a')||
'                                || CASE WHEN g_apex_ir_info.group_by_cols IS NOT NULL'||unistr('\000a')||
'                                        THEN ''GROUP BY '' || g_apex_ir_info.group_by_cols'||unistr('\000a')||
'                                   END'||unistr('\000a')||
'                                || '' ) '' || g_apex_ir_info.group_by_so';

s:=s||'rt;'||unistr('\000a')||
'    ELSE'||unistr('\000a')||
'      -- Split sql query on first from and inject highlight conditions'||unistr('\000a')||
'      g_apex_ir_info.final_sql := SUBSTR(g_apex_ir_info.report_definition.sql_query, 1, INSTR(UPPER(g_apex_ir_info.report_definition.sql_query), '' FROM''))'||unistr('\000a')||
'                               || g_apex_ir_info.final_sql'||unistr('\000a')||
'                               || SUBSTR(apex_plugin_util.replace_substitutions(g_apex_ir_info.report_';

s:=s||'definition.sql_query), INSTR(UPPER(g_apex_ir_info.report_definition.sql_query), '' FROM''));'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    g_cursor_info.cursor_id := dbms_sql.open_cursor;'||unistr('\000a')||
'    dbms_sql.parse( g_cursor_info.cursor_id, g_apex_ir_info.final_sql, dbms_sql.NATIVE );'||unistr('\000a')||
'    dbms_sql.describe_columns2( g_cursor_info.cursor_id, g_cursor_info.column_count, l_desc_tab );'||unistr('\000a')||
''||unistr('\000a')||
'    /* Bind values from IR structure*/'||unistr('\000a')||
'    FOR i IN 1.';

s:=s||'.g_apex_ir_info.report_definition.binds.count LOOP'||unistr('\000a')||
'      IF g_apex_ir_info.report_definition.binds(i).NAME = ''REQUEST'' THEN'||unistr('\000a')||
'        dbms_sql.bind_variable( g_cursor_info.cursor_id, g_apex_ir_info.report_definition.binds(i).NAME, g_apex_ir_info.request);'||unistr('\000a')||
'      ELSE'||unistr('\000a')||
'        dbms_sql.bind_variable( g_cursor_info.cursor_id, g_apex_ir_info.report_definition.binds(i).name, g_apex_ir_info.report_definiti';

s:=s||'on.binds(i).value);'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
''||unistr('\000a')||
'    /* Amend column settings*/'||unistr('\000a')||
'    FOR c IN 1 .. g_cursor_info.column_count LOOP'||unistr('\000a')||
'      g_sql_columns(c).col_name := l_desc_tab(c).col_name;'||unistr('\000a')||
'      CASE'||unistr('\000a')||
'        WHEN l_desc_tab( c ).col_type IN ( 2, 100, 101 ) THEN'||unistr('\000a')||
'          dbms_sql.define_array( g_cursor_info.cursor_id, c, g_cursor_info.num_tab, c_bulk_size, 1 );'||unistr('\000a')||
'          g_sql_columns(c).col_data_';

s:=s||'type := c_col_data_type_num;'||unistr('\000a')||
'        WHEN l_desc_tab( c ).col_type IN ( 12, 178, 179, 180, 181 , 231 ) THEN'||unistr('\000a')||
'          dbms_sql.define_array( g_cursor_info.cursor_id, c, g_cursor_info.date_tab, c_bulk_size, 1 );'||unistr('\000a')||
'          g_sql_columns(c).col_data_type := c_col_data_type_date;'||unistr('\000a')||
'        WHEN l_desc_tab( c ).col_type IN ( 1, 8, 9, 96 ) THEN'||unistr('\000a')||
'          dbms_sql.define_array( g_cursor_info.cursor_id, c, ';

s:=s||'g_cursor_info.vc_tab, c_bulk_size, 1 );'||unistr('\000a')||
'          g_sql_columns(c).col_data_type := c_col_data_type_vc;'||unistr('\000a')||
'        WHEN l_desc_tab( c ).col_type = 112 THEN'||unistr('\000a')||
'          dbms_sql.define_array( g_cursor_info.cursor_id, c, g_cursor_info.clob_tab, c_bulk_size, 1 );'||unistr('\000a')||
'          g_sql_columns(c).col_data_type := c_col_data_type_clob;'||unistr('\000a')||
'        ELSE'||unistr('\000a')||
'          NULL;'||unistr('\000a')||
'      END CASE;'||unistr('\000a')||
''||unistr('\000a')||
'      IF g_col_settings.exists(l';

s:=s||'_desc_tab(c).col_name) THEN'||unistr('\000a')||
'        IF g_col_settings(l_desc_tab(c).col_name).is_visible THEN -- remove hidden cols'||unistr('\000a')||
'          g_xlsx_options.display_column_count := g_xlsx_options.display_column_count + 1; -- count number of displayed columns'||unistr('\000a')||
'          g_sql_columns(c).is_displayed := TRUE;'||unistr('\000a')||
'          g_sql_columns(c).col_type := c_display_column;'||unistr('\000a')||
'          g_col_settings(l_desc_tab(c).col_name).sq';

s:=s||'l_col_num := c; -- column in SQL'||unistr('\000a')||
'          g_col_settings(l_desc_tab(c).col_name).display_column := g_xlsx_options.display_column_count; -- column in spreadsheet'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'      ELSIF g_row_highlights.EXISTS(l_desc_tab(c).col_name) THEN'||unistr('\000a')||
'        g_row_highlights(l_desc_tab(c).col_name).col_num := c;'||unistr('\000a')||
'        g_sql_columns(c).col_type := c_row_highlight;'||unistr('\000a')||
'      ELSIF g_col_highlights.EXISTS(l_de';

s:=s||'sc_tab(c).col_name) THEN'||unistr('\000a')||
'        g_col_highlights(l_desc_tab(c).col_name).col_num := c;'||unistr('\000a')||
'        g_sql_columns(c).col_type := c_column_highlight;'||unistr('\000a')||
'        l_cur_col_highlight := g_col_highlights(l_desc_tab(c).col_name);'||unistr('\000a')||
'        g_col_settings(l_cur_col_highlight.affected_column).highlight_conds(l_desc_tab(c).col_name) := l_cur_col_highlight;'||unistr('\000a')||
'      ELSIF l_desc_tab(c).col_name = c_break_definition TH';

s:=s||'EN'||unistr('\000a')||
'        g_sql_columns(c).col_type := c_break_definition;'||unistr('\000a')||
'        g_apex_ir_info.break_def_column := c;'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'  END prepare_cursor;'||unistr('\000a')||
''||unistr('\000a')||
'  /** '||unistr('\000a')||
'  * Prints the column headings.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_column_headers'||unistr('\000a')||
'  AS'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    xlsx_builder_pkg.set_row( p_row => g_current_disp_row'||unistr('\000a')||
'                            , p_alignment => xlsx_builder_pkg.get_alignment( p_vertical => ''cen';

s:=s||'ter'''||unistr('\000a')||
'                                                                           , p_horizontal => ''center'''||unistr('\000a')||
'                                                                           , p_wrapText => NULLIF(g_xlsx_options.allow_wrap_text, TRUE)'||unistr('\000a')||
'                                                                           )'||unistr('\000a')||
'                            , p_fontId => xlsx_builder_pkg.get_font( p_name => g';

s:=s||'_xlsx_options.default_font'||unistr('\000a')||
'                                                                   , p_bold => TRUE'||unistr('\000a')||
'                                                                   )'||unistr('\000a')||
'                            , p_fillId => xlsx_builder_pkg.get_fill( p_patterntype => ''solid'''||unistr('\000a')||
'                                                                   , p_fgRGB => ''FFF8DC'''||unistr('\000a')||
'                                     ';

s:=s||'                              )'||unistr('\000a')||
'                            , p_borderId => xlsx_builder_pkg.get_border(''thin'', ''thin'', ''thin'', ''thin'')'||unistr('\000a')||
'                            , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                            );'||unistr('\000a')||
'    FOR c IN 1..g_cursor_info.column_count LOOP'||unistr('\000a')||
'      IF g_sql_columns(c).is_displayed THEN'||unistr('\000a')||
'        xlsx_builder_pkg.cell( p_col => g_col_settings(g_sql_columns(c).col_nam';

s:=s||'e).display_column'||unistr('\000a')||
'                             , p_row => g_current_disp_row'||unistr('\000a')||
'                             , p_value => REPLACE(g_col_settings(g_sql_columns(c).col_name).report_label, g_xlsx_options.original_line_break, g_xlsx_options.replace_line_break)'||unistr('\000a')||
'                             , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                             );'||unistr('\000a')||
'        IF g_xlsx_options.col_hdr_help THEN'||unistr('\000a')||
'       ';

s:=s||'   IF g_col_settings(g_sql_columns(c).col_name).help_text IS NOT NULL THEN'||unistr('\000a')||
'            xlsx_builder_pkg.COMMENT( p_col => g_col_settings(g_sql_columns(c).col_name).display_column'||unistr('\000a')||
'                                    , p_row => g_current_disp_row'||unistr('\000a')||
'                                    , p_text => REPLACE(g_col_settings(g_sql_columns(c).col_name).help_text, g_xlsx_options.original_line_break, g_xlsx_opt';

s:=s||'ions.replace_line_break)'||unistr('\000a')||
'                                    , p_author => ''Help'''||unistr('\000a')||
'                                    , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                    );'||unistr('\000a')||
'          END IF;'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    g_current_disp_row := g_current_disp_row + 1;'||unistr('\000a')||
'  END print_column_headers;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Checks column highlights for specified column and marks th';

s:=s||'em active if condition is met.'||unistr('\000a')||
'  * @param p_column_name     The current column to check'||unistr('\000a')||
'  * @param p_fetched_row_cnt Amount of rows fetched'||unistr('\000a')||
'  */'||unistr('\000a')||
'  FUNCTION process_col_highlights ( p_column_name IN VARCHAR2'||unistr('\000a')||
'                                  , p_fetched_row_cnt IN PLS_INTEGER'||unistr('\000a')||
'                                  )'||unistr('\000a')||
'    RETURN apexir_xlsx_types_pkg.t_apex_ir_active_hl'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_cur_hl_name VARCHAR2(30)';

s:=s||';'||unistr('\000a')||
'    l_cur_col_highlight apexir_xlsx_types_pkg.t_apex_ir_highlight;'||unistr('\000a')||
'    retval apexir_xlsx_types_pkg.t_apex_ir_active_hl;'||unistr('\000a')||
'    l_col_hl_value dbms_sql.number_table;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_cur_hl_name := g_col_settings(p_column_name).highlight_conds.FIRST;'||unistr('\000a')||
'    WHILE (l_cur_hl_name IS NOT NULL) LOOP'||unistr('\000a')||
'      l_cur_col_highlight := g_col_settings(p_column_name).highlight_conds(l_cur_hl_name);'||unistr('\000a')||
'      dbms_sql.COLU';

s:=s||'MN_VALUE( g_cursor_info.cursor_id, l_cur_col_highlight.col_num, l_col_hl_value);'||unistr('\000a')||
'      FOR i IN 0 .. p_fetched_row_cnt - 1 LOOP'||unistr('\000a')||
'        -- highlight condition TRUE'||unistr('\000a')||
'        IF l_col_hl_value(i + l_col_hl_value.FIRST()) IS NOT NULL THEN'||unistr('\000a')||
'          -- no previous highlight condition matched'||unistr('\000a')||
'          IF NOT retval.EXISTS(i) THEN'||unistr('\000a')||
'            retval(i) := l_cur_col_highlight;'||unistr('\000a')||
'          END IF;'||unistr('\000a')||
'        E';

s:=s||'ND IF;'||unistr('\000a')||
'      END LOOP;'||unistr('\000a')||
'      l_col_hl_value.DELETE;'||unistr('\000a')||
'      l_cur_hl_name := g_col_settings(p_column_name).highlight_conds.next(l_cur_hl_name);'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    RETURN retval;'||unistr('\000a')||
'  END process_col_highlights;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Prints the defined aggregates for a column'||unistr('\000a')||
'  * @param p_column_name     The current column to run the aggregates'||unistr('\000a')||
'  * @param p_fetched_row_cnt Amount of rows fetched  '||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE ';

s:=s||'print_aggregates ( p_column_name IN VARCHAR2'||unistr('\000a')||
'                             , p_fetched_row_cnt IN PLS_INTEGER'||unistr('\000a')||
'                             )'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_aggregate_values dbms_sql.number_table;'||unistr('\000a')||
'    l_cur_aggregate_name VARCHAR2(30);'||unistr('\000a')||
'    l_aggregate_offset PLS_INTEGER := 1;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    IF NOT ( g_col_settings(p_column_name).is_break_col'||unistr('\000a')||
'          OR g_col_settings(p_column_name).display_column = g_ape';

s:=s||'x_ir_info.aggregate_type_disp_column'||unistr('\000a')||
'           )'||unistr('\000a')||
'      THEN -- exclude break cols'||unistr('\000a')||
'      -- fixed order for aggregates, same as occurence in t_apexir_col type'||unistr('\000a')||
'      l_cur_aggregate_name := g_apex_ir_info.active_aggregates.FIRST();'||unistr('\000a')||
'      WHILE (l_cur_aggregate_name IS NOT NULL) LOOP'||unistr('\000a')||
'        IF g_col_settings(p_column_name).aggregates.EXISTS(l_cur_aggregate_name) THEN'||unistr('\000a')||
'          dbms_sql.COLUMN_VALUE';

s:=s||'( g_cursor_info.cursor_id'||unistr('\000a')||
'                               , g_col_settings(p_column_name).aggregates(l_cur_aggregate_name).col_num'||unistr('\000a')||
'                               , l_aggregate_values'||unistr('\000a')||
'                               );'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'        FOR i IN 0 .. p_fetched_row_cnt - 1 loop'||unistr('\000a')||
'          IF ( g_apex_ir_info.aggregate_type_disp_column > 1'||unistr('\000a')||
'           AND g_cursor_info.break_rows(g_current_sql_row ';

s:=s||'+ i + 1) != g_cursor_info.break_rows(g_current_sql_row + i)'||unistr('\000a')||
'             )'||unistr('\000a')||
'          OR ( g_apex_ir_info.aggregate_type_disp_column = 1'||unistr('\000a')||
'           AND p_fetched_row_cnt <= c_bulk_size'||unistr('\000a')||
'           AND i = p_fetched_row_cnt - 1'||unistr('\000a')||
'             ) --last row'||unistr('\000a')||
'          THEN'||unistr('\000a')||
'            xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).display_column'||unistr('\000a')||
'                                 , p_row => g';

s:=s||'_current_disp_row + i + g_cursor_info.break_rows(g_current_sql_row + i) + l_aggregate_offset'||unistr('\000a')||
'                                 , p_value => CASE'||unistr('\000a')||
'                                                WHEN NOT g_col_settings(p_column_name).aggregates.EXISTS(l_cur_aggregate_name)'||unistr('\000a')||
'                                                  THEN TO_NUMBER(NULL)'||unistr('\000a')||
'                                                WHEN i = 0';

s:=s||' AND g_current_sql_row > 1'||unistr('\000a')||
'                                                  THEN g_col_settings(p_column_name).aggregates(l_cur_aggregate_name).last_value'||unistr('\000a')||
'                                                ELSE l_aggregate_values( i + l_aggregate_values.FIRST() )'||unistr('\000a')||
'                                              END'||unistr('\000a')||
'                                 , p_numFmtId => xlsx_builder_pkg.get_numFmt(xlsx_builde';

s:=s||'r_pkg.OraNumFmt2Excel(g_col_settings(p_column_name).format_mask))'||unistr('\000a')||
'                                 , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'          END IF;'||unistr('\000a')||
'          IF g_col_settings(p_column_name).aggregates.EXISTS(l_cur_aggregate_name) THEN'||unistr('\000a')||
'            g_col_settings(p_column_name).aggregates(l_cur_aggregate_name).last_value := l_aggregate_values( i + l_aggregate_';

s:=s||'values.FIRST() );'||unistr('\000a')||
'          END IF;'||unistr('\000a')||
'        END LOOP row_loop;'||unistr('\000a')||
'        l_aggregate_offset := l_aggregate_offset + 1;'||unistr('\000a')||
'        l_cur_aggregate_name := g_apex_ir_info.active_aggregates.NEXT(l_cur_aggregate_name);'||unistr('\000a')||
'        l_aggregate_values.DELETE;'||unistr('\000a')||
'      END LOOP aggregate_loop;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'  END print_aggregates;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Print column of type NUMBER.'||unistr('\000a')||
'  * @param p_column_name       Current column'||unistr('\000a')||
'  ';

s:=s||'* @param p_fetched_row_cnt   Amount of rows fetched'||unistr('\000a')||
'  * @param p_active_highlights Array of column highlights where condition was met.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_num_column ( p_column_name IN VARCHAR2'||unistr('\000a')||
'                             , p_fetched_row_cnt IN PLS_INTEGER'||unistr('\000a')||
'                             , p_active_highlights IN apexir_xlsx_types_pkg.t_apex_ir_active_hl'||unistr('\000a')||
'                             )'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_c';

s:=s||'ol_values dbms_sql.number_table;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    dbms_sql.COLUMN_VALUE( g_cursor_info.cursor_id, g_col_settings(p_column_name).sql_col_num, l_col_values );'||unistr('\000a')||
'    FOR i IN 0 .. p_fetched_row_cnt - 1 loop'||unistr('\000a')||
'      xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).display_column'||unistr('\000a')||
'                           , p_row => g_current_disp_row + i + g_cursor_info.break_rows(g_current_sql_row + i)'||unistr('\000a')||
'         ';

s:=s||'                  , p_value => l_col_values( i + l_col_values.FIRST() )'||unistr('\000a')||
'                           , p_numFmtId => xlsx_builder_pkg.get_numFmt(xlsx_builder_pkg.OraNumFmt2Excel(g_col_settings(p_column_name).format_mask))'||unistr('\000a')||
'                           , p_fontId => CASE'||unistr('\000a')||
'                                           WHEN p_active_highlights.EXISTS(i) AND p_active_highlights(i).font_color IS NOT NULL THEN'||unistr('\000a')||
' ';

s:=s||'                                            xlsx_builder_pkg.get_font( p_name => g_xlsx_options.default_font'||unistr('\000a')||
'                                                                      , p_rgb => p_active_highlights(i).font_color'||unistr('\000a')||
'                                                                      )'||unistr('\000a')||
'                                           ELSE NULL'||unistr('\000a')||
'                                         END'||unistr('\000a')||
'      ';

s:=s||'                     , p_fillId => CASE'||unistr('\000a')||
'                                           WHEN p_active_highlights.EXISTS(i) AND p_active_highlights(i).bg_color IS NOT NULL THEN'||unistr('\000a')||
'                                             xlsx_builder_pkg.get_fill( p_patternType => ''solid'''||unistr('\000a')||
'                                                                      , p_fgRGB => p_active_highlights(i).bg_color'||unistr('\000a')||
'                 ';

s:=s||'                                                     )'||unistr('\000a')||
'                                           ELSE NULL'||unistr('\000a')||
'                                         END'||unistr('\000a')||
'                           , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                           );'||unistr('\000a')||
'      IF g_xlsx_options.show_aggregates AND g_apex_ir_info.aggregate_type_disp_column > g_col_settings(p_column_name).display_column THEN'||unistr('\000a')||
'        IF (i = p_';

s:=s||'fetched_row_cnt AND p_fetched_row_cnt < c_bulk_size) '||unistr('\000a')||
'         OR g_cursor_info.break_rows(g_current_sql_row + i + 1) != g_cursor_info.break_rows(g_current_sql_row + i)'||unistr('\000a')||
'        THEN'||unistr('\000a')||
'          FOR j IN 1..g_apex_ir_info.active_aggregates.count LOOP'||unistr('\000a')||
'            xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).display_column'||unistr('\000a')||
'                                 , p_row => g_current_disp_row ';

s:=s||'+ i + g_cursor_info.break_rows(g_current_sql_row + i) + j'||unistr('\000a')||
'                                 , p_value => CASE'||unistr('\000a')||
'                                                WHEN g_col_settings(p_column_name).is_break_col'||unistr('\000a')||
'                                                  THEN l_col_values( i + l_col_values.FIRST() )'||unistr('\000a')||
'                                                ELSE NULL'||unistr('\000a')||
'                                         ';

s:=s||'     END'||unistr('\000a')||
'                                 , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'          END LOOP;'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END loop;'||unistr('\000a')||
'    IF g_xlsx_options.show_aggregates THEN'||unistr('\000a')||
'      print_aggregates(p_column_name, p_fetched_row_cnt);'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    l_col_values.DELETE;'||unistr('\000a')||
'  END print_num_column;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Prints a column of data type DATE.'||unistr('\000a')||
'  * @param p_col';

s:=s||'umn_name       Current column'||unistr('\000a')||
'  * @param p_fetched_row_cnt   Amount of rows fetched'||unistr('\000a')||
'  * @param p_active_highlights Array of column highlights where condition was met  '||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_date_column ( p_column_name IN VARCHAR2'||unistr('\000a')||
'                              , p_fetched_row_cnt IN PLS_INTEGER'||unistr('\000a')||
'                              , p_active_highlights IN apexir_xlsx_types_pkg.t_apex_ir_active_hl'||unistr('\000a')||
'       ';

s:=s||'                       )'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_col_values dbms_sql.date_table;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    dbms_sql.COLUMN_VALUE( g_cursor_info.cursor_id, g_col_settings(p_column_name).sql_col_num, l_col_values );'||unistr('\000a')||
'    FOR i IN 0 .. p_fetched_row_cnt - 1 loop'||unistr('\000a')||
'      xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).display_column'||unistr('\000a')||
'                           , p_row => g_current_disp_row + i + g_cursor_info.break_ro';

s:=s||'ws(g_current_sql_row + i)'||unistr('\000a')||
'                           , p_value => l_col_values( i + l_col_values.FIRST() )'||unistr('\000a')||
'                           , p_numFmtId => xlsx_builder_pkg.get_numFmt(xlsx_builder_pkg.OraFmt2Excel(COALESCE(g_col_settings(p_column_name).format_mask, g_xlsx_options.default_date_format)))'||unistr('\000a')||
'                           , p_fontId => CASE'||unistr('\000a')||
'                                           WHEN p_active';

s:=s||'_highlights.EXISTS(i) AND p_active_highlights(i).font_color IS NOT NULL THEN'||unistr('\000a')||
'                                             xlsx_builder_pkg.get_font( p_name => g_xlsx_options.default_font'||unistr('\000a')||
'                                                                      , p_rgb => p_active_highlights(i).font_color'||unistr('\000a')||
'                                                                      )'||unistr('\000a')||
'                          ';

s:=s||'                 ELSE NULL'||unistr('\000a')||
'                                         END'||unistr('\000a')||
'                           , p_fillId => CASE'||unistr('\000a')||
'                                           WHEN p_active_highlights.EXISTS(i) AND p_active_highlights(i).bg_color IS NOT NULL THEN'||unistr('\000a')||
'                                             xlsx_builder_pkg.get_fill( p_patternType => ''solid'''||unistr('\000a')||
'                                                      ';

s:=s||'                , p_fgRGB => p_active_highlights(i).bg_color'||unistr('\000a')||
'                                                                      )'||unistr('\000a')||
'                                           ELSE NULL'||unistr('\000a')||
'                                         END'||unistr('\000a')||
'                           , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                           );'||unistr('\000a')||
'      IF g_xlsx_options.show_aggregates AND g_apex_ir_info.aggregate_type_disp';

s:=s||'_column > g_col_settings(p_column_name).display_column THEN'||unistr('\000a')||
'        IF (i = p_fetched_row_cnt AND p_fetched_row_cnt < c_bulk_size) '||unistr('\000a')||
'         OR g_cursor_info.break_rows(g_current_sql_row + i + 1) != g_cursor_info.break_rows(g_current_sql_row + i)'||unistr('\000a')||
'        THEN'||unistr('\000a')||
'          FOR j IN 1..g_apex_ir_info.active_aggregates.count LOOP'||unistr('\000a')||
'            xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).';

s:=s||'display_column'||unistr('\000a')||
'                                 , p_row => g_current_disp_row + i + g_cursor_info.break_rows(g_current_sql_row + i) + j'||unistr('\000a')||
'                                 , p_value => CASE'||unistr('\000a')||
'                                                WHEN g_col_settings(p_column_name).is_break_col'||unistr('\000a')||
'                                                  THEN l_col_values( i + l_col_values.FIRST() )'||unistr('\000a')||
'                     ';

s:=s||'                           ELSE NULL'||unistr('\000a')||
'                                              END'||unistr('\000a')||
'                                 , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'          END LOOP;'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    IF g_xlsx_options.show_aggregates THEN'||unistr('\000a')||
'      print_aggregates(p_column_name, p_fetched_row_cnt);'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    l_col_values.DELETE;'||unistr('\000a')||
'  END pr';

s:=s||'int_date_column;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Prints a column of data type VARCHAR.'||unistr('\000a')||
'  * @param p_column_name       Current column'||unistr('\000a')||
'  * @param p_fetched_row_cnt   Amount of rows fetched'||unistr('\000a')||
'  * @param p_active_highlights Array of column highlights where condition was met  '||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_vc_column ( p_column_name IN VARCHAR2'||unistr('\000a')||
'                            , p_fetched_row_cnt IN PLS_INTEGER'||unistr('\000a')||
'                          ';

s:=s||'  , p_active_highlights IN apexir_xlsx_types_pkg.t_apex_ir_active_hl'||unistr('\000a')||
'                            )'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_col_values dbms_sql.varchar2_table;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    dbms_sql.COLUMN_VALUE( g_cursor_info.cursor_id, g_col_settings(p_column_name).sql_col_num, l_col_values );'||unistr('\000a')||
'    FOR i IN 0 .. p_fetched_row_cnt - 1 loop'||unistr('\000a')||
'      xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).display_column'||unistr('\000a')||
'       ';

wwv_flow_api.append_to_install_script(
  p_id => 12971322408117110 + wwv_flow_api.g_id_offset,
  p_flow_id => wwv_flow.g_flow_id,
  p_script_clob => s);
end;
 
 
end;
/

 
begin
 
declare
    s varchar2(32767) := null;
begin
s:=s||'                    , p_row => g_current_disp_row + i + g_cursor_info.break_rows(g_current_sql_row + i)'||unistr('\000a')||
'                           , p_value => REPLACE(l_col_values(i + l_col_values.FIRST()), g_xlsx_options.original_line_break, g_xlsx_options.replace_line_break)'||unistr('\000a')||
'                           , p_alignment => CASE WHEN g_xlsx_options.allow_wrap_text THEN NULL ELSE xlsx_builder_pkg.get_alignment(p_wrap';

s:=s||'Text => FALSE) END'||unistr('\000a')||
'                           , p_fontId => CASE'||unistr('\000a')||
'                                           WHEN p_active_highlights.EXISTS(i) AND p_active_highlights(i).font_color IS NOT NULL THEN'||unistr('\000a')||
'                                             xlsx_builder_pkg.get_font( p_name => g_xlsx_options.default_font'||unistr('\000a')||
'                                                                      , p_rgb => p_active_hi';

s:=s||'ghlights(i).font_color'||unistr('\000a')||
'                                                                      )'||unistr('\000a')||
'                                           ELSE NULL'||unistr('\000a')||
'                                         END'||unistr('\000a')||
'                           , p_fillId => CASE'||unistr('\000a')||
'                                           WHEN p_active_highlights.EXISTS(i) AND p_active_highlights(i).bg_color IS NOT NULL THEN'||unistr('\000a')||
'                              ';

s:=s||'               xlsx_builder_pkg.get_fill( p_patternType => ''solid'''||unistr('\000a')||
'                                                                      , p_fgRGB => p_active_highlights(i).bg_color'||unistr('\000a')||
'                                                                      )'||unistr('\000a')||
'                                           ELSE NULL'||unistr('\000a')||
'                                         END'||unistr('\000a')||
'                           , p_sheet => g_xlsx_o';

s:=s||'ptions.sheet'||unistr('\000a')||
'                           );'||unistr('\000a')||
'      IF g_xlsx_options.show_aggregates AND g_apex_ir_info.aggregate_type_disp_column > g_col_settings(p_column_name).display_column THEN'||unistr('\000a')||
'        IF (i = p_fetched_row_cnt AND p_fetched_row_cnt < c_bulk_size) '||unistr('\000a')||
'         OR g_cursor_info.break_rows(g_current_sql_row + i + 1) != g_cursor_info.break_rows(g_current_sql_row + i)'||unistr('\000a')||
'        THEN'||unistr('\000a')||
'          FOR j IN ';

s:=s||'1..g_apex_ir_info.active_aggregates.count LOOP'||unistr('\000a')||
'            xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).display_column'||unistr('\000a')||
'                                 , p_row => g_current_disp_row + i + g_cursor_info.break_rows(g_current_sql_row + i) + j'||unistr('\000a')||
'                                 , p_value => CASE'||unistr('\000a')||
'                                                WHEN g_col_settings(p_column_name).is_break_';

s:=s||'col'||unistr('\000a')||
'                                                  THEN REPLACE(l_col_values(i + l_col_values.FIRST()), g_xlsx_options.original_line_break, g_xlsx_options.replace_line_break)'||unistr('\000a')||
'                                               ELSE NULL'||unistr('\000a')||
'                                             END'||unistr('\000a')||
'                                 , p_alignment => CASE WHEN g_xlsx_options.allow_wrap_text THEN NULL ELSE xlsx_build';

s:=s||'er_pkg.get_alignment(p_wrapText => FALSE) END'||unistr('\000a')||
'                                 , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'          END LOOP;'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    IF g_xlsx_options.show_aggregates THEN'||unistr('\000a')||
'      print_aggregates(p_column_name, p_fetched_row_cnt);'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    l_col_values.DELETE;'||unistr('\000a')||
'  END print_vc_column;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Prints a column';

s:=s||' of data type CLOB.'||unistr('\000a')||
'  * Value is converted to VARCHAR2, therefore standard limitation of VARCAHR2 applies.'||unistr('\000a')||
'  * If CLOB is too long value is simply truncated.'||unistr('\000a')||
'  * @param p_column_name       Current column'||unistr('\000a')||
'  * @param p_fetched_row_cnt   Amount of rows fetched'||unistr('\000a')||
'  * @param p_active_highlights Array of column highlights where condition was met  '||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_clob_column ( p_column_name IN VARC';

s:=s||'HAR2'||unistr('\000a')||
'                              , p_fetched_row_cnt IN PLS_INTEGER'||unistr('\000a')||
'                              , p_active_highlights IN apexir_xlsx_types_pkg.t_apex_ir_active_hl'||unistr('\000a')||
'                              )'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_col_values dbms_sql.clob_table;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    dbms_sql.COLUMN_VALUE( g_cursor_info.cursor_id, g_col_settings(p_column_name).sql_col_num, l_col_values );'||unistr('\000a')||
'    FOR i IN 0 .. p_fetched_row_cnt - 1';

s:=s||' loop'||unistr('\000a')||
'      xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).display_column'||unistr('\000a')||
'                           , p_row => g_current_disp_row + i + g_cursor_info.break_rows(g_current_sql_row + i)'||unistr('\000a')||
'                           , p_value => REPLACE(dbms_lob.substr(l_col_values(i + l_col_values.FIRST())), g_xlsx_options.original_line_break, g_xlsx_options.replace_line_break)'||unistr('\000a')||
'                        ';

s:=s||'   , p_alignment => CASE WHEN g_xlsx_options.allow_wrap_text THEN NULL ELSE xlsx_builder_pkg.get_alignment(p_wrapText => FALSE) END'||unistr('\000a')||
'                           , p_fontId => CASE'||unistr('\000a')||
'                                           WHEN p_active_highlights.EXISTS(i) AND p_active_highlights(i).font_color IS NOT NULL THEN'||unistr('\000a')||
'                                             xlsx_builder_pkg.get_font( p_name => g_xlsx_';

s:=s||'options.default_font'||unistr('\000a')||
'                                                                      , p_rgb => p_active_highlights(i).font_color'||unistr('\000a')||
'                                                                      )'||unistr('\000a')||
'                                           ELSE NULL'||unistr('\000a')||
'                                         END'||unistr('\000a')||
'                           , p_fillId => CASE'||unistr('\000a')||
'                                           WHEN ';

s:=s||'p_active_highlights.EXISTS(i) AND p_active_highlights(i).bg_color IS NOT NULL THEN'||unistr('\000a')||
'                                             xlsx_builder_pkg.get_fill( p_patternType => ''solid'''||unistr('\000a')||
'                                                                      , p_fgRGB => p_active_highlights(i).bg_color'||unistr('\000a')||
'                                                                      )'||unistr('\000a')||
'                                 ';

s:=s||'          ELSE NULL'||unistr('\000a')||
'                                         END'||unistr('\000a')||
'                           , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                           );'||unistr('\000a')||
'      IF g_xlsx_options.show_aggregates AND g_apex_ir_info.aggregate_type_disp_column != g_col_settings(p_column_name).display_column THEN'||unistr('\000a')||
'        IF (i = p_fetched_row_cnt AND p_fetched_row_cnt < c_bulk_size) '||unistr('\000a')||
'         OR g_cursor_info.break_r';

s:=s||'ows(g_current_sql_row + i + 1) != g_cursor_info.break_rows(g_current_sql_row + i)'||unistr('\000a')||
'        THEN'||unistr('\000a')||
'          FOR j IN 1..g_apex_ir_info.active_aggregates.count LOOP'||unistr('\000a')||
'            xlsx_builder_pkg.cell( p_col => g_col_settings(p_column_name).display_column'||unistr('\000a')||
'                                 , p_row => g_current_disp_row + i + g_cursor_info.break_rows(g_current_sql_row + i) + j'||unistr('\000a')||
'                             ';

s:=s||'    , p_value => CASE'||unistr('\000a')||
'                                                WHEN g_col_settings(p_column_name).is_break_col'||unistr('\000a')||
'                                                  THEN REPLACE(dbms_lob.substr(l_col_values(i + l_col_values.FIRST())), g_xlsx_options.original_line_break, g_xlsx_options.replace_line_break)'||unistr('\000a')||
'                                               ELSE NULL'||unistr('\000a')||
'                                  ';

s:=s||'           END'||unistr('\000a')||
'                                 , p_alignment => CASE WHEN g_xlsx_options.allow_wrap_text THEN NULL ELSE xlsx_builder_pkg.get_alignment(p_wrapText => FALSE) END'||unistr('\000a')||
'                                 , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'          END LOOP;'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    IF g_xlsx_options.show_aggregates THEN'||unistr('\000a')||
'      print_a';

s:=s||'ggregates(p_column_name, p_fetched_row_cnt);'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    l_col_values.DELETE;'||unistr('\000a')||
'  END print_clob_column;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Prints the used aggregate types given a control break.'||unistr('\000a')||
'  * @param p_row_offset Additional row offset to place aggregate types'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_aggregate_types (p_row_offset IN PLS_INTEGER)'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_cur_aggregate_type VARCHAR2(30);'||unistr('\000a')||
'    l_cnt PLS_INTEGER := 0;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_c';

s:=s||'ur_aggregate_type := g_apex_ir_info.active_aggregates.FIRST();'||unistr('\000a')||
'    WHILE (l_cur_aggregate_type IS NOT NULL) LOOP'||unistr('\000a')||
'      xlsx_builder_pkg.set_row( p_row => g_current_disp_row + p_row_offset + l_cnt'||unistr('\000a')||
'                              , p_fontId => xlsx_builder_pkg.get_font( p_name => g_xlsx_options.default_font'||unistr('\000a')||
'                                                                     , p_bold => TRUE'||unistr('\000a')||
'         ';

s:=s||'                                                            )'||unistr('\000a')||
'                              , p_fillId => xlsx_builder_pkg.get_fill( p_patternType => ''solid'''||unistr('\000a')||
'                                                                     , p_fgRGB => ''FFF8DC'''||unistr('\000a')||
'                                                                     )'||unistr('\000a')||
'                              , p_alignment => xlsx_builder_pkg.get_alignment( p';

s:=s||'_wrapText => NULLIF(g_xlsx_options.allow_wrap_text, TRUE) )'||unistr('\000a')||
'                              , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                              );'||unistr('\000a')||
''||unistr('\000a')||
'      xlsx_builder_pkg.cell( p_col => g_apex_ir_info.aggregate_type_disp_column'||unistr('\000a')||
'                           , p_row => g_current_disp_row + p_row_offset + l_cnt'||unistr('\000a')||
'                           , p_value => l_cur_aggregate_type'||unistr('\000a')||
'                     ';

s:=s||'      , p_sheet => g_xlsx_options.sheet'||unistr('\000a')||
'                           );'||unistr('\000a')||
'      l_cnt := l_cnt + 1;'||unistr('\000a')||
'      l_cur_aggregate_type := g_apex_ir_info.active_aggregates.next(l_cur_aggregate_type);'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'  END print_aggregate_types;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Checks for any control breaks in current fetch and populates global variable.'||unistr('\000a')||
'  * @param p_fetched_row_cnt   Amount of rows fetched'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE process_br';

s:=s||'eak_rows (p_fetched_row_cnt IN PLS_INTEGER)'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_cnt NUMBER := 0;'||unistr('\000a')||
'    l_break_values dbms_sql.varchar2_table;'||unistr('\000a')||
'    l_cur_break_val VARCHAR2(32767);'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    IF NOT g_cursor_info.break_rows.EXISTS(g_current_sql_row) THEN'||unistr('\000a')||
'      g_cursor_info.break_rows(g_current_sql_row) := 0;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    IF g_xlsx_options.show_aggregates AND g_apex_ir_info.break_def_column IS NOT NULL THEN'||unistr('\000a')||
'      DBMS_SQ';

s:=s||'L.COLUMN_VALUE( g_cursor_info.cursor_id, g_apex_ir_info.break_def_column, l_break_values);'||unistr('\000a')||
'      FOR i IN 0..p_fetched_row_cnt - 1 LOOP'||unistr('\000a')||
'        l_cur_break_val := l_break_values(i + l_break_values.FIRST());'||unistr('\000a')||
'        IF l_cur_break_val != g_cursor_info.prev_break_val THEN'||unistr('\000a')||
'          print_aggregate_types(i + l_cnt);'||unistr('\000a')||
'          l_cnt := l_cnt + g_apex_ir_info.active_aggregates.count;'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'  ';

s:=s||'      g_cursor_info.prev_break_val := l_break_values(i + l_break_values.FIRST());'||unistr('\000a')||
'        g_cursor_info.break_rows(g_current_sql_row + i) := l_cnt;'||unistr('\000a')||
'      END LOOP;'||unistr('\000a')||
'      IF p_fetched_row_cnt < c_bulk_size THEN'||unistr('\000a')||
'        g_cursor_info.break_rows(g_current_sql_row + p_fetched_row_cnt) := l_cnt + g_apex_ir_info.active_aggregates.count;'||unistr('\000a')||
'        print_aggregate_types(p_fetched_row_cnt + l_cnt);'||unistr('\000a')||
'      ELS';

s:=s||'E'||unistr('\000a')||
'        g_cursor_info.break_rows(g_current_sql_row + p_fetched_row_cnt) := l_cnt + 1;'||unistr('\000a')||
'      END IF;      '||unistr('\000a')||
'      l_break_values.DELETE;'||unistr('\000a')||
'    ELSE'||unistr('\000a')||
'      FOR i IN 1..p_fetched_row_cnt + 1 LOOP'||unistr('\000a')||
'        g_cursor_info.break_rows(g_current_sql_row + i) := l_cnt;'||unistr('\000a')||
'      END LOOP;'||unistr('\000a')||
'      IF p_fetched_row_cnt < c_bulk_size AND g_xlsx_options.show_aggregates THEN'||unistr('\000a')||
'        print_aggregate_types(p_fetched_row_cn';

s:=s||'t + l_cnt);'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'  END process_break_rows;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Wrapper for all procedures printing data.'||unistr('\000a')||
'  * Calls the building blocks in the right order and provides the overall control structure.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  PROCEDURE print_data'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_cur_col_name VARCHAR2(4000);'||unistr('\000a')||
'    l_fetched_row_cnt PLS_INTEGER;'||unistr('\000a')||
'    l_active_col_highlights apexir_xlsx_types_pkg.t_apex_ir_active_hl;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_fe';

s:=s||'tched_row_cnt := dbms_sql.execute( g_cursor_info.cursor_id );'||unistr('\000a')||
'    LOOP'||unistr('\000a')||
'      l_fetched_row_cnt := dbms_sql.fetch_rows( g_cursor_info.cursor_id );'||unistr('\000a')||
'      IF l_fetched_row_cnt > 0 THEN'||unistr('\000a')||
'        -- new calculation for every bulk set'||unistr('\000a')||
'        process_break_rows( p_fetched_row_cnt => l_fetched_row_cnt );'||unistr('\000a')||
'        process_row_highlights( p_fetched_row_cnt => l_fetched_row_cnt );'||unistr('\000a')||
'        FOR c IN 1..g_cursor';

s:=s||'_info.column_count LOOP'||unistr('\000a')||
'          IF g_sql_columns(c).is_displayed THEN'||unistr('\000a')||
'            -- next display column, empty active highlights'||unistr('\000a')||
'            l_active_col_highlights.DELETE;'||unistr('\000a')||
'            -- check if highlight processing is enabled and column has highlights attached'||unistr('\000a')||
'            IF g_xlsx_options.process_highlights AND'||unistr('\000a')||
'               g_col_settings(g_sql_columns(c).col_name).highlight_conds.count()';

s:=s||' > 0'||unistr('\000a')||
'            THEN'||unistr('\000a')||
'              l_active_col_highlights := process_col_highlights( p_column_name => g_sql_columns(c).col_name'||unistr('\000a')||
'                                                               , p_fetched_row_cnt => l_fetched_row_cnt'||unistr('\000a')||
'                                                               );'||unistr('\000a')||
'            END IF;'||unistr('\000a')||
''||unistr('\000a')||
'            -- now create the cells'||unistr('\000a')||
'            CASE'||unistr('\000a')||
'              WHEN g_sql_c';

s:=s||'olumns(c).col_data_type = c_col_data_type_num THEN'||unistr('\000a')||
'                print_num_column( p_column_name => g_sql_columns(c).col_name'||unistr('\000a')||
'                                , p_fetched_row_cnt => l_fetched_row_cnt'||unistr('\000a')||
'                                , p_active_highlights => l_active_col_highlights'||unistr('\000a')||
'                                );'||unistr('\000a')||
'              WHEN g_sql_columns(c).col_data_type = c_col_data_type_date THEN'||unistr('\000a')||
'     ';

s:=s||'           print_date_column( p_column_name => g_sql_columns(c).col_name'||unistr('\000a')||
'                                 , p_fetched_row_cnt => l_fetched_row_cnt'||unistr('\000a')||
'                                 , p_active_highlights => l_active_col_highlights'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'              WHEN g_sql_columns(c).col_data_type = c_col_data_type_vc THEN'||unistr('\000a')||
'                print_vc_column( p_column_name => g_sql_col';

s:=s||'umns(c).col_name'||unistr('\000a')||
'                               , p_fetched_row_cnt => l_fetched_row_cnt'||unistr('\000a')||
'                               , p_active_highlights => l_active_col_highlights'||unistr('\000a')||
'                               );'||unistr('\000a')||
'              WHEN g_sql_columns(c).col_data_type = c_col_data_type_clob THEN'||unistr('\000a')||
'                print_clob_column( p_column_name => g_sql_columns(c).col_name'||unistr('\000a')||
'                                 , p_fetc';

s:=s||'hed_row_cnt => l_fetched_row_cnt'||unistr('\000a')||
'                                 , p_active_highlights => l_active_col_highlights'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'              ELSE NULL; -- unsupported data type'||unistr('\000a')||
'            END CASE;'||unistr('\000a')||
'          END IF;'||unistr('\000a')||
'        END LOOP;'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'      EXIT WHEN l_fetched_row_cnt != c_bulk_size;'||unistr('\000a')||
'      g_current_sql_row := g_current_sql_row + l_fetched_row_cnt;'||unistr('\000a')||
'      g_cur';

s:=s||'rent_disp_row := g_current_disp_row + l_fetched_row_cnt + g_cursor_info.break_rows(g_current_sql_row - 1);'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    dbms_sql.close_cursor( g_cursor_info.cursor_id );'||unistr('\000a')||
'  END print_data;'||unistr('\000a')||
''||unistr('\000a')||
'  FUNCTION get_report_id'||unistr('\000a')||
'  RETURN NUMBER'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_region_id NUMBER;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    SELECT region_id'||unistr('\000a')||
'      INTO l_region_id'||unistr('\000a')||
'      FROM apex_application_page_ir'||unistr('\000a')||
'     WHERE application_id = g_apex_ir_info.appli';

s:=s||'cation_id'||unistr('\000a')||
'       AND page_id = g_apex_ir_info.page_id;'||unistr('\000a')||
'    RETURN l_region_id;'||unistr('\000a')||
'  EXCEPTION'||unistr('\000a')||
'    WHEN TOO_MANY_ROWS THEN'||unistr('\000a')||
'      apex_debug.ERROR( p_message => ''WARNING: More than 1 IR Region on page %s, choose p_ir_region_id!'''||unistr('\000a')||
'                      , p0        => TO_CHAR (g_apex_ir_info.page_id)'||unistr('\000a')||
'                      );'||unistr('\000a')||
'      raise_application_error( num => -20002'||unistr('\000a')||
'                             , msg =';

s:=s||'> ''Error retrieving Region ID, check APEX Debug Messages!'''||unistr('\000a')||
'                             );'||unistr('\000a')||
'    WHEN NO_DATA_FOUND THEN'||unistr('\000a')||
'      apex_debug.ERROR( p_message => ''WARNING: There is NO IR Region on page %s!'''||unistr('\000a')||
'                      , p0        => TO_CHAR (g_apex_ir_info.page_id)'||unistr('\000a')||
'                      );'||unistr('\000a')||
'      raise_application_error( num => -20002'||unistr('\000a')||
'                             , msg => ''Error retrieving Reg';

s:=s||'ion ID, check APEX Debug Messages!'''||unistr('\000a')||
'                             );'||unistr('\000a')||
'  END get_report_id;'||unistr('\000a')||
'  '||unistr('\000a')||
'  FUNCTION apexir2sheet'||unistr('\000a')||
'    ( p_ir_region_id NUMBER := NULL'||unistr('\000a')||
'    , p_app_id NUMBER := NV(''APP_ID'')'||unistr('\000a')||
'    , p_ir_page_id NUMBER := NV(''APP_PAGE_ID'')'||unistr('\000a')||
'    , p_ir_session_id NUMBER := NV(''SESSION'')'||unistr('\000a')||
'    , p_ir_request VARCHAR2 := V(''REQUEST'')'||unistr('\000a')||
'    , p_ir_view_mode VARCHAR2 := NULL'||unistr('\000a')||
'    , p_column_headers BOOLEAN := T';

s:=s||'RUE'||unistr('\000a')||
'    , p_col_hdr_help BOOLEAN := TRUE'||unistr('\000a')||
'    , p_aggregates IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_process_highlights IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_show_report_title IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_show_filters IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_show_highlights IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_original_line_break IN VARCHAR2 := ''<br />'''||unistr('\000a')||
'    , p_replace_line_break IN VARCHAR2 := chr(13) || chr(10)'||unistr('\000a')||
'    , p_append_date IN BOOLEAN := ';

s:=s||'TRUE'||unistr('\000a')||
'    )'||unistr('\000a')||
'  RETURN apexir_xlsx_types_pkg.t_returnvalue'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_retval apexir_xlsx_types_pkg.t_returnvalue;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    -- IR infos'||unistr('\000a')||
'    g_apex_ir_info.application_id := p_app_id;'||unistr('\000a')||
'    g_apex_ir_info.page_id := p_ir_page_id;'||unistr('\000a')||
'    g_apex_ir_info.session_id := p_ir_session_id;'||unistr('\000a')||
'    g_apex_ir_info.region_id := COALESCE( p_ir_region_id, get_report_id );'||unistr('\000a')||
'    g_apex_ir_info.request := p_ir_request;'||unistr('\000a')||
'    g';

s:=s||'_apex_ir_info.base_report_id := apex_ir.get_last_viewed_report_id(p_page_id => g_apex_ir_info.page_id, p_region_id => g_apex_ir_info.region_id); -- set manual for test outside APEX Environment'||unistr('\000a')||
'    g_apex_ir_info.report_definition := APEX_IR.GET_REPORT ( p_page_id => g_apex_ir_info.page_id, p_region_id => g_apex_ir_info.region_id);'||unistr('\000a')||
'    g_apex_ir_info.aggregates_offset := regexp_count(substr(g_apex_';

s:=s||'ir_info.report_definition.sql_query, 1, INSTR(UPPER(g_apex_ir_info.report_definition.sql_query), '') OVER ('')), '','');'||unistr('\000a')||
'    -- Fix the IR view mode if requested'||unistr('\000a')||
'    IF p_ir_view_mode IN (c_ir_standard_view, c_ir_group_by_view) THEN'||unistr('\000a')||
'      g_apex_ir_info.view_mode := p_ir_view_mode;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
''||unistr('\000a')||
'    -- Generation Options'||unistr('\000a')||
'    g_xlsx_options.show_aggregates := p_aggregates;'||unistr('\000a')||
'    g_xlsx_options.process_hig';

s:=s||'hlights := p_process_highlights;'||unistr('\000a')||
'    g_xlsx_options.show_title := p_show_report_title;'||unistr('\000a')||
'    g_xlsx_options.show_filters := p_show_filters;'||unistr('\000a')||
'    g_xlsx_options.show_highlights := p_show_highlights;'||unistr('\000a')||
'    g_xlsx_options.show_column_headers := p_column_headers;'||unistr('\000a')||
'    g_xlsx_options.col_hdr_help := p_col_hdr_help;'||unistr('\000a')||
'    g_xlsx_options.display_column_count := 0; -- shift result set to right if > 0'||unistr('\000a')||
'    g_xlsx_o';

s:=s||'ptions.default_font := ''Arial'';'||unistr('\000a')||
'    g_xlsx_options.default_border_color := ''b0a070''; -- not yet implemented...'||unistr('\000a')||
'    g_xlsx_options.allow_wrap_text := TRUE;'||unistr('\000a')||
'    g_xlsx_options.original_line_break := p_original_line_break;'||unistr('\000a')||
'    g_xlsx_options.replace_line_break := p_replace_line_break;'||unistr('\000a')||
'    g_xlsx_options.append_date_file_name := p_append_date;'||unistr('\000a')||
'    g_xlsx_options.requested_view_mode := p_ir_view_mode;'||unistr('\000a')||
'';

s:=s||''||unistr('\000a')||
'    -- retrieve IR infos'||unistr('\000a')||
'    get_settings;'||unistr('\000a')||
'    -- construct full SQL and prepare cursor'||unistr('\000a')||
'    prepare_cursor;'||unistr('\000a')||
'    '||unistr('\000a')||
'    -- needed before running any xlsx_builder_pkg commands'||unistr('\000a')||
'    g_xlsx_options.sheet := xlsx_builder_pkg.new_sheet( p_sheetname => g_apex_ir_info.view_mode );'||unistr('\000a')||
'    -- print header if any header option is enabled'||unistr('\000a')||
'    IF g_xlsx_options.show_title OR g_xlsx_options.show_filters OR g_xlsx_op';

s:=s||'tions.show_highlights THEN'||unistr('\000a')||
'      print_header;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
''||unistr('\000a')||
'    -- print column headings if enabled'||unistr('\000a')||
'    IF g_xlsx_options.show_column_headers THEN'||unistr('\000a')||
'      print_column_headers;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
''||unistr('\000a')||
'    -- Generate the "real" data'||unistr('\000a')||
'    print_data;'||unistr('\000a')||
''||unistr('\000a')||
'    -- show generated SQL statement in debug mode'||unistr('\000a')||
'    apex_debug.log_long_message( p_message => ''Generated SQL: '' || g_apex_ir_info.final_sql'||unistr('\000a')||
'                     ';

s:=s||'          , p_level => apex_debug.c_log_level_info'||unistr('\000a')||
'                               );'||unistr('\000a')||
'    -- return the generated spreadsheet and file info                             '||unistr('\000a')||
'    l_retval.file_content := xlsx_builder_pkg.FINISH;'||unistr('\000a')||
'    l_retval.file_name := g_apex_ir_info.report_title || CASE WHEN g_xlsx_options.append_date_file_name THEN ''_'' || to_char(SYSDATE, ''YYYYMMDD'') ELSE NULL END || ''.xlsx'';'||unistr('\000a')||
'    l_r';

s:=s||'etval.mime_type := ''application/octet'';'||unistr('\000a')||
'    l_retval.file_size := dbms_lob.getlength(l_retval.file_content);'||unistr('\000a')||
'    RETURN l_retval;'||unistr('\000a')||
'  EXCEPTION'||unistr('\000a')||
'    WHEN OTHERS THEN'||unistr('\000a')||
'      IF dbms_sql.is_open( g_cursor_info.cursor_id ) THEN'||unistr('\000a')||
'        dbms_sql.close_cursor( g_cursor_info.cursor_id );'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'      apex_debug.message( p_message => ''Unexpected error while generating file.'''||unistr('\000a')||
'                        , ';

s:=s||'p_level => apex_debug.c_log_level_error'||unistr('\000a')||
'                        , p_force => true'||unistr('\000a')||
'                        );'||unistr('\000a')||
'      apex_debug.log_long_message( p_message => ''Error Backtrace: '' || dbms_utility.format_error_backtrace'||unistr('\000a')||
'                                 , p_level => apex_debug.c_log_level_error'||unistr('\000a')||
'                                 , p_enabled => true'||unistr('\000a')||
'                                 );                  '||unistr('\000a')||
'  ';

s:=s||'    apex_debug.log_long_message( p_message => ''Generated SQL: '' || g_apex_ir_info.final_sql'||unistr('\000a')||
'                                 , p_level => apex_debug.c_log_level_error'||unistr('\000a')||
'                                 );'||unistr('\000a')||
'      l_retval.error_encountered := TRUE;'||unistr('\000a')||
'      RETURN l_retval;'||unistr('\000a')||
'  END apexir2sheet;'||unistr('\000a')||
''||unistr('\000a')||
'  PROCEDURE download'||unistr('\000a')||
'    ( p_ir_region_id NUMBER := NULL'||unistr('\000a')||
'    , p_app_id NUMBER := NV(''APP_ID'')'||unistr('\000a')||
'    , p_ir_page_';

s:=s||'id NUMBER := NV(''APP_PAGE_ID'')'||unistr('\000a')||
'    , p_ir_session_id NUMBER := NV(''SESSION'')'||unistr('\000a')||
'    , p_ir_request VARCHAR2 := V(''REQUEST'')'||unistr('\000a')||
'    , p_ir_view_mode VARCHAR2 := NULL'||unistr('\000a')||
'    , p_column_headers BOOLEAN := TRUE'||unistr('\000a')||
'    , p_col_hdr_help BOOLEAN := TRUE'||unistr('\000a')||
'    , p_aggregates IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_process_highlights IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_show_report_title IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_show_filters IN BOOLEAN := T';

s:=s||'RUE'||unistr('\000a')||
'    , p_show_highlights IN BOOLEAN := TRUE'||unistr('\000a')||
'    , p_original_line_break IN VARCHAR2 := ''<br />'''||unistr('\000a')||
'    , p_replace_line_break IN VARCHAR2 := chr(13) || chr(10)'||unistr('\000a')||
'    , p_append_date IN BOOLEAN := TRUE'||unistr('\000a')||
'    )'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_xlsx apexir_xlsx_types_pkg.t_returnvalue;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_xlsx := apexir2sheet( p_ir_region_id'||unistr('\000a')||
'                          , p_app_id'||unistr('\000a')||
'                          , p_ir_page_id'||unistr('\000a')||
'             ';

s:=s||'             , p_ir_session_id'||unistr('\000a')||
'                          , p_ir_request'||unistr('\000a')||
'                          , p_ir_view_mode'||unistr('\000a')||
'                          , p_column_headers'||unistr('\000a')||
'                          , p_col_hdr_help'||unistr('\000a')||
'                          , p_aggregates'||unistr('\000a')||
'                          , p_process_highlights'||unistr('\000a')||
'                          , p_show_report_title'||unistr('\000a')||
'                          , p_show_filters'||unistr('\000a')||
'                ';

s:=s||'          , p_show_highlights'||unistr('\000a')||
'                          , p_original_line_break'||unistr('\000a')||
'                          , p_replace_line_break'||unistr('\000a')||
'                          , p_append_date'||unistr('\000a')||
'                          );'||unistr('\000a')||
'    IF NOT l_xlsx.error_encountered THEN'||unistr('\000a')||
'      OWA_UTIL.mime_header( l_xlsx.mime_type, FALSE );'||unistr('\000a')||
'      HTP.P( ''Content-length: '' || l_xlsx.file_size );'||unistr('\000a')||
'      HTP.P( ''Content-Disposition: attachment; fi';

s:=s||'lename="'' || l_xlsx.file_name || ''"'' );'||unistr('\000a')||
'      OWA_UTIL.http_header_close;'||unistr('\000a')||
'      WPG_DOCLOAD.download_file( l_xlsx.file_content );'||unistr('\000a')||
'    ELSE'||unistr('\000a')||
'      raise_application_error( -20001, ''Something went wrong while generating the file. :-('' );'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'  END download;'||unistr('\000a')||
''||unistr('\000a')||
'  FUNCTION get_version'||unistr('\000a')||
'    RETURN VARCHAR2'||unistr('\000a')||
'  AS'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    RETURN c_version;'||unistr('\000a')||
'  END get_version;'||unistr('\000a')||
''||unistr('\000a')||
'END APEXIR_XLSX_PKG;'||unistr('\000a')||
''||unistr('\000a')||
'/'||unistr('\000a')||
'';

wwv_flow_api.append_to_install_script(
  p_id => 12971322408117110 + wwv_flow_api.g_id_offset,
  p_flow_id => wwv_flow.g_flow_id,
  p_script_clob => s);
end;
 
 
end;
/

 
begin
 
declare
    s varchar2(32767) := null;
    l_clob clob;
    l_length number := 1;
begin
s:=s||'CREATE OR REPLACE PACKAGE PPTX_CREATOR_PKG'||unistr('\000a')||
'  AUTHID CURRENT_USER'||unistr('\000a')||
'AS '||unistr('\000a')||
'/**'||unistr('\000a')||
'* Package provides functions to generate PPTX files based on a template.'||unistr('\000a')||
'* @headcom'||unistr('\000a')||
'*/'||unistr('\000a')||
''||unistr('\000a')||
'  /** Default value for substitution string */'||unistr('\000a')||
'  c_enclose_character CONSTANT VARCHAR2(1) := ''#'';'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Simple one-dimensional array of varchar.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  TYPE t_vc_value_row IS TABLE OF VARCHAR2(4000);'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Simple two-dimensional ';

s:=s||'array with values,'||unistr('\000a')||
'  * which should replace the substitution strings in the template.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  TYPE t_vc_value_tab IS TABLE OF t_vc_value_row INDEX BY PLS_INTEGER;'||unistr('\000a')||
'  '||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Complex type to hold the value for a single replacement.'||unistr('\000a')||
'  * Only one of the *_value parameters should be set.'||unistr('\000a')||
'  * Order of evaluation is: varchar_value -> number_value -> date_value -> clob_value'||unistr('\000a')||
'  * If number or date is use';

s:=s||'d, format_mask needs to be filled also.'||unistr('\000a')||
'  * @param varchar_value Substitution value as VARCHAR2'||unistr('\000a')||
'  * @param number_value Substitution value as NUMBER (format_mask mandatory)'||unistr('\000a')||
'  * @param date_value Substitution value as DATE (format_mask mandatory)'||unistr('\000a')||
'  * @param clob_value Substitution value as CLOB (use with caution)'||unistr('\000a')||
'  */'||unistr('\000a')||
'  TYPE t_replace_value IS RECORD ( varchar_value VARCHAR2(32767)'||unistr('\000a')||
'                ';

s:=s||'                 , number_value NUMBER'||unistr('\000a')||
'                                 , date_value DATE'||unistr('\000a')||
'                                 , clob_value CLOB'||unistr('\000a')||
'                                 , format_mask VARCHAR2(100)'||unistr('\000a')||
'                                 );'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Array of the values indexed by the substitution string.'||unistr('\000a')||
'  * This acts as the value list for a single slide.'||unistr('\000a')||
'  */  '||unistr('\000a')||
'  TYPE t_replace_value_row IS TABLE ';

s:=s||'OF t_replace_value INDEX BY VARCHAR2(32767);'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Array of named value lists.'||unistr('\000a')||
'  * This acts as a table holding all slide lists.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  TYPE t_replace_value_tab IS TABLE OF t_replace_value_row INDEX BY PLS_INTEGER;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Converts the given template into a slide-deck.'||unistr('\000a')||
'  * This is the simpler interface just taking varchar values.'||unistr('\000a')||
'  * @param p_template The PPTX file which should serve as th';

s:=s||'e template.'||unistr('\000a')||
'  * @param p_replace_patterns One-dimensional array of the substitution strings to be replaced.'||unistr('\000a')||
'  * @param p_replace_values Two-dimensional array with the values. (Every row must be ordered same as p_replace_patterns)'||unistr('\000a')||
'  * @return New PPTX file with substitution strings replaced and template slide duplicated as needed.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  FUNCTION convert_template ( p_template IN BLOB'||unistr('\000a')||
'              ';

s:=s||'              , p_replace_patterns IN t_vc_value_row'||unistr('\000a')||
'                            , p_replace_values IN t_vc_value_tab '||unistr('\000a')||
'                            )'||unistr('\000a')||
'    RETURN BLOB;'||unistr('\000a')||
'    '||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Converts the given template into a slide-deck.'||unistr('\000a')||
'  * This overload uses a more sophisticated input, which can deal with varchar, number and date values.'||unistr('\000a')||
'  * @param p_template The PPTX file which should serve as the templa';

s:=s||'te.'||unistr('\000a')||
'  * @param p_replace_name_value Two-dimensional array holding all replacement data.'||unistr('\000a')||
'  * @param p_enclose_char The character surrounding substitution strings.'||unistr('\000a')||
'  * @return New PPTX file with substitution strings replaced and template slide duplicated as needed.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  FUNCTION convert_template ( p_template IN BLOB'||unistr('\000a')||
'                            , p_replace_name_value IN t_replace_value_tab'||unistr('\000a')||
'        ';

s:=s||'                    , p_enclose_char IN VARCHAR2 DEFAULT c_enclose_character'||unistr('\000a')||
'                            )'||unistr('\000a')||
'    RETURN BLOB;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Converts the given template into a slide-deck.'||unistr('\000a')||
'  * This overload allows to pass in a ref cursor'||unistr('\000a')||
'  * which is parsed and the column aliases are used as substitution strings.'||unistr('\000a')||
'  * @param p_template The PPTX file which should serve as the template.'||unistr('\000a')||
'  * @param p_cursor ';

s:=s||'A ref cursor defining the data to use.'||unistr('\000a')||
'  * @param p_enclose_char The character surrounding substitution strings.'||unistr('\000a')||
'  */'||unistr('\000a')||
'  FUNCTION convert_template ( p_template IN BLOB'||unistr('\000a')||
'                            , p_cursor IN OUT NOCOPY sys_refcursor'||unistr('\000a')||
'                            , p_enclose_char IN VARCHAR2 DEFAULT c_enclose_character'||unistr('\000a')||
'                            )'||unistr('\000a')||
'    RETURN BLOB;'||unistr('\000a')||
''||unistr('\000a')||
'END PPTX_CREATOR_PKG;'||unistr('\000a')||
'/'||unistr('\000a')||
''||unistr('\000a')||
'create o';

s:=s||'r replace PACKAGE BODY PPTX_CREATOR_PKG'||unistr('\000a')||
'AS'||unistr('\000a')||
''||unistr('\000a')||
'  /*'||unistr('\000a')||
'    Types'||unistr('\000a')||
'  */'||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Record which holds all data about a single slide'||unistr('\000a')||
'  * @param slide_num'||unistr('\000a')||
'  * @param slide_id'||unistr('\000a')||
'  * @param relation_id'||unistr('\000a')||
'  * @param slide_data'||unistr('\000a')||
'  * @param notes_data'||unistr('\000a')||
'  */'||unistr('\000a')||
'  TYPE t_single_slide IS RECORD'||unistr('\000a')||
'    ( slide_num NUMBER'||unistr('\000a')||
'    , slide_id NUMBER'||unistr('\000a')||
'    , relation_id NUMBER'||unistr('\000a')||
'    , slide_data CLOB'||unistr('\000a')||
'    , notes_data CLOB'||unistr('\000a')||
'    );'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * ';

s:=s||'Array of slide details'||unistr('\000a')||
'  */'||unistr('\000a')||
'  TYPE t_all_slides IS TABLE OF t_single_slide INDEX BY PLS_INTEGER;'||unistr('\000a')||
''||unistr('\000a')||
'  /**'||unistr('\000a')||
'  * Record to keep all relevant templates together'||unistr('\000a')||
'  * @param slide The template slide with substitution patterns'||unistr('\000a')||
'  * @param slide_relations Template slide relations'||unistr('\000a')||
'  * @param notes The template notes with substitution patterns'||unistr('\000a')||
'  * @param notes_relations Template notes relations'||unistr('\000a')||
'  */'||unistr('\000a')||
'  TYPE t_t';

s:=s||'emplates IS RECORD'||unistr('\000a')||
'    ( slide CLOB'||unistr('\000a')||
'    , slide_relations CLOB'||unistr('\000a')||
'    , notes CLOB'||unistr('\000a')||
'    , notes_relations CLOB'||unistr('\000a')||
'    );'||unistr('\000a')||
''||unistr('\000a')||
'  TYPE t_col_values IS RECORD'||unistr('\000a')||
'    ( varchar_values dbms_sql.varchar2_table'||unistr('\000a')||
'    , number_values dbms_sql.number_table'||unistr('\000a')||
'    , date_values dbms_sql.date_table'||unistr('\000a')||
'    , clob_values dbms_sql.clob_table'||unistr('\000a')||
'    );'||unistr('\000a')||
'    '||unistr('\000a')||
'  TYPE t_sql_rows IS TABLE OF t_col_values INDEX BY VARCHAR2(32767);'||unistr('\000a')||
''||unistr('\000a')||
'  /*'||unistr('\000a')||
'    C';

s:=s||'onstants'||unistr('\000a')||
'  */'||unistr('\000a')||
'  '||unistr('\000a')||
'  /* Static filenames for top level files */'||unistr('\000a')||
'  c_content_types_fname CONSTANT VARCHAR(19 CHAR) := ''[Content_Types].xml'';'||unistr('\000a')||
'  c_presentation_fname CONSTANT VARCHAR2(20 CHAR) := ''ppt/presentation.xml'';'||unistr('\000a')||
'  c_pres_rel_fname CONSTANT VARCHAR2(31 CHAR) := ''ppt/_rels/presentation.xml.rels'';'||unistr('\000a')||
'  '||unistr('\000a')||
'  /* Filenames for templates */'||unistr('\000a')||
'  c_template_slide CONSTANT VARCHAR2(21 CHAR) := ''ppt/slides/slide';

s:=s||'1.xml'';'||unistr('\000a')||
'  c_template_slide_rel CONSTANT VARCHAR2(32 CHAR) := ''ppt/slides/_rels/slide1.xml.rels'';'||unistr('\000a')||
'  c_template_notes CONSTANT VARCHAR2(31 CHAR) := ''ppt/notesSlides/notesSlide1.xml'';'||unistr('\000a')||
'  c_template_notes_rel CONSTANT VARCHAR2(42 CHAR) := ''ppt/notesSlides/_rels/notesSlide1.xml.rels'';'||unistr('\000a')||
'  '||unistr('\000a')||
'  /* Directories for slides and notes */'||unistr('\000a')||
'  c_slide_dname CONSTANT VARCHAR2(11 CHAR) := ''ppt/slides/'';'||unistr('\000a')||
'  c_slide_rel_d';

s:=s||'name CONSTANT VARCHAR2(17 CHAR) := ''ppt/slides/_rels/'';'||unistr('\000a')||
'  c_notes_dname CONSTANT VARCHAR2(16 CHAR) := ''ppt/notesSlides/'';'||unistr('\000a')||
'  c_notes_rel_dname CONSTANT VARCHAR2(22 CHAR) := ''ppt/notesSlides/_rels/'';'||unistr('\000a')||
'  '||unistr('\000a')||
'  /* Template filenames for slide and notes */'||unistr('\000a')||
'  c_slide_fname_pattern CONSTANT VARCHAR2(14 CHAR) := ''slide#NUM#.xml'';'||unistr('\000a')||
'  c_notes_fname_pattern CONSTANT VARCHAR2(20 CHAR) := ''notesSlide#NUM#.xml'';'||unistr('\000a')||
'  c';

s:=s||'_relation_suffix CONSTANT VARCHAR2(5 CHAR) := ''.rels'';'||unistr('\000a')||
''||unistr('\000a')||
'  c_slide_content_type CONSTANT VARCHAR2(70 CHAR) := ''application/vnd.openxmlformats-officedocument.presentationml.slide+xml'';'||unistr('\000a')||
'  c_sliderel_content_type CONSTANT VARCHAR2(73 CHAR) := ''http://schemas.openxmlformats.org/officeDocument/2006/relationships/slide'';'||unistr('\000a')||
'  c_notes_content_type CONSTANT VARCHAR2(75 CHAR) := ''application/vnd.openxmlformats';

s:=s||'-officedocument.presentationml.notesSlide+xml'';'||unistr('\000a')||
'  c_presentation_slide CONSTANT VARCHAR2(45 CHAR) := ''<p:sldId id="#SLIDE_ID#" r:id="rId#REL_ID#"/>'';'||unistr('\000a')||
''||unistr('\000a')||
'  c_slide_num_offset CONSTANT NUMBER := 0;'||unistr('\000a')||
'  c_bulk_size CONSTANT PLS_INTEGER := 100;'||unistr('\000a')||
''||unistr('\000a')||
'  /*'||unistr('\000a')||
'    Globals'||unistr('\000a')||
'  */'||unistr('\000a')||
'  g_base_file BLOB;'||unistr('\000a')||
'  g_file_list zip_util_pkg.t_file_list;'||unistr('\000a')||
''||unistr('\000a')||
'  g_slide_id_offset NUMBER := 255;'||unistr('\000a')||
'  g_slide_relation_id_offset NUMBER := 6;'||unistr('\000a')||
''||unistr('\000a')||
' ';

s:=s||' g_templates t_templates;'||unistr('\000a')||
'  g_slides t_all_slides;'||unistr('\000a')||
''||unistr('\000a')||
'  g_replace_value_tab t_replace_value_tab;'||unistr('\000a')||
'  g_enclose_character VARCHAR2(1);'||unistr('\000a')||
''||unistr('\000a')||
'  /*'||unistr('\000a')||
'    Private API'||unistr('\000a')||
'  */'||unistr('\000a')||
''||unistr('\000a')||
'  FUNCTION convert_replace( p_replace_patterns IN t_vc_value_row'||unistr('\000a')||
'                          , p_replace_values IN t_vc_value_tab'||unistr('\000a')||
'                          )'||unistr('\000a')||
'    RETURN t_replace_value_tab'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_returnvalue t_replace_value_tab;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'   ';

s:=s||' FOR i IN 1..p_replace_values.count LOOP'||unistr('\000a')||
'      FOR j IN 1..p_replace_patterns.count LOOP'||unistr('\000a')||
'        l_returnvalue(i)(p_replace_patterns(j)).varchar_value := p_replace_values(i)(j);'||unistr('\000a')||
'      END LOOP;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    RETURN l_returnvalue;'||unistr('\000a')||
'  END convert_replace;'||unistr('\000a')||
'  '||unistr('\000a')||
'  FUNCTION convert_replace( p_sql_result IN t_sql_rows )'||unistr('\000a')||
'    RETURN t_replace_value_tab'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_returnvalue t_replace_value_tab;'||unistr('\000a')||
'    l_cu';

s:=s||'r_col VARCHAR2(32767);'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_cur_col := p_sql_result.first();'||unistr('\000a')||
'    WHILE l_cur_col IS NOT NULL LOOP'||unistr('\000a')||
'      IF p_sql_result(l_cur_col).varchar_values.count > 0 THEN'||unistr('\000a')||
'        FOR i IN 1..p_sql_result(l_cur_col).varchar_values.count LOOP'||unistr('\000a')||
'          l_returnvalue(i)(l_cur_col).varchar_value := p_sql_result(l_cur_col).varchar_values(i);'||unistr('\000a')||
'        END LOOP;'||unistr('\000a')||
'      ELSIF p_sql_result(l_cur_col).number_v';

s:=s||'alues.count > 0 THEN'||unistr('\000a')||
'        FOR i IN 1..p_sql_result(l_cur_col).number_values.count LOOP'||unistr('\000a')||
'          l_returnvalue(i)(l_cur_col).number_value := p_sql_result(l_cur_col).number_values(i);'||unistr('\000a')||
'        END LOOP;'||unistr('\000a')||
'      ELSIF p_sql_result(l_cur_col).date_values.count > 0 THEN'||unistr('\000a')||
'        FOR i IN 1..p_sql_result(l_cur_col).date_values.count LOOP'||unistr('\000a')||
'          l_returnvalue(i)(l_cur_col).date_value := p_sql_result(l';

s:=s||'_cur_col).date_values(i);'||unistr('\000a')||
'        END LOOP;'||unistr('\000a')||
'      ELSIF p_sql_result(l_cur_col).clob_values.count > 0 THEN'||unistr('\000a')||
'        FOR i IN 1..p_sql_result(l_cur_col).clob_values.count LOOP'||unistr('\000a')||
'          l_returnvalue(i)(l_cur_col).clob_value := p_sql_result(l_cur_col).clob_values(i);'||unistr('\000a')||
'        END LOOP;      '||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'      l_cur_col := p_sql_result.next(l_cur_col);'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    RETURN l_returnvalue;'||unistr('\000a')||
'  END c';

s:=s||'onvert_replace;'||unistr('\000a')||
''||unistr('\000a')||
'  FUNCTION convert_value ( p_value IN t_replace_value )'||unistr('\000a')||
'    RETURN VARCHAR2'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_returnvalue VARCHAR2(32767);'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    IF p_value.varchar_value IS NOT NULL THEN'||unistr('\000a')||
'      l_returnvalue := p_value.varchar_value;'||unistr('\000a')||
'    ELSIF p_value.number_value IS NOT NULL THEN'||unistr('\000a')||
'      IF p_value.format_mask IS NOT NULL THEN'||unistr('\000a')||
'        l_returnvalue := to_char( p_value.number_value, p_value.format_ma';

s:=s||'sk );'||unistr('\000a')||
'      ELSE'||unistr('\000a')||
'        l_returnvalue := to_char( p_value.number_value);'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    ELSIF p_value.date_value IS NOT NULL THEN'||unistr('\000a')||
'      IF p_value.format_mask IS NOT NULL THEN'||unistr('\000a')||
'        l_returnvalue := to_char( p_value.date_value, p_value.format_mask );'||unistr('\000a')||
'      ELSE'||unistr('\000a')||
'        l_returnvalue := to_char( p_value.date_value);'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    ELSIF p_value.clob_value IS NOT NULL THEN'||unistr('\000a')||
'      l_returnval';

s:=s||'ue := dbms_lob.substr(p_value.clob_value);'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    RETURN l_returnvalue;'||unistr('\000a')||
'  END convert_value;'||unistr('\000a')||
''||unistr('\000a')||
'  FUNCTION replace_substitution( p_original IN CLOB'||unistr('\000a')||
'                               , p_row_num IN PLS_INTEGER'||unistr('\000a')||
'                               )'||unistr('\000a')||
'    RETURN CLOB'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_returnvalue CLOB := p_original;'||unistr('\000a')||
'    l_current_index VARCHAR2(30);'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_current_index := g_replace_value_tab(p_row_num)';

s:=s||'.FIRST;'||unistr('\000a')||
'    WHILE l_current_index IS NOT NULL LOOP'||unistr('\000a')||
'      l_returnvalue := REPLACE( l_returnvalue'||unistr('\000a')||
'                              , g_enclose_character || l_current_index || g_enclose_character'||unistr('\000a')||
'                              , convert_value(g_replace_value_tab(p_row_num)(l_current_index))'||unistr('\000a')||
'                              );'||unistr('\000a')||
'      l_current_index := g_replace_value_tab(p_row_num).NEXT(l_current_index);'||unistr('\000a')||
'  ';

s:=s||'  END LOOP;'||unistr('\000a')||
'    RETURN l_returnvalue;'||unistr('\000a')||
'  END replace_substitution;'||unistr('\000a')||
''||unistr('\000a')||
'  PROCEDURE set_offsets'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_data XMLTYPE;'||unistr('\000a')||
'    l_clob CLOB;'||unistr('\000a')||
'    l_domdoc dbms_xmldom.DOMDocument;'||unistr('\000a')||
'    l_root_node dbms_xmldom.DOMNode;'||unistr('\000a')||
'    l_rels dbms_xmldom.DOMNodeList;'||unistr('\000a')||
'    l_act_node dbms_xmldom.DOMNode;'||unistr('\000a')||
'    l_act_element dbms_xmldom.DOMElement;'||unistr('\000a')||
'    l_attrs dbms_xmldom.DOMNamedNodeMap;'||unistr('\000a')||
'    l_attr VARCHAR2(32676);'||unistr('\000a')||
'    l_mat';

s:=s||'ch_left NUMBER;'||unistr('\000a')||
'    l_match_right NUMBER;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    -- presentation to get slide Ids'||unistr('\000a')||
'    l_clob := zip_util_pkg.get_file_clob( g_base_file, c_presentation_fname );'||unistr('\000a')||
'    l_match_left := INSTR(l_clob, ''sldId'');'||unistr('\000a')||
'    l_match_left := INSTR(l_clob, ''"'', l_match_left);'||unistr('\000a')||
'    l_match_right := INSTR(l_clob, ''"'', l_match_left + 1);'||unistr('\000a')||
'    l_attr := SUBSTR(l_clob, l_match_left + 1, l_match_right - l_match_left -';

s:=s||' 1);'||unistr('\000a')||
'    IF l_attr IS NOT NULL THEN'||unistr('\000a')||
'      g_slide_id_offset := GREATEST(g_slide_id_offset, to_number(l_attr));'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    '||unistr('\000a')||
'    -- presentation rels to get rId'||unistr('\000a')||
'    l_data := XMLTYPE(zip_util_pkg.get_file_clob( g_base_file, c_pres_rel_fname ));'||unistr('\000a')||
'    l_domdoc := dbms_xmldom.newDOMDocument(l_data);'||unistr('\000a')||
'    l_root_node := dbms_xmldom.makeNode(DBMS_XMLDOM.getDocumentElement(l_domdoc));'||unistr('\000a')||
'    l_rels := dbm';

s:=s||'s_xmldom.getChildNodes(l_root_node);'||unistr('\000a')||
'    FOR i IN 1..dbms_xmldom.getLength(l_rels)'||unistr('\000a')||
'    LOOP'||unistr('\000a')||
'      l_act_element := dbms_xmldom.makeElement(dbms_xmldom.item(l_rels, i));'||unistr('\000a')||
'      l_attr := dbms_xmldom.getAttribute(l_act_element, ''Id'');'||unistr('\000a')||
'      IF l_attr IS NOT NULL THEN'||unistr('\000a')||
'        g_slide_relation_id_offset := GREATEST(g_slide_relation_id_offset, to_number(substr(l_attr, 4)));'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
' ';

s:=s||' END set_offsets;'||unistr('\000a')||
''||unistr('\000a')||
'  PROCEDURE set_template_slide'||unistr('\000a')||
'  AS'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    g_templates.slide := zip_util_pkg.get_file_clob( g_base_file, c_template_slide );'||unistr('\000a')||
'    g_templates.slide_relations := zip_util_pkg.get_file_clob( g_base_file, c_template_slide_rel );'||unistr('\000a')||
'    g_templates.notes := zip_util_pkg.get_file_clob( g_base_file, c_template_notes );'||unistr('\000a')||
'    g_templates.notes_relations := zip_util_pkg.get_file_clob( g_';

s:=s||'base_file, c_template_notes_rel );'||unistr('\000a')||
'  END set_template_slide;'||unistr('\000a')||
'  '||unistr('\000a')||
'  FUNCTION process_slide_relation( p_slide_num IN NUMBER )'||unistr('\000a')||
'    RETURN CLOB'||unistr('\000a')||
'  AS'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    RETURN REPLACE( g_templates.slide_relations, ''notesSlide1'', ''notesSlide'' || to_char(p_slide_num) );'||unistr('\000a')||
'  END process_slide_relation;'||unistr('\000a')||
'  '||unistr('\000a')||
'  FUNCTION process_notes_relation( p_slide_num IN NUMBER )'||unistr('\000a')||
'    RETURN CLOB'||unistr('\000a')||
'  AS'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    RETURN REPLACE( g_t';

s:=s||'emplates.notes_relations, ''slide1'', ''slide'' || to_char(p_slide_num) );'||unistr('\000a')||
'  END process_notes_relation;  '||unistr('\000a')||
''||unistr('\000a')||
'  -- Insert override tags into content types for each slide'||unistr('\000a')||
'  PROCEDURE update_content_types( p_file IN OUT NOCOPY BLOB )'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_data XMLTYPE;'||unistr('\000a')||
'    l_domdoc dbms_xmldom.DOMDocument;'||unistr('\000a')||
'    l_root_node dbms_xmldom.DOMNode;'||unistr('\000a')||
'    l_slide_element dbms_xmldom.DOMElement;'||unistr('\000a')||
'    l_slide_node dbms_xmldom.D';

s:=s||'OMNode;'||unistr('\000a')||
'    l_notes_element dbms_xmldom.DOMElement;'||unistr('\000a')||
'    l_notes_node dbms_xmldom.DOMNode;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_data := XMLTYPE(zip_util_pkg.get_file_clob(g_base_file, c_content_types_fname));'||unistr('\000a')||
'    l_domdoc := dbms_xmldom.newDOMDocument(l_data);'||unistr('\000a')||
'    l_root_node := dbms_xmldom.makeNode(DBMS_XMLDOM.getDocumentElement(l_domdoc));'||unistr('\000a')||
'    FOR i IN 2..g_slides.COUNT'||unistr('\000a')||
'    LOOP'||unistr('\000a')||
'      l_slide_element := dbms_xmldom.cre';

s:=s||'ateElement(l_domdoc, ''Override'');'||unistr('\000a')||
'      dbms_xmldom.setAttribute(l_slide_element, ''PartName'', ''/'' || c_slide_dname || REPLACE(c_slide_fname_pattern, ''#NUM#'', to_char(g_slides(i).slide_num)) );'||unistr('\000a')||
'      dbms_xmldom.setAttribute(l_slide_element, ''ContentType'', c_slide_content_type);'||unistr('\000a')||
'      l_slide_node := dbms_xmldom.appendChild(l_root_node, dbms_xmldom.makeNode(l_slide_element));'||unistr('\000a')||
'      IF g_templates.n';

s:=s||'otes IS NOT NULL THEN'||unistr('\000a')||
'        l_notes_element := dbms_xmldom.createElement(l_domdoc, ''Override'');'||unistr('\000a')||
'        dbms_xmldom.setAttribute(l_notes_element, ''PartName'', ''/'' || c_notes_dname || REPLACE(c_notes_fname_pattern, ''#NUM#'', to_char(g_slides(i).slide_num)) );'||unistr('\000a')||
'        dbms_xmldom.setAttribute(l_notes_element, ''ContentType'', c_notes_content_type);        '||unistr('\000a')||
'        l_notes_node := dbms_xmldom.appendChi';

s:=s||'ld(l_root_node, dbms_xmldom.makeNode(l_notes_element));'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    dbms_xmldom.freeDocument(l_domdoc);'||unistr('\000a')||
'    zip_util_pkg.add_file(p_file, c_content_types_fname, REPLACE(l_data.getClobVal, ''ISO-8859-15'', ''UTF-8''));'||unistr('\000a')||
'  END update_content_types;'||unistr('\000a')||
''||unistr('\000a')||
'  -- Insert slide id tag into slide id list of presentation for each slide'||unistr('\000a')||
'  PROCEDURE update_presentation( p_file IN OUT NOCOPY BLOB )'||unistr('\000a')||
' ';

s:=s||' AS'||unistr('\000a')||
'    l_data XMLTYPE;'||unistr('\000a')||
'    l_domdoc dbms_xmldom.DOMDocument;'||unistr('\000a')||
'    l_root_node dbms_xmldom.DOMNode;'||unistr('\000a')||
'    l_slide_list dbms_xmldom.DOMNode;'||unistr('\000a')||
'    l_element dbms_xmldom.DOMElement;'||unistr('\000a')||
'    l_slide_node dbms_xmldom.DOMNode;'||unistr('\000a')||
'    l_id_attr dbms_xmldom.DOMAttr;'||unistr('\000a')||
'    l_rid_attr dbms_xmldom.DOMAttr;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_data := XMLTYPE(zip_util_pkg.get_file_clob(g_base_file, c_presentation_fname));'||unistr('\000a')||
'    l_domdoc := dbms_x';

s:=s||'mldom.newDOMDocument(l_data);'||unistr('\000a')||
'    l_root_node := dbms_xmldom.makeNode(DBMS_XMLDOM.getDocumentElement(l_domdoc));'||unistr('\000a')||
'    l_slide_list := dbms_xmldom.item(dbms_xmldom.getElementsByTagName(l_domdoc, ''sldIdLst''), 0);'||unistr('\000a')||
'    FOR i IN 2..g_slides.COUNT'||unistr('\000a')||
'    LOOP'||unistr('\000a')||
'      l_element := dbms_xmldom.createElement(l_domdoc, ''p:sldId'');'||unistr('\000a')||
'      l_id_attr := dbms_xmldom.createAttribute(l_domdoc, ''id'');'||unistr('\000a')||
'      l_rid_attr :=';

s:=s||' dbms_xmldom.createAttribute(l_domdoc, ''r:id'');'||unistr('\000a')||
'      dbms_xmldom.setValue(l_rid_attr, ''rId'' || to_char(g_slides(i).relation_id));'||unistr('\000a')||
'      dbms_xmldom.setValue(l_id_attr, to_char(g_slides(i).slide_id));'||unistr('\000a')||
'      l_id_attr := dbms_xmldom.setAttributeNode(l_element, l_id_attr);'||unistr('\000a')||
'      l_rid_attr := dbms_xmldom.setAttributeNode(l_element, l_rid_attr);      '||unistr('\000a')||
'      l_slide_node := dbms_xmldom.appendChild(l_s';

s:=s||'lide_list, dbms_xmldom.makeNode(l_element));'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    dbms_xmldom.freeDocument(l_domdoc);'||unistr('\000a')||
'    zip_util_pkg.add_file(p_file, c_presentation_fname, REPLACE(l_data.getClobVal, ''ISO-8859-15'', ''UTF-8''));'||unistr('\000a')||
'  END update_presentation;'||unistr('\000a')||
'  '||unistr('\000a')||
'  -- Insert relationship tag into presenation relations for every slide'||unistr('\000a')||
'  PROCEDURE update_pres_rel( p_file IN OUT NOCOPY BLOB )'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_data XMLTYPE;'||unistr('\000a')||
'    l_do';

s:=s||'mdoc dbms_xmldom.DOMDocument;'||unistr('\000a')||
'    l_root_node dbms_xmldom.DOMNode;'||unistr('\000a')||
'    l_element dbms_xmldom.DOMElement;'||unistr('\000a')||
'    l_slide_node dbms_xmldom.DOMNode;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_data := XMLTYPE(zip_util_pkg.get_file_clob(g_base_file, c_pres_rel_fname));'||unistr('\000a')||
'    l_domdoc := dbms_xmldom.newDOMDocument(l_data);'||unistr('\000a')||
'    l_root_node := dbms_xmldom.makeNode(DBMS_XMLDOM.getDocumentElement(l_domdoc));'||unistr('\000a')||
'    FOR i IN 2..g_slides.COUNT'||unistr('\000a')||
' ';

s:=s||'   LOOP'||unistr('\000a')||
'      l_element := dbms_xmldom.createElement(l_domdoc, ''Relationship'');'||unistr('\000a')||
'      dbms_xmldom.setAttribute(l_element, ''Id'', ''rId'' || to_char(g_slides(i).relation_id));'||unistr('\000a')||
'      dbms_xmldom.setAttribute(l_element, ''Type'', c_sliderel_content_type);'||unistr('\000a')||
'      dbms_xmldom.setAttribute(l_element, ''Target'', ''slides/'' || REPLACE(c_slide_fname_pattern, ''#NUM#'', to_char(g_slides(i).slide_num)));'||unistr('\000a')||
'      l_slide';

s:=s||'_node := dbms_xmldom.appendChild(l_root_node, dbms_xmldom.makeNode(l_element));'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    dbms_xmldom.freeDocument(l_domdoc);'||unistr('\000a')||
'    zip_util_pkg.add_file(p_file, c_pres_rel_fname, REPLACE(l_data.getClobVal, ''ISO-8859-15'', ''UTF-8''));'||unistr('\000a')||
'  END update_pres_rel;'||unistr('\000a')||
'  '||unistr('\000a')||
''||unistr('\000a')||
'  /*'||unistr('\000a')||
'    Public API'||unistr('\000a')||
'  */'||unistr('\000a')||
'  '||unistr('\000a')||
'  FUNCTION convert_template ( p_template IN BLOB'||unistr('\000a')||
'                            , p_replace_patterns IN t_vc_';

s:=s||'value_row'||unistr('\000a')||
'                            , p_replace_values IN t_vc_value_tab '||unistr('\000a')||
'                            )'||unistr('\000a')||
'    RETURN BLOB'||unistr('\000a')||
'  AS'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    RETURN convert_template( p_template => p_template'||unistr('\000a')||
'                           , p_replace_name_value => convert_replace( p_replace_patterns, p_replace_values )'||unistr('\000a')||
'                           , p_enclose_char => NULL'||unistr('\000a')||
'                           );'||unistr('\000a')||
'  END convert_templa';

s:=s||'te;'||unistr('\000a')||
''||unistr('\000a')||
'  FUNCTION convert_template ( p_template IN BLOB'||unistr('\000a')||
'                            , p_replace_name_value IN t_replace_value_tab'||unistr('\000a')||
'                            , p_enclose_char IN VARCHAR2 DEFAULT c_enclose_character'||unistr('\000a')||
'                            )'||unistr('\000a')||
'    RETURN BLOB'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_current_slide t_single_slide;'||unistr('\000a')||
'    l_returnvalue BLOB;'||unistr('\000a')||
'    l_current_file BLOB;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    /* Init globals */'||unistr('\000a')||
'    g_base_file := p';

s:=s||'_template;'||unistr('\000a')||
'    g_file_list := zip_util_pkg.get_file_list (g_base_file);'||unistr('\000a')||
'    set_template_slide;'||unistr('\000a')||
'    set_offsets;'||unistr('\000a')||
'    g_replace_value_tab := p_replace_name_value;'||unistr('\000a')||
'    g_enclose_character := p_enclose_char;'||unistr('\000a')||
'    '||unistr('\000a')||
'    IF g_replace_value_tab.count = 0 THEN'||unistr('\000a')||
'      RETURN NULL;'||unistr('\000a')||
'    END IF;'||unistr('\000a')||
'    '||unistr('\000a')||
'    -- Create all slides and notes based on template and add to file'||unistr('\000a')||
'    FOR i IN 1..g_replace_value_tab.COUNT'||unistr('\000a')||
' ';

s:=s||'   LOOP'||unistr('\000a')||
'      l_current_slide.slide_num := c_slide_num_offset + i;'||unistr('\000a')||
'      l_current_slide.slide_id := g_slide_id_offset + i;'||unistr('\000a')||
'      l_current_slide.relation_id := g_slide_relation_id_offset + i;'||unistr('\000a')||
'      '||unistr('\000a')||
'      /* Slide and Relations (always)*/      '||unistr('\000a')||
'      l_current_slide.slide_data := replace_substitution( p_original => g_templates.slide'||unistr('\000a')||
'                                                        , p_row_';

s:=s||'num => i'||unistr('\000a')||
'                                                        );'||unistr('\000a')||
'      zip_util_pkg.add_file ( l_returnvalue'||unistr('\000a')||
'                            , c_slide_dname || REPLACE(c_slide_fname_pattern, ''#NUM#'', to_char(l_current_slide.slide_num))'||unistr('\000a')||
'                            , l_current_slide.slide_data'||unistr('\000a')||
'                            );'||unistr('\000a')||
'      zip_util_pkg.add_file ( l_returnvalue'||unistr('\000a')||
'                            , c_s';

s:=s||'lide_rel_dname || REPLACE(c_slide_fname_pattern, ''#NUM#'', to_char(l_current_slide.slide_num)) || c_relation_suffix'||unistr('\000a')||
'                            , process_slide_relation( l_current_slide.slide_num )'||unistr('\000a')||
'                            );'||unistr('\000a')||
'      /* Notes and Relations (only if notes exist) */'||unistr('\000a')||
'      IF g_templates.notes IS NOT NULL THEN'||unistr('\000a')||
'        l_current_slide.notes_data := replace_substitution( p_original => ';

s:=s||'g_templates.notes'||unistr('\000a')||
'                                                          , p_row_num => i'||unistr('\000a')||
'                                                          );'||unistr('\000a')||
'        zip_util_pkg.add_file ( l_returnvalue'||unistr('\000a')||
'                              , c_notes_dname || REPLACE(c_notes_fname_pattern, ''#NUM#'', to_char(l_current_slide.slide_num))'||unistr('\000a')||
'                              , l_current_slide.notes_data'||unistr('\000a')||
'                ';

s:=s||'              );'||unistr('\000a')||
'        zip_util_pkg.add_file ( l_returnvalue'||unistr('\000a')||
'                              , c_notes_rel_dname || REPLACE(c_notes_fname_pattern, ''#NUM#'', to_char(l_current_slide.slide_num)) || c_relation_suffix'||unistr('\000a')||
'                              , process_notes_relation( l_current_slide.slide_num )'||unistr('\000a')||
'                              );        '||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'      g_slides(i) := l_current_slide;'||unistr('\000a')||
'    END LO';

s:=s||'OP;'||unistr('\000a')||
'    '||unistr('\000a')||
'    -- slides prepared, loop through file content and adapt where needed'||unistr('\000a')||
'    FOR i IN 1..g_file_list.COUNT'||unistr('\000a')||
'    LOOP'||unistr('\000a')||
'      IF g_file_list(i) = c_content_types_fname'||unistr('\000a')||
'      THEN'||unistr('\000a')||
'        update_content_types(l_returnvalue); --update content types file with overrides for slides'||unistr('\000a')||
'      ELSIF g_file_list(i) = c_presentation_fname'||unistr('\000a')||
'      THEN'||unistr('\000a')||
'        update_presentation(l_returnvalue); -- update pr';

s:=s||'esentation file with slide refs'||unistr('\000a')||
'      ELSIF g_file_list(i) = c_pres_rel_fname'||unistr('\000a')||
'      THEN'||unistr('\000a')||
'        update_pres_rel(l_returnvalue); --update presentation rels with slide infos'||unistr('\000a')||
'      ELSIF g_file_list(i) NOT LIKE c_slide_dname || ''%'' AND g_file_list(i) NOT LIKE c_notes_dname || ''%'''||unistr('\000a')||
'      THEN'||unistr('\000a')||
'        zip_util_pkg.add_file( l_returnvalue, g_file_list(i), zip_util_pkg.get_file( g_base_file, g_file_list(';

s:=s||'i)));'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    zip_util_pkg.finish_zip (l_returnvalue);'||unistr('\000a')||
'    RETURN l_returnvalue;'||unistr('\000a')||
'  END convert_template;'||unistr('\000a')||
''||unistr('\000a')||
'  FUNCTION convert_template ( p_template IN BLOB'||unistr('\000a')||
'                            , p_cursor IN OUT NOCOPY sys_refcursor'||unistr('\000a')||
'                            , p_enclose_char IN VARCHAR2 DEFAULT c_enclose_character'||unistr('\000a')||
'                            )'||unistr('\000a')||
'    RETURN BLOB'||unistr('\000a')||
'  AS'||unistr('\000a')||
'    l_cursor_id PL';

s:=s||'S_INTEGER;'||unistr('\000a')||
'    l_desc_tab dbms_sql.desc_tab2;'||unistr('\000a')||
'    l_col_cnt PLS_INTEGER;'||unistr('\000a')||
'    l_row_cnt PLS_INTEGER;'||unistr('\000a')||
'    l_sql_result t_sql_rows;'||unistr('\000a')||
'  BEGIN'||unistr('\000a')||
'    l_cursor_id := dbms_sql.to_cursor_number( p_cursor );'||unistr('\000a')||
'    dbms_sql.describe_columns2( l_cursor_id, l_col_cnt, l_desc_tab );'||unistr('\000a')||
'    '||unistr('\000a')||
'    -- Prepare the arrays'||unistr('\000a')||
'    FOR i IN 1..l_col_cnt LOOP'||unistr('\000a')||
'      CASE'||unistr('\000a')||
'        WHEN l_desc_tab( i ).col_type IN ( 2, 100, 101 ) THEN'||unistr('\000a')||
'';

s:=s||'          dbms_sql.define_array( l_cursor_id, i, l_sql_result(l_desc_tab(i).col_name).number_values, c_bulk_size, 1 );'||unistr('\000a')||
'        WHEN l_desc_tab( i ).col_type IN ( 12, 178, 179, 180, 181 , 231 ) THEN'||unistr('\000a')||
'          dbms_sql.define_array( l_cursor_id, i, l_sql_result(l_desc_tab(i).col_name).date_values, c_bulk_size, 1 );'||unistr('\000a')||
'        WHEN l_desc_tab( i ).col_type IN ( 1, 8, 9, 96 ) THEN'||unistr('\000a')||
'          dbms_sql.defi';

s:=s||'ne_array( l_cursor_id, i, l_sql_result(l_desc_tab(i).col_name).varchar_values, c_bulk_size, 1 );'||unistr('\000a')||
'        WHEN l_desc_tab( i ).col_type = 112 THEN'||unistr('\000a')||
'          dbms_sql.define_array( l_cursor_id, i, l_sql_result(l_desc_tab(i).col_name).clob_values, c_bulk_size, 1 );'||unistr('\000a')||
'        ELSE'||unistr('\000a')||
'          NULL;'||unistr('\000a')||
'      END CASE;      '||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    '||unistr('\000a')||
'    -- Execute and fill arrays'||unistr('\000a')||
'    l_row_cnt := dbms_sql.EXECUTE( l';

s:=s||'_cursor_id );'||unistr('\000a')||
'    LOOP'||unistr('\000a')||
'      l_row_cnt := dbms_sql.fetch_rows( l_cursor_id );'||unistr('\000a')||
'      IF l_row_cnt > 0 THEN'||unistr('\000a')||
'        FOR i IN 1..l_col_cnt LOOP'||unistr('\000a')||
'          CASE'||unistr('\000a')||
'            WHEN l_desc_tab( i ).col_type IN ( 2, 100, 101 ) THEN'||unistr('\000a')||
'              dbms_sql.COLUMN_VALUE( l_cursor_id, i, l_sql_result(l_desc_tab(i).col_name).number_values);'||unistr('\000a')||
'            WHEN l_desc_tab( i ).col_type IN ( 12, 178, 179, 180, 181 , ';

s:=s||'231 ) THEN'||unistr('\000a')||
'              dbms_sql.COLUMN_VALUE( l_cursor_id, i, l_sql_result(l_desc_tab(i).col_name).date_values);'||unistr('\000a')||
'            WHEN l_desc_tab( i ).col_type IN ( 1, 8, 9, 96 ) THEN'||unistr('\000a')||
'              dbms_sql.COLUMN_VALUE( l_cursor_id, i, l_sql_result(l_desc_tab(i).col_name).varchar_values);'||unistr('\000a')||
'            WHEN l_desc_tab( i ).col_type = 112 THEN'||unistr('\000a')||
'              dbms_sql.COLUMN_VALUE( l_cursor_id, i, l_sql_';

s:=s||'result(l_desc_tab(i).col_name).clob_values);'||unistr('\000a')||
'            ELSE'||unistr('\000a')||
'              NULL;'||unistr('\000a')||
'          END CASE;          '||unistr('\000a')||
'        END LOOP;'||unistr('\000a')||
'      END IF;'||unistr('\000a')||
'      EXIT WHEN l_row_cnt != c_bulk_size;'||unistr('\000a')||
'    END LOOP;'||unistr('\000a')||
'    dbms_sql.close_cursor(l_cursor_id);'||unistr('\000a')||
'    RETURN convert_template( p_template => p_template'||unistr('\000a')||
'                           , p_replace_name_value => convert_replace( l_sql_result )'||unistr('\000a')||
'                     ';

s:=s||'      , p_enclose_char => p_enclose_char'||unistr('\000a')||
'                           );'||unistr('\000a')||
'    EXCEPTION'||unistr('\000a')||
'      WHEN OTHERS THEN'||unistr('\000a')||
'        IF dbms_sql.is_open(l_cursor_id) THEN'||unistr('\000a')||
'          dbms_sql.close_cursor(l_cursor_id);'||unistr('\000a')||
'        END IF;'||unistr('\000a')||
'        RETURN NULL;'||unistr('\000a')||
'  END convert_template;'||unistr('\000a')||
''||unistr('\000a')||
'END PPTX_CREATOR_PKG;'||unistr('\000a')||
'/';

wwv_flow_api.create_install_script(
  p_id => 9275412645188860 + wwv_flow_api.g_id_offset,
  p_flow_id => wwv_flow.g_flow_id,
  p_install_id=> 8189707789662029 + wwv_flow_api.g_id_offset,
  p_name => 'Install PPTX_CREATOR Package',
  p_sequence=> 80,
  p_script_clob=> s);
end;
 
 
end;
/

 
begin
 
declare
    s varchar2(32767) := null;
    l_clob clob;
    l_length number := 1;
begin
s:=s||'CREATE OR REPLACE PROCEDURE GET_MULTI_PPTX ( p_empnos IN VARCHAR2 )'||unistr('\000a')||
'  AUTHID CURRENT_USER'||unistr('\000a')||
'AS'||unistr('\000a')||
'  l_values pptx_creator_pkg.t_vc_value_tab;'||unistr('\000a')||
'  l_patterns pptx_creator_pkg.t_vc_value_row;'||unistr('\000a')||
'  l_file BLOB;'||unistr('\000a')||
'  l_new_file BLOB;'||unistr('\000a')||
'  i NUMBER := 0;'||unistr('\000a')||
'BEGIN'||unistr('\000a')||
'  SELECT template_content'||unistr('\000a')||
'    INTO l_file'||unistr('\000a')||
'    FROM pptx_template'||unistr('\000a')||
'   WHERE template_name = ''Employees'';'||unistr('\000a')||
''||unistr('\000a')||
'  l_patterns := pptx_creator_pkg.t_vc_value_row( ''#EMPNO';

s:=s||'#'', ''#ENAME#'', ''#JOB#'''||unistr('\000a')||
'                                               , ''#MGR#'', ''#HIREDATE#'', ''#SAL#'''||unistr('\000a')||
'                                               , ''#COMM#'', ''#DEPTNO#'''||unistr('\000a')||
'                                               );'||unistr('\000a')||
'  FOR rec IN (SELECT to_char(EMPNO) empno'||unistr('\000a')||
'                   , ENAME'||unistr('\000a')||
'                   , JOB'||unistr('\000a')||
'                   , (SELECT ename FROM emp par WHERE par.empno = ch.mgr) MGR'||unistr('\000a')||
'     ';

s:=s||'              , to_char(HIREDATE, ''dd.mm.yyyy'') hiredate'||unistr('\000a')||
'                   , to_char(SAL) sal'||unistr('\000a')||
'                   , to_char(COMM) comm'||unistr('\000a')||
'                   , (SELECT dname FROM dept WHERE dept.deptno = ch.deptno) DEPTNO'||unistr('\000a')||
'                FROM EMP ch'||unistr('\000a')||
'              WHERE INSTR(p_empnos, to_char(empno)) > 0)'||unistr('\000a')||
'  LOOP'||unistr('\000a')||
'    i := i + 1;'||unistr('\000a')||
'    l_values(i) := pptx_creator_pkg.t_vc_value_row( rec.empno'||unistr('\000a')||
'            ';

s:=s||'                                      , rec.ename'||unistr('\000a')||
'                                                  , rec.job'||unistr('\000a')||
'                                                  , rec.mgr'||unistr('\000a')||
'                                                  , rec.hiredate'||unistr('\000a')||
'                                                  , rec.sal'||unistr('\000a')||
'                                                  , rec.comm'||unistr('\000a')||
'                                            ';

s:=s||'      , rec.deptno'||unistr('\000a')||
'                                                  );'||unistr('\000a')||
'  END LOOP;'||unistr('\000a')||
'  l_new_file := pptx_creator_pkg.convert_template(l_file, l_patterns, l_values);'||unistr('\000a')||
'  IF l_new_file IS NOT NULL THEN'||unistr('\000a')||
'    OWA_UTIL.mime_header (''application/octet'', FALSE);'||unistr('\000a')||
'    HTP.p (''Content-length: '' || dbms_lob.getlength(l_new_file));'||unistr('\000a')||
'    HTP.p ( ''Content-Disposition: attachment; filename="Employees_'' || to_char(sy';

s:=s||'sdate, ''YYYYMMDD'') || ''.pptx"'');'||unistr('\000a')||
'    OWA_UTIL.http_header_close;'||unistr('\000a')||
'    WPG_DOCLOAD.download_file (l_new_file);'||unistr('\000a')||
'  END IF;'||unistr('\000a')||
'  '||unistr('\000a')||
'END GET_MULTI_PPTX;'||unistr('\000a')||
'/';

wwv_flow_api.create_install_script(
  p_id => 8197330570812567 + wwv_flow_api.g_id_offset,
  p_flow_id => wwv_flow.g_flow_id,
  p_install_id=> 8189707789662029 + wwv_flow_api.g_id_offset,
  p_name => 'Install PPTX Download Procedure',
  p_sequence=> 90,
  p_script_type=> 'INSTALL',
  p_script_clob=> s);
end;
 
 
end;
/

 
begin
 
declare
    s varchar2(32767) := null;
    l_clob clob;
    l_length number := 1;
begin
s:=s||' '||unistr('\000a')||
'begin'||unistr('\000a')||
' '||unistr('\000a')||
'wwv_flow_api.g_varchar2_table := wwv_flow_api.empty_varchar2_table;'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(1) := ''89504E470D0A1A0A0000000D4948445200000040000000400806000000AA6971DE0000001974455874536F6674776172650041646F626520496D616765526561647971C9653C0000032469545874584D4C3A636F6D2E61646F62652E786D700000000000'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(2) := ''3C3F787061636B657420626567696E3D22EFBBBF22206';

s:=s||'9643D2257354D304D7043656869487A7265537A4E54637A6B633964223F3E203C783A786D706D65746120786D6C6E733A783D2261646F62653A6E733A6D6574612F2220783A786D70746B3D2241'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(3) := ''646F626520584D5020436F726520352E332D633031312036362E3134353636312C20323031322F30322F30362D31343A35363A32372020202020202020223E203C7264663A52444620786D6C6E733A7264663D22687474703A2F2F7777772E77332E6F72'';'||unistr('\000a')||
'ww';

s:=s||'v_flow_api.g_varchar2_table(4) := ''672F313939392F30322F32322D7264662D73796E7461782D6E7323223E203C7264663A4465736372697074696F6E207264663A61626F75743D222220786D6C6E733A786D703D22687474703A2F2F6E732E61646F62652E636F6D2F7861702F312E302F22'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(5) := ''20786D6C6E733A786D704D4D3D22687474703A2F2F6E732E61646F62652E636F6D2F7861702F312E302F6D6D2F2220786D6C6E733A73745265663D2268747';

s:=s||'4703A2F2F6E732E61646F62652E636F6D2F7861702F312E302F73547970652F5265736F7572'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(6) := ''6365526566232220786D703A43726561746F72546F6F6C3D2241646F62652050686F746F73686F702043533620284D6163696E746F7368292220786D704D4D3A496E7374616E636549443D22786D702E6969643A45353137384132453939413031314532'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(7) := ''394131354243313034364138393034442220786D704D4';

s:=s||'D3A446F63756D656E7449443D22786D702E6469643A4535313738413246393941303131453239413135424331303436413839303444223E203C786D704D4D3A4465726976656446726F6D207374'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(8) := ''5265663A696E7374616E636549443D22786D702E6969643A4535313738413243393941303131453239413135424331303436413839303444222073745265663A646F63756D656E7449443D22786D702E6469643A45353137384132443939413031314532'';'||unistr('\000a')||
'ww';

s:=s||'v_flow_api.g_varchar2_table(9) := ''39413135424331303436413839303444222F3E203C2F7264663A4465736372697074696F6E3E203C2F7264663A5244463E203C2F783A786D706D6574613E203C3F787061636B657420656E643D2272223F3E158AE958000006B34944415478DAE45B696C'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(10) := ''545514BE33168AB614912E415C0A546B6BDDEA02FEC005A3A0FE51896D845016A3022A92B8F003434C35514413ADC14051448910AAE212E31614AB892049';

s:=s||'45492DB860DA502BA5A805BB8092D6EF6BCF3433C3BCBE77DEBC99BE0927F97267B9F7DC73BE'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(11) := ''77DF7DF7DC7B5EC02458F27272F3515C035C081402E70163804C2043AA75021DC09FC0CF827AE0EBD6B6838D89B42F9000878328AE05EE026E02CE8D536513F019B009A805213DBE24008EE7A27810980B9C95A00BD60CAC07AA40449B2F0880E374F651'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(12) := ''E05E6084498E1C05AA819520A279480880E3C3502C';

s:=s||'019E004E3343235DD2FF0B20E2BFA41100E72F47B1012832FE903DC06C9050975002E038EB2F069E05861B7FC9BF722BBE04227A3D2700CE9F8AE275E04EE36F790B980312BA3D2300CE8F42F13170'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(13) := ''B5490DD90EDC0C120EC74D8038FF05506A524B381FDC604742C0C679CEEE9F03934D6ACA0E21A14B4D804C78EF00B79BD4967781195613E32956AD3233321E91193FD5858FEA8ECEAECE6F1C8F005C7D4E765F016936CAAF07788F5D0C4C076E4BC26A90''';

s:=s||';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(14) := ''ABC0F7804F80DD00E7A86D366D8E3320C328D86E4B009C4F9748ACC046E92E282C8D6A7B3A8A05C063C0688F1D6F075600ABD16F7B54BFDFA1B8CCA6FDAF4009DA1E0BFF31D6155EEAC079234F860811C39E8141AB515602F70341F9FB10F023C0F0F60F'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(15) := ''F91E5ABE72599D0D8C05F22574CE96FF18FDAD0296473B1E26DB1C105020BE555A8E00183E01450390EE8080793068BD83257391C4F54DCA5527C3E';

s:=s||'8295CE6DA2D71519711E86B0ED4F2EA1743DF6F562360A943E72907EC2A88E1756EC6BB10D6E4952D'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(16) := ''22E9E2E37DA11F82612C9EC3ABAAB071988F667A4D5C324F7C8D2440425B8D53393E22205B79E196441020B1FD2C65A757F88800AD2DB3C4E78111300DC8552AB9CA4704686DC9159F070828572AE0E36B918F085814F648752AE5E104DCA86CFC2466E9'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(17) := ''9D7EF15E6CA95436EBF339807BA1489EFD4EA';

s:=s||'50598884E8FFA69C10F3F46C86A6F9CA2597150161B1A59E537E76514D0A69795CDA6048D7E6373B38F23BF1A6DA448020A150D9AC1F43EBF7A0FDB780BFCAE6852187418F884646F0AC4FF7B14750B82CA'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(18) := ''B0B535050838A8A87B060918A968703C0508D0AC0732838AE88F929502048C52D44D0F2A958F4C0102543692806E45FDF1294040BEA26E375782AD8A4088734096D363A721580DF2F8EE88B1DFCC1D98303902F62BFA48F359181C2D572A9CA7EC27';

s:=s||'018D'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(19) := ''CA4E6EF53101B728EB3706958110A54CF280FC36FC695399B259031B69C3DAF12E3A4A8694B998A4770624B949BBC2E3B6F245831D3A26F9EAF310B7DE0501794138C1A5A376EB9AE707553EBAFA552E9CAFA3EFA17BF923179DDE0DE61FF7C1D55F4E5B'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(20) := ''5C34EDF339204A2E504651E1B216589CEC4D1279E6BF08DCE35245116CDE1B0853C86482492E95FD023C0C7CA8495072E9386DE629F44A60A2';

s:=s||'4B35DFC2CEBEA48F81FC80CC8C0C6659C54A86E021E81CE07DE01FA024C66283B9BF4C8DBD037A8603AD9D'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(21) := ''5D9DED1E3B5E00BDF3F1F115E00186B271A85B06FB7E18B805A4833499DDCF8EAACC4CCC6960AC41EA9D6FFACFE7EDB6D2B8C0AA95D999C9CF9F461F4D0FE22C031AE61B70B7AAD8F4275B8FF3884BAE7C27C096E3110448C73C345C1DA3111F770BD068'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(22) := ''83D4E3B118332E9CEE26BD01CC757A7B';

s:=s||'C8307FD5E8CE2A9D0AFD5813FA124D0047C1F7A6FF7C3EE6CC8FC6EBA42ED7DD3B4CE4F9622CF98B8F28B43BA21CF2593222C778E83CF3132E0D5DFD88398082FBA207F7D94FF85861A1603AFEDF827A6D400B3E'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(23) := ''93C0EB6C3A5D830E3FD05A0AFDC7A03FCF789B9B38337A53F784AB870A4C8BABB6DA41019E0BFBCED398A76D3ADD1A87C15B3D74BE5A7C8B10AB24A94CB915AC1E339740D9EEB0FA936466E6A385F303DFFCD805F0CA6F763AF9C5B0832B4E2';

s:=s||'FB6E1F7C9'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(24) := ''D0EF704480745E22F778468CBF6BA0ACDC245824E9EAEF38D5F0759CC9B0B7DE6A4B2CA64803CE053D1621712A2450D2F60A2BE78DDD0C8E865B502CB4F8FB4D9030C3E7042C141F2CC569B6F8625977C792B781E76579D9EBA35BE021D8631BB16ADE17'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(25) := ''988D629DB1DE733B24ABBEC332B2B8CFC0202B7F90FCBE4410C067FCFCD0A2CD3302C420BE0E576374E934A3934800F736CAD05FADD30';

s:=s||'6AABD3D51CCF4D82F7D78BFD3A6528DF36A0284041E3F4F95E77EA70F1CA70D9CA3A68A6D2A09C439499D89E229'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(26) := ''D3FFB2A495AEB130EC804BFD39C6FAB497132EDF615A06FD2D6E7DF0E4CD5118CA9075A9EC0944275BE6BA7DCB137A9900D9166392DB08AC0885E8F188A7EF0ECB88A89011C1587E138C9C19A7CE8D422C83B4F50CADE3B9E22710D0DBDB6B4E6639E909'';'||unistr('\000a')||
'wwv_flow_api.g_varchar2_table(27) := ''F85F800100547B2280E6553E4D0';

s:=s||'000000049454E44AE426082'';'||unistr('\000a')||
' '||unistr('\000a')||
'end;'||unistr('\000a')||
'/'||unistr('\000a')||
''||unistr('\000a')||
'declare'||unistr('\000a')||
'    l_name   varchar2(255);'||unistr('\000a')||
'begin'||unistr('\000a')||
'    l_name := ''GitHub-Mark-64px.png'';'||unistr('\000a')||
' '||unistr('\000a')||
'  wwv_flow_api.create_or_remove_file('||unistr('\000a')||
'     p_name=> l_name,'||unistr('\000a')||
'     p_varchar2_table=> wwv_flow_api.g_varchar2_table,'||unistr('\000a')||
'     p_mimetype=> ''image/png'','||unistr('\000a')||
'     p_location=> ''APPLICATION'','||unistr('\000a')||
'     p_nlang=> ''0'','||unistr('\000a')||
'     p_notes=> ''GitHub Logo'','||unistr('\000a')||
'     p_mode=> ''CREATE_OR_REPLACE'','||unistr('\000a')||
'     p_type=> ''IMA';

s:=s||'GE'');'||unistr('\000a')||
' '||unistr('\000a')||
'end;'||unistr('\000a')||
'/'||unistr('\000a')||
''||unistr('\000a')||
'';

wwv_flow_api.create_install_script(
  p_id => 9850332543431079 + wwv_flow_api.g_id_offset,
  p_flow_id => wwv_flow.g_flow_id,
  p_install_id=> 8189707789662029 + wwv_flow_api.g_id_offset,
  p_name => 'Install GitHub Logo',
  p_sequence=> 100,
  p_script_type=> 'INSTALL',
  p_script_clob=> s);
end;
 
 
end;
/

--application/deployment/checks
prompt  ...application deployment checks
--
 
begin
 
null;
 
end;
/

--application/deployment/buildoptions
prompt  ...application deployment build options
--
 
begin
 
null;
 
end;
/

prompt  ...post import process
 
begin
 
wwv_flow_api.post_import_process(p_flow_id => wwv_flow.g_flow_id);
null;
 
end;
/

--application/end_environment
commit;
commit;

--export elap=           0.9354
