Skip to main content

Hello everyone,

I'm working on a workflow where I need to:

  • Fetch the results of a quick report,
  • Convert the output into a CSV file, and
  • Send that file as an email attachment using a custom projection.

I've already created a custom projection that uses Command_Sys.Mail() (Overload 1), as suggested in some other forum posts. The process works well overall, the report is fetched and converted to CSV, but I’ve hit a limitation: the VARCHAR2 parameter is too small to hold the full content of the report. Because of this, I can’t reliably use the Mail procedure in its current form.

I considered switching to Command_Sys.Mail() (Overload 2), which supports CLOB input for both the message body and attachments. However, it seems that this overload cannot be added as an action in a custom projection due to its use of CLOB parameters.

My questions are:

  • Is there a recommended way to use the first Mail function with a reference to a local file (since it supports file paths when the input doesn’t contain an equals sign)?
  • If so, how and where could I store the temporary file on the server so it can be referenced reliably during execution?
  • Alternatively, is there a workaround to invoke the second Mail overload (with CLOB) from a projection or PL/SQL context?

Any guidance or best practices would be greatly appreciated.

Thank you in advance for your help!

I don’t have a direct answer, but the following info may help you to come up with solution.

In Apps 10 I had a similar requirement. i.e. Along with the Purchase Order Email all document attachment related files to be attached and sent.

I was able to achieve it by writing an event action, where it downloads all the files attached to the PO in to the Application Server and then attaching it to the email.
I used the Document Management download path as follows;

download_path_        VARCHAR2(4000) := ifsapp.DOCMAN_DEFAULT_API.Get_Default_Value_('BatchTransferHandler', 'FILE_PATH_FOR_BATCH_TRANSFER')

​@malik.sally Thanks for your input - it actually pointed me in the right direction, and I've now resolved the issue.

Initially, I added the Quick Report SQL as an IAL object and scheduled it to run daily. Then, I created a custom event that listens for changes on the IAL_OBJECT_TAB entity. I filtered it to react only when the specific IAL object is updated or replicated and status is 'OK'. The event then triggers an online SQL block that sends out a CSV file as an email attachment to the recipients.

Here's the SQL I used for the initial CSV-based approach:
 

DECLARE
  l_alias     VARCHAR2(50) := 'Sender name';
  l_subject   VARCHAR2(50) := 'Subject name';
  l_receivers VARCHAR2(200) := 'receiver@example.com';
  l_msg       CLOB := TO_CLOB('Message...');

  l_bom_str   VARCHAR2(3) := CHR(239) || CHR(187) || CHR(191);
  l_msg_utf   CLOB;
  l_csv       CLOB;
  l_csv_utf   CLOB;

  -- Proper attachment structure
  l_attachments COMMAND_SYS.attachment_arr;
  l_rec         COMMAND_SYS.attachment_rec;

  -- Cursor and line variables
  TYPE rc IS REF CURSOR;
  c_csv  rc;
  v_line VARCHAR2(32767);
  v_rn   NUMBER;
BEGIN
  -- Build CSV
  DBMS_LOB.CREATETEMPORARY(l_csv, TRUE, DBMS_LOB.SESSION);
  OPEN c_csv FOR
    WITH results AS
     (SELECT * FROM ifsinfo.ial_test_object),
    hdr AS
     (SELECT LISTAGG('"' || REPLACE(column_name, '"', '""') || '"', ';') WITHIN GROUP(ORDER BY column_id) AS line,
             0 AS rn
        FROM all_tab_columns
       WHERE owner = 'IFSINFO'
         AND table_name = 'IAL_TEST_OBJECT'),
    data_lines AS
     (SELECT '"' || REPLACE(NVL(CLM_1, ''), '"', '""') || '"' || ';' || '"' ||
             REPLACE(NVL(CLM_2, ''), '"', '""') || '"' || ';' || '"' ||
             REPLACE(NVL(CLM_3, ''), '"', '""') || '"' || ';' || '"' ||
             REPLACE(NVL(CLM_4, ''), '"', '""') || '"' || ';' || '"' ||
             REPLACE(TO_CHAR(CLM_5), '"', '""') || '"' AS line,
             ROW_NUMBER() OVER(ORDER BY NULL) AS rn
        FROM results),
    all_lines AS
     (SELECT line, rn FROM hdr UNION ALL SELECT line, rn FROM data_lines)
    SELECT line, rn FROM all_lines ORDER BY rn;

  LOOP
    FETCH c_csv
      INTO v_line, v_rn;
    EXIT WHEN c_csv%NOTFOUND;
    DBMS_LOB.APPEND(l_csv, v_line || CHR(13) || CHR(10));
  END LOOP;
  CLOSE c_csv;

  -- Prepend UTF-8 BOM
  l_csv_utf := TO_CLOB(l_bom_str) || l_csv;

  -- Prepend UTF-8 BOM
  l_msg_utf := TO_CLOB(l_bom_str) || l_msg;

  -- Prepare attachment record
  l_rec.clob_ := l_csv_utf;
  l_attachments('Excel-Export-' || TO_CHAR(SYSDATE, 'DD-MM-YYYY') || '.csv') := l_rec;

  COMMAND_SYS.Mail(sender_      => 'IFSAPP',
                   from_        => l_alias,
                   to_list_     => l_receivers,
                   cc_list_     => NULL,
                   bcc_list_    => NULL,
                   subject_     => l_subject,
                   text_        => l_msg_utf,
                   attachments_ => l_attachments,
                   mail_sender_ => NULL);
END;

 

However, this approach had a limitation: Excel didn't handle UTF-8 encoding properly when opening the CSV attachment, which was a blocker in my case.

 

So I looked for a better solution and discovered Dat_Excel_Builder_Util_API, which is used internally for creating Excel documents for migration objects. Unfortunately, the key procedure Query_To_Sheet is not exposed for direct use since it's nested inside the Create_Excel_Workbook procedure.
 

To work around this, I mimicked the logic and dependencies of the original code and built my own version that can generate a proper Excel .xlsx file from any SQL query. This Excel file is then sent as an email attachment. Here's the improved version that builds and sends a real Excel attachment:

DECLARE
  l_sender_name      VARCHAR2(50) := 'Sender name';
  l_subject      VARCHAR2(50) := 'Subject name';
  l_receiver_list      VARCHAR2(100) := 'test@example.com';
  l_message      VARCHAR2(400) := 'Message...';
  l_sql_to_execute      VARCHAR2(400) := 'SELECT * FROM ifsinfo.IAL_TEST_OBJECT';
  l_sheet_name      VARCHAR2(50) := 'Sheet 1';
  l_attachment_name      VARCHAR2(50) := 'Excel-Export.xlsx';

  workbook_     Dat_Excel_Builder_Util_API.tp_book;
  l_excel_file  BLOB;
  l_attachments COMMAND_SYS.attachment_arr;
  l_rec         COMMAND_SYS.attachment_rec;

  PROCEDURE Set_Col_Width(p_sheet_  PLS_INTEGER,
                          p_col_    PLS_INTEGER,
                          p_format_ VARCHAR2) IS
    t_width_  NUMBER;
    t_nr_chr_ PLS_INTEGER;
  BEGIN
    IF p_format_ IS NULL THEN
      RETURN;
    END IF;
    IF INSTR(p_format_, ';') > 0 THEN
      t_nr_chr_ := LENGTH(TRANSLATE(SUBSTR(p_format_,
                                           1,
                                           INSTR(p_format_, ';') - 1),
                                    'a\"',
                                    'a'));
    ELSE
      t_nr_chr_ := LENGTH(TRANSLATE(p_format_, 'a\"', 'a'));
    END IF;
    t_width_ := TRUNC((t_nr_chr_ * 7 + 5) / 7 * 256) / 256;
    IF workbook_.sheets(p_sheet_).widths.exists(p_col_) THEN
      workbook_.sheets(p_sheet_).widths(p_col_) := GREATEST(workbook_.sheets(p_sheet_).widths(p_col_),
                                                            t_width_);
    ELSE
      workbook_.sheets(p_sheet_).widths(p_col_) := GREATEST(t_width_, 8.43);
    END IF;
  END Set_Col_Width;

  FUNCTION Get_X_FId(p_sheet_      PLS_INTEGER,
                     p_col_        PLS_INTEGER,
                     p_row_        PLS_INTEGER,
                     p_num_fmt_id_ PLS_INTEGER := NULL,
                     p_font_id_    PLS_INTEGER := NULL,
                     p_fill_id_    PLS_INTEGER := NULL,
                     p_border_id_  PLS_INTEGER := NULL,
                     p_alignment_  Dat_Excel_Builder_Util_API.tp_alignment := NULL)
    RETURN VARCHAR2 IS
    t_cnt_    PLS_INTEGER;
    t_xf_id_  PLS_INTEGER;
    t_xf_     Dat_Excel_Builder_Util_API.tp_XF_fmt;
    t_col_xf_ Dat_Excel_Builder_Util_API.tp_XF_fmt;
    t_row_xf_ Dat_Excel_Builder_Util_API.tp_XF_fmt;
  BEGIN
    IF workbook_.sheets(p_sheet_).col_fmts.exists(p_col_) THEN
      t_col_xf_ := workbook_.sheets(p_sheet_).col_fmts(p_col_);
    END IF;
    IF workbook_.sheets(p_sheet_).row_fmts.exists(p_row_) THEN
      t_row_xf_ := workbook_.sheets(p_sheet_).row_fmts(p_row_);
    END IF;
    t_xf_.numFmtId  := COALESCE(p_num_fmt_id_,
                                t_col_xf_.numFmtId,
                                t_row_xf_.numFmtId,
                                0);
    t_xf_.fontId    := COALESCE(p_font_id_,
                                t_col_xf_.fontId,
                                t_row_xf_.fontId,
                                0);
    t_xf_.fillId    := COALESCE(p_fill_id_,
                                t_col_xf_.fillId,
                                t_row_xf_.fillId,
                                0);
    t_xf_.borderId  := COALESCE(p_border_id_,
                                t_col_xf_.borderId,
                                t_row_xf_.borderId,
                                0);
    t_xf_.alignment := COALESCE(p_alignment_,
                                t_col_xf_.alignment,
                                t_row_xf_.alignment);
    IF (t_xf_.numFmtId + t_xf_.fontId + t_xf_.fillId + t_xf_.borderId = 0 AND
       t_xf_.alignment.vertical IS NULL AND
       t_xf_.alignment.horizontal IS NULL AND
       NOT NVL(t_xf_.alignment.wrapText, FALSE)) THEN
      RETURN '';
    END IF;
    IF t_xf_.numFmtId > 0 THEN
      Set_Col_Width(p_sheet_,
                    p_col_,
                    workbook_.numFmts(workbook_.numFmtIndexes(t_xf_.numFmtId)).formatCode);
    END IF;
    t_cnt_ := workbook_.cellXfs.count();
    FOR i in 1 .. t_cnt_ LOOP
      IF (workbook_.cellXfs(i)
         .numFmtId = t_xf_.numFmtId AND workbook_.cellXfs(i)
         .fontId = t_xf_.fontId AND workbook_.cellXfs(i)
         .fillId = t_xf_.fillId AND workbook_.cellXfs(i)
         .borderId = t_xf_.borderId AND
          NVL(workbook_.cellXfs(i).alignment.vertical, 'x') =
          nvl(t_xf_.alignment.vertical, 'x') AND
          NVL(workbook_.cellXfs(i).alignment.horizontal, 'x') =
          nvl(t_xf_.alignment.horizontal, 'x') AND
          NVL(workbook_.cellXfs(i).alignment.wrapText, FALSE) =
          nvl(t_xf_.alignment.wrapText, FALSE)) THEN
        t_xf_id_ := i;
        EXIT;
      END IF;
    END LOOP;
    IF t_xf_id_ IS NULL THEN
      t_cnt_ := t_cnt_ + 1;
      t_xf_id_ := t_cnt_;
      workbook_.cellXfs(t_cnt_) := t_xf_;
    END IF;
    RETURN 's="' || t_xf_id_ || '"';
  END Get_X_FId;

  FUNCTION Add_String(p_string_ VARCHAR2) RETURN PLS_INTEGER IS
    t_cnt_ PLS_INTEGER;
  BEGIN
    IF workbook_.strings.exists(p_string_) THEN
      t_cnt_ := workbook_.strings(p_string_);
    ELSE
      t_cnt_ := workbook_.strings.count();
      workbook_.str_ind(t_cnt_) := p_string_;
      workbook_.strings(NVL(p_string_, '')) := t_cnt_;
    END IF;
    workbook_.str_cnt := workbook_.str_cnt + 1;
    RETURN t_cnt_;
  END Add_String;

  FUNCTION Get_Font(p_name_      VARCHAR2,
                    p_family_    PLS_INTEGER := 2,
                    p_fontsize_  NUMBER := 11,
                    p_theme_     PLS_INTEGER := 1,
                    p_underline_ BOOLEAN := FALSE,
                    p_italic_    BOOLEAN := FALSE,
                    p_bold_      BOOLEAN := FALSE,
                    p_rgb_       VARCHAR2 := NULL) RETURN PLS_INTEGER IS
    t_ind_ PLS_INTEGER;
  BEGIN
    IF workbook_.fonts.count() > 0 THEN
      FOR f IN 0 .. workbook_.fonts.count() - 1 LOOP
        IF (workbook_.fonts(f)
           .name = p_name_ AND workbook_.fonts(f).family = p_family_ AND workbook_.fonts(f)
           .fontsize = p_fontsize_ AND workbook_.fonts(f).theme = p_theme_ AND workbook_.fonts(f)
           .underline = p_underline_ AND workbook_.fonts(f)
           .italic = p_italic_ AND workbook_.fonts(f).bold = p_bold_ AND
            (workbook_.fonts(f).rgb = p_rgb_ OR (workbook_.fonts(f).rgb IS NULL AND
              p_rgb_ IS NULL))) THEN
          RETURN f;
        END IF;
      END LOOP;
    END IF;
    t_ind_ := workbook_.fonts.count();
    workbook_.fonts(t_ind_).name := p_name_;
    workbook_.fonts(t_ind_).family := p_family_;
    workbook_.fonts(t_ind_).fontsize := p_fontsize_;
    workbook_.fonts(t_ind_).theme := p_theme_;
    workbook_.fonts(t_ind_).underline := p_underline_;
    workbook_.fonts(t_ind_).italic := p_italic_;
    workbook_.fonts(t_ind_).bold := p_bold_;
    workbook_.fonts(t_ind_).rgb := p_rgb_;
    RETURN t_ind_;
  END Get_Font;

  FUNCTION Get_NumFmt(p_format_ VARCHAR2 := NULL) RETURN PLS_INTEGER IS
    t_cnt_        PLS_INTEGER;
    t_num_fmt_id_ PLS_INTEGER;
  BEGIN
    IF p_format_ IS NULL THEN
      RETURN 0;
    END IF;
    t_cnt_ := workbook_.numFmts.count();
    FOR i IN 1 .. t_cnt_ LOOP
      IF workbook_.numFmts(i).formatCode = p_format_ THEN
        t_num_fmt_id_ := workbook_.numFmts(i).numFmtId;
        EXIT;
      END IF;
    END LOOP;
    IF t_num_fmt_id_ IS NULL THEN
      t_num_fmt_id_ := CASE
                         WHEN t_cnt_ = 0 THEN
                          164
                         ELSE
                          workbook_.numFmts(t_cnt_).numFmtId + 1
                       END;
      t_cnt_ := t_cnt_ + 1;
      workbook_.numFmts(t_cnt_).numFmtId := t_num_fmt_id_;
      workbook_.numFmts(t_cnt_).formatCode := p_format_;
      workbook_.numFmtIndexes(t_num_fmt_id_) := t_cnt_;
    END IF;
    RETURN t_num_fmt_id_;
  END Get_NumFmt;

  PROCEDURE Cell(p_col_        PLS_INTEGER,
                 p_row_        PLS_INTEGER,
                 p_value_      NUMBER,
                 p_num_fmt_id_ PLS_INTEGER := NULL,
                 p_font_id_    PLS_INTEGER := NULL,
                 p_fill_id_    PLS_INTEGER := NULL,
                 p_border_id_  PLS_INTEGER := NULL,
                 p_alignment_  Dat_Excel_Builder_Util_API.tp_alignment := NULL,
                 p_sheet_      PLS_INTEGER := NULL) IS
    t_sheet_ PLS_INTEGER := NVL(p_sheet_, workbook_.sheets.count());
  BEGIN
    IF p_value_ IS NOT NULL THEN
      workbook_.sheets(t_sheet_).rows(p_row_)(p_col_).value_ := p_value_;
      workbook_.sheets(t_sheet_).rows(p_row_)(p_col_).style := NULL;
      workbook_.sheets(t_sheet_).rows(p_row_)(p_col_).style := Get_X_FId(t_sheet_,
                                                                         p_col_,
                                                                         p_row_,
                                                                         p_num_fmt_id_,
                                                                         p_font_id_,
                                                                         p_fill_id_,
                                                                         p_border_id_,
                                                                         p_alignment_);
    ELSIF workbook_.sheets(t_sheet_).rows(p_row_).exists(p_col_) THEN
      workbook_.sheets(t_sheet_).rows(p_row_).delete(p_col_);
    END IF;
  END Cell;

  PROCEDURE Cell(p_col_        PLS_INTEGER,
                 p_row_        PLS_INTEGER,
                 p_value_      VARCHAR2,
                 p_num_fmt_id_ PLS_INTEGER := NULL,
                 p_font_id_    PLS_INTEGER := NULL,
                 p_fill_id_    PLS_INTEGER := NULL,
                 p_border_id_  PLS_INTEGER := NULL,
                 p_alignment_  Dat_Excel_Builder_Util_API.tp_alignment := NULL,
                 p_sheet_      PLS_INTEGER := NULL) IS
    t_sheet_     pls_integer := NVL(p_sheet_, workbook_.sheets.count());
    t_alignment_ Dat_Excel_Builder_Util_API.tp_alignment := p_alignment_;
  BEGIN
    IF p_value_ IS NOT NULL THEN
      workbook_.sheets(t_sheet_).rows(p_row_)(p_col_).value_ := Add_String(Dat_Excel_Builder_Util_API.Clean_String(p_value_));
      IF t_alignment_.wrapText IS NULL AND instr(p_value_, CHR(13)) > 0 THEN
        t_alignment_.wrapText := TRUE;
      END IF;
      workbook_.sheets(t_sheet_).rows(p_row_)(p_col_).style := 't="s" ' ||
                                                               Get_X_FId(t_sheet_,
                                                                         p_col_,
                                                                         p_row_,
                                                                         p_num_fmt_id_,
                                                                         p_font_id_,
                                                                         p_fill_id_,
                                                                         p_border_id_,
                                                                         t_alignment_);
    ELSIF workbook_.sheets(t_sheet_).rows(p_row_).exists(p_col_) THEN
      workbook_.sheets(t_sheet_).rows(p_row_).delete(p_col_);
    END IF;
  END Cell;

  PROCEDURE Cell(p_col_        PLS_INTEGER,
                 p_row_        PLS_INTEGER,
                 p_value_      DATE,
                 p_num_fmt_id_ PLS_INTEGER := NULL,
                 p_font_id_    PLS_INTEGER := NULL,
                 p_fill_id_    PLS_INTEGER := NULL,
                 p_border_id_  PLS_INTEGER := NULL,
                 p_alignment_  Dat_Excel_Builder_Util_API.tp_alignment := NULL,
                 p_sheet_      PLS_INTEGER := NULL) IS
    t_num_fmt_id_ PLS_INTEGER := p_num_fmt_id_;
    t_sheet_      PLS_INTEGER := NVL(p_sheet_, workbook_.sheets.count());
  BEGIN
    workbook_.sheets(t_sheet_).rows(p_row_)(p_col_).value_ := p_value_ -
                                                              to_date('01-01-1904',
                                                                      'DD-MM-YYYY');
    IF t_num_fmt_id_ IS NULL AND NOT
        (workbook_.sheets(t_sheet_).col_fmts.exists(p_col_) AND workbook_.sheets(t_sheet_).col_fmts(p_col_).numFmtId IS NOT NULL) AND NOT
        (workbook_.sheets(t_sheet_).row_fmts.exists(p_row_) AND workbook_.sheets(t_sheet_).row_fmts(p_row_).numFmtId IS NOT NULL) THEN
      t_num_fmt_id_ := Get_NumFmt('dd/mm/yyyy');
    END IF;
    workbook_.sheets(t_sheet_).rows(p_row_)(p_col_).style := Get_X_FId(t_sheet_,
                                                                       p_col_,
                                                                       p_row_,
                                                                       t_num_fmt_id_,
                                                                       p_font_id_,
                                                                       p_fill_id_,
                                                                       p_border_id_,
                                                                       p_alignment_);
  END Cell;

  FUNCTION Get_Fill(p_pattern_type_ VARCHAR2, p_fg_rgb_ VARCHAR2 := NULL)
    RETURN PLS_INTEGER IS
    t_ind_ PLS_INTEGER;
  BEGIN
    IF workbook_.fills.count() > 0 THEN
      FOR f IN 0 .. workbook_.fills.count() - 1 LOOP
        IF (workbook_.fills(f)
           .patternType = p_pattern_type_ AND
            NVL(workbook_.fills(f).fgRGB, 'x') = nvl(upper(p_fg_rgb_), 'x')) THEN
          RETURN f;
        END IF;
      END LOOP;
    END IF;
    t_ind_ := workbook_.fills.count();
    workbook_.fills(t_ind_).patternType := p_pattern_type_;
    workbook_.fills(t_ind_).fgRGB := upper(p_fg_rgb_);
    RETURN t_ind_;
  END Get_Fill;

  FUNCTION Get_Border(p_top_    VARCHAR2 := 'thin',
                      p_bottom_ VARCHAR2 := 'thin',
                      p_left_   VARCHAR2 := 'thin',
                      p_right_  VARCHAR2 := 'thin') RETURN PLS_INTEGER IS
    t_ind_ PLS_INTEGER;
  BEGIN
    IF workbook_.borders.count() > 0 THEN
      FOR b IN 0 .. workbook_.borders.count() - 1 LOOP
        IF (NVL(workbook_.borders(b).top, 'x') = NVL(p_top_, 'x') AND
           NVL(workbook_.borders(b).bottom, 'x') = NVL(p_bottom_, 'x') AND
           NVL(workbook_.borders(b).left, 'x') = NVL(p_left_, 'x') AND
           NVL(workbook_.borders(b).right, 'x') = NVL(p_right_, 'x')) THEN
          RETURN b;
        END IF;
      END LOOP;
    END IF;
    t_ind_ := workbook_.borders.count();
    workbook_.borders(t_ind_).top := p_top_;
    workbook_.borders(t_ind_).bottom := p_bottom_;
    workbook_.borders(t_ind_).left := p_left_;
    workbook_.borders(t_ind_).right := p_right_;
    RETURN t_ind_;
  END Get_Border;

  PROCEDURE New_Sheet(p_sheetname_ VARCHAR2 := NULL) IS
    t_nr_  PLS_INTEGER := workbook_.sheets.count() + 1;
    t_ind_ PLS_INTEGER;
  BEGIN
    workbook_.sheets(t_nr_).name := NVL(DBMS_XMLGEN.CONVERT(TRANSLATE(p_sheetname_,
                                                                      'a/\ ]*:?',
                                                                      'a')),
                                        'Sheet' || t_nr_);
    IF workbook_.strings.count() = 0 THEN
      workbook_.str_cnt := 0;
    END IF;
    IF workbook_.fonts.count() = 0 THEN
      t_ind_ := Get_Font('Calibri');
    END IF;
    IF workbook_.fills.count() = 0 THEN
      t_ind_ := Get_Fill('none');
      t_ind_ := Get_Fill('gray125');
    END IF;
    IF workbook_.borders.count() = 0 THEN
      t_ind_ := Get_Border('', '', '', '');
    END IF;
  END New_Sheet;

  PROCEDURE Query_To_Sheet(p_sql_            VARCHAR2,
                           p_column_headers_ BOOLEAN := TRUE,
                           p_directory_      VARCHAR2 := NULL,
                           p_filename_       VARCHAR2 := NULL,
                           p_sheet_          PLS_INTEGER := NULL,
                           p_sheetname_      VARCHAR2 := NULL) IS
    t_sheet_     PLS_INTEGER;
    t_c_         INTEGER;
    t_col_cnt_   INTEGER;
    t_desc_tab_  DBMS_SQL.DESC_TAB2;
    d_tab_       DBMS_SQL.DATE_TABLE;
    n_tab_       DBMS_SQL.NUMBER_TABLE;
    v_tab_       DBMS_SQL.VARCHAR2_TABLE;
    t_bulk_size_ PLS_INTEGER := 200;
    t_r_         INTEGER;
    t_cur_row_   PLS_INTEGER;
  BEGIN
    IF p_sheet_ IS NULL THEN
      New_Sheet(p_sheetname_);
    END IF;
    t_c_ := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(t_c_, p_sql_, DBMS_SQL.NATIVE);
    DBMS_SQL.DESCRIBE_COLUMNS2(t_c_, t_col_cnt_, t_desc_tab_);
    FOR c IN 1 .. t_col_cnt_ LOOP
      IF p_column_headers_ THEN
        Cell(c, 1, t_desc_tab_(c).col_name, p_sheet_ => t_sheet_);
      END IF;
      CASE
        WHEN t_desc_tab_(c).col_type IN (2, 100, 101) THEN
          DBMS_SQL.DEFINE_ARRAY(t_c_, c, n_tab_, t_bulk_size_, 1);
        WHEN t_desc_tab_(c).col_type IN (12, 178, 179, 180, 181, 231) THEN
          DBMS_SQL.DEFINE_ARRAY(t_c_, c, d_tab_, t_bulk_size_, 1);
        WHEN t_desc_tab_(c).col_type in (1, 8, 9, 96, 112) THEN
          DBMS_SQL.DEFINE_ARRAY(t_c_, c, v_tab_, t_bulk_size_, 1);
        ELSE
          NULL;
      END CASE;
    END LOOP;
    t_cur_row_ := CASE
                    WHEN p_column_headers_ THEN
                     2
                    ELSE
                     1
                  END;
    t_sheet_   := NVL(p_sheet_, workbook_.sheets.count());
    t_r_       := DBMS_SQL.EXECUTE(t_c_);
    LOOP
      t_r_ := DBMS_SQL.FETCH_ROWS(t_c_);
      IF t_r_ > 0 THEN
        FOR c IN 1 .. t_col_cnt_ LOOP
          CASE
            WHEN t_desc_tab_(c).col_type IN (2, 100, 101) THEN
              DBMS_SQL.COLUMN_VALUE(t_c_, c, n_tab_);
              FOR i IN 0 .. t_r_ - 1 LOOP
                IF n_tab_(i + n_tab_.first()) IS NOT NULL THEN
                  Cell(c,
                       t_cur_row_ + i,
                       n_tab_(i + n_tab_.first()),
                       p_sheet_ => t_sheet_);
                END IF;
              END LOOP;
              n_tab_.delete;
            WHEN t_desc_tab_(c).col_type in (12, 178, 179, 180, 181, 231) THEN
              DBMS_SQL.COLUMN_VALUE(t_c_, c, d_tab_);
              FOR i IN 0 .. t_r_ - 1 LOOP
                IF d_tab_(i + d_tab_.first()) IS NOT NULL THEN
                  Cell(c,
                       t_cur_row_ + i,
                       d_tab_(i + d_tab_.first()),
                       p_sheet_ => t_sheet_);
                END IF;
              END LOOP;
              d_tab_.delete;
            WHEN t_desc_tab_(c).col_type in (1, 8, 9, 96, 112) THEN
              DBMS_SQL.COLUMN_VALUE(t_c_, c, v_tab_);
              FOR i IN 0 .. t_r_ - 1 LOOP
                IF v_tab_(i + v_tab_.first()) IS NOT NULL THEN
                  Cell(c,
                       t_cur_row_ + i,
                       v_tab_(i + v_tab_.first()),
                       p_sheet_ => t_sheet_);
                END IF;
              END LOOP;
              v_tab_.delete;
            ELSE
              NULL;
          END CASE;
        END LOOP;
      END IF;
      EXIT WHEN t_r_ != t_bulk_size_;
      t_cur_row_ := t_cur_row_ + t_r_;
    END LOOP;
    DBMS_SQL.CLOSE_CURSOR(t_c_);
  EXCEPTION
    WHEN OTHERS THEN
      IF DBMS_SQL.IS_OPEN(t_c_) THEN
        DBMS_SQL.CLOSE_CURSOR(t_c_);
      END IF;
      RAISE;
  END Query_To_Sheet;

BEGIN
  Query_To_Sheet(p_sql_       => l_sql_to_execute,
                 p_sheetname_ => l_sheet_name);
  l_excel_file := Dat_Excel_Builder_Util_API.Finish(workbook_);
  l_rec.blob_ := l_excel_file;
  l_attachments(l_attachment_name) := l_rec;
  COMMAND_SYS.Mail(sender_      => l_sender_name,
                   from_        => NULL,
                   to_list_     => l_receiver_list,
                   cc_list_     => NULL,
                   bcc_list_    => NULL,
                   subject_     => l_subject,
                   text_        => TO_CLOB(l_message),
                   attachments_ => l_attachments,
                   mail_sender_ => NULL);
END;

 

Hope this helps anyone facing a similar issue!

Didn’t realize your requirement was to send a result set from an sql query as a .csv. If that is the case… in Apps 10 you can create an application server task for a quick report which will be scheduled to run. May be the same in Cloud? Or simply schedule a quick report from Order Report  screen?

 

Reply


Terms & ConditionsCookie settings