Getting Table Output From Procedure

Question

I have a working procedure that outputs in a, what I would consider, scripting output using the DBMS_OUTPUT.put_line function.

The procedure works perfectly, but I can only view the results using the DBMS output line in my Toad system. I can transfer the data over to excel using a CSV, however this process takes some time and ideally I'd like to be able to only use excel in order to run the report.

I am very capable of running macros and have connected to my Oracle database before to run queries strictly from an Excel workbook. The issue becomes harvesting the DBMS output line format script response; I can only harvest the output in the "data grid" format- a table format, something like the output select * from table1.

My question then is, does Oracle have a way to output a collection in tabular format into the datagrid format from a procedure?

Here's an example of the procedure:

LOOP
      SELECT (CAST (MULTISET (SELECT DECODE (mike.flg, '1', 'No Trailer', DECODE(mike.prod, '1', 'Waiting', mike.txt))
                                FROM (  SELECT *
                                          FROM (  SELECT *
                                                    FROM hist
                                                   WHERE     id = v_array.seq_id
                                                         AND txt IS NOT NULL
                                                         AND txt <> 'NOW'
                                                         AND flg = 1
                                                ORDER BY id DESC) b                                         WHERE ROWNUM <= 1                                      ORDER BY ROWNUM DESC) m) AS wbr.data_1_col_ty))
        INTO v_col
        FROM DUAL;

        --select * from v_col;

      IF v_col IS NOT NULL AND v_col.COUNT > 0
      THEN
         FOR v_index IN v_col.FIRST .. v_col.LAST
         LOOP
                 DBMS_OUTPUT.put_line ();
         END LOOP;
      END IF;
   END LOOP;

I'd like to be able to output the results from v_col in tabular format as I go or all at once at the very end of the procedure. I know v_array.seq_id gets matched up within the loop, but is there a way to output in tabular format at the end? Being able to do this should allow me to call this script through Excel Macro's and harvest the results.

Could you provide a couple sample values of hist.txt?

troy
– troy

2014-09-15 16:45:35 +00:00
Commented Sep 15, 2014 at 16:45 — troy
– troy, Commented Sep 15, 2014 at 16:45
How is v_col defined?

Alex Poole
– Alex Poole

2014-09-15 16:55:52 +00:00
Commented Sep 15, 2014 at 16:55 — Alex Poole
– Alex Poole, Commented Sep 15, 2014 at 16:55

Tomasz Żuk · Accepted Answer · 2014-09-15 17:58:44Z

CREATE FUNCTION some_fun
  RETURN wbr.data_1_col_ty PIPELINED
IS
LOOP
  SELECT (CAST (MULTISET (SELECT DECODE (mike.flg, '1', 'No Trailer', DECODE(mike.prod, '1', 'Waiting', mike.txt))
                            FROM (  SELECT *
                                      FROM (  SELECT *
                                                FROM hist
                                               WHERE     id = v_array.seq_id
                                                     AND txt IS NOT NULL
                                                     AND txt <> 'NOW'
                                                     AND flg = 1
                                            ORDER BY id DESC) b                                         WHERE ROWNUM <= 1                                      ORDER BY ROWNUM DESC) m) AS wbr.data_1_col_ty))
    INTO v_col
    FROM DUAL;

    --select * from v_col;

  IF v_col IS NOT NULL AND v_col.COUNT > 0
  THEN
     FOR v_index IN v_col.FIRST .. v_col.LAST
     LOOP
        PIPE ROW(v_col(v_index));
     END LOOP;
  END IF;
END LOOP;
END some_fun;

Then you can just do

select * from table(some_fun())

in your Excel file.

BTW, instead of selecting and casting a multiset, you may use PL/SQL bulk binding feature:

  SELECT DECODE (mike.flg, '1', 'No Trailer', DECODE(mike.prod, '1', 'Waiting', mike.txt))
  BULK COLLECT INTO v_col
                            FROM (  SELECT *
                                      FROM (  SELECT *
                                                FROM hist
                                               WHERE     id = v_array.seq_id
                                                     AND txt IS NOT NULL
                                                     AND txt <> 'NOW'
                                                     AND flg = 1
                                            ORDER BY id DESC) b                                         WHERE ROWNUM <= 1                                      ORDER BY ROWNUM DESC) m;

Collectives™ on Stack Overflow

Getting Table Output From Procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related