How to take dbms output of an object in oracle 19c?

Question

create or replace type pb_calculate_bill_ot as object ( bill id number(9),
                                                    version number(2),
                                                    rate    number(5),
                                                    descp   varchar2(25)
                                                );
                                                
create or replace type pb_calculate_bill_ct is table of pb_calculate_bill_ot;

----- inside a package -----

procedure select_price   (  pi_bill id  in number,
                        pi_version  in number,
                        po_data_ct  out pb_calculate_bill_ct) IS
                        
lt_calculate_bill_ct       pb_calculate_bill_ct := pb_calculate_bill_ct();
                        
-- procedure functionality--

end select_price;

--------- calling this proc inside same pkg ----------

pkg.select_price (  pi_bill_id => pi_bill,
                pi_version => pi_version,
                po_data_ct => lt_calculate_bill_ct);

how to take dbms_output of lt_calculate_bill_ct ???

`dbms_output` is not intended for data transfer, it **prints some text**. So the only usage of it is for debugging/scripting, it is a very rare case when you may want to grab its output. If you wish to return this data to the client, then you may check this question [return table from oracle procedure](https://stackoverflow.com/q/72239987/) — astentx, May 17 '22 at 07:46

score 0 · Answer 1 · answered May 17 '22 at 05:59

Here's an example.

Sample table:

SQL> CREATE TABLE bill
  2  AS
  3     SELECT 1 bill_id, 20 version, 1234 rate, 'Little' descp FROM DUAL
  4     UNION ALL
  5     SELECT 2, 13, 434, 'Foot' FROM DUAL;

Table created.

Types you created:

SQL> CREATE OR REPLACE TYPE pb_calculate_bill_ot AS OBJECT
  2  (
  3     bill_id NUMBER (9),
  4     version NUMBER (2),
  5     rate NUMBER (5),
  6     descp VARCHAR2 (25)
  7  );
  8  /

Type created.

SQL> CREATE OR REPLACE TYPE pb_calculate_bill_ct IS TABLE OF pb_calculate_bill_ot;
  2  /

Type created.

Sample procedure:

SQL> CREATE OR REPLACE PROCEDURE select_price (
  2     pi_bill_id  IN     NUMBER,
  3     pi_version  IN     NUMBER,
  4     po_data_ct     OUT pb_calculate_bill_ct)
  5  IS
  6     lt_calculate_bill_ct  pb_calculate_bill_ct := pb_calculate_bill_ct ();
  7  BEGIN
  8     SELECT pb_calculate_bill_ot (bill_id,
  9                                  version,
 10                                  rate,
 11                                  descp)
 12       BULK COLLECT INTO lt_calculate_bill_ct
 13       FROM bill
 14      WHERE     bill_id = pi_bill_id
 15            AND pi_version = pi_version;
 16
 17     po_data_ct := lt_calculate_bill_ct;
 18  END select_price;
 19  /

Procedure created.

Testing (this is what you asked for): declare a local variable which will hold result returned by the procedure; then, in a loop, do something with the result - I displayed it using dbms_output.put_line:

SQL> DECLARE
  2     l_res  pb_calculate_bill_ct;
  3  BEGIN
  4     select_price (1, 20, l_res);
  5
  6     FOR i IN 1 .. l_res.COUNT
  7     LOOP
  8        DBMS_OUTPUT.put_line (
  9              'Bill ID = '
 10           || l_res (i).bill_id
 11           || ', version = '
 12           || l_res (i).version
 13           || ', rate = '
 14           || l_res (i).rate
 15           || ', description = '
 16           || l_res (i).descp);
 17     END LOOP;
 18  END;
 19  /
Bill ID = 1, version = 20, rate = 1234, description = Little

PL/SQL procedure successfully completed.

SQL>

How to take dbms output of an object in oracle 19c?

1 Answers1