Skip to Main Content
  • Questions
  • Running SQL against Table of Records

Breadcrumb

Question and Answer

Tom Kyte

Thanks for the question, Igor.

Asked: September 15, 2000 - 9:47 am UTC

Last updated: February 13, 2012 - 8:00 am UTC

Version: 8.1.5

Viewed 10K+ times! This question is

You Asked

Tom,

Let say I have defined types:

TYPE testREC IS RECORD (gauge_id integer,
gauge_name varchar2(30));

TYPE arrayTAB IS TABLE OF testREC
INDEX BY BINARY_INTEGER;

and I have them populated:

declare out_rec arrayTAB;
begin
FOR nJ IN 1..3 loop
out_rec(nJ).gauge_id := nJ;
out_rec(nJ).gauge_name := 'nJ = ' || to_char(nJ);
end loop;
.....................
................

Is it possible to use something like:

SELECT * FROM THE (SELECT CAST(out_tec as arrayTAB) from dual...

in order to get the value of 'gauge_name' in out_rec where
gauge_id has specific value (say '2) ?

TIA

Igor Neyman






and we said...

Yes and No.

Not using PLSQL types of RECORDS and PLSQL tables.

Yes using Object Types (like a record) and Nested Table Types (like a plsql table).

the types must be defined at the SQL level for SQL to operate on them.

Here is an example:


Instead of a PLSQL record:

ops$tkyte@DEV816> create or replace type myRecordType
2 as object
3 ( x int,
4 y date,
5 z varchar2(25)
6 )
7 /

Type created.

Instead of a PLSQL table:

ops$tkyte@DEV816> create or replace type myTableType
2 as table of myRecordType;
3 /

Type created.

ops$tkyte@DEV816> create or replace function my_function
return myTableType
2 as
3 some_data myTableType := myTabletype();
4 begin
5 for i in 1 .. 10 loop
6 some_data.extend;
7 some_data(i) :=
8 myRecordType( i, sysdate+i, 'record ' || i );
9 end loop;
10 --shows we can select from VARIABLE
11 for x in
12 ( select *
13 from TABLE ( cast( some_data as mytableType ) )
14 order by z desc
15 )
16 loop
17 dbms_output.put( x.x );
18 dbms_output.put( ' ' || x.y );
19 dbms_output.put_line( ' ' || x.z );
20 end loop;
21
22 return some_data;
23 end;
24 /

Function created.

or select from "F(x)" where F is a plsql function returning a variable

ops$tkyte@DEV816> select *
2 from the ( select cast( my_function() as mytableType )
3 from dual );

X Y Z
---------- --------- -------------------------
1 16-SEP-00 record 1
2 17-SEP-00 record 2
3 18-SEP-00 record 3
4 19-SEP-00 record 4
5 20-SEP-00 record 5
6 21-SEP-00 record 6
7 22-SEP-00 record 7
8 23-SEP-00 record 8
9 24-SEP-00 record 9
10 25-SEP-00 record 10

10 rows selected.

and here is the dbms_output output

ops$tkyte@DEV816> exec null;
9 24-SEP-00 record 9
8 23-SEP-00 record 8
7 22-SEP-00 record 7
6 21-SEP-00 record 6
5 20-SEP-00 record 5
4 19-SEP-00 record 4
3 18-SEP-00 record 3
2 17-SEP-00 record 2
10 25-SEP-00 record 10
1 16-SEP-00 record 1

PL/SQL procedure successfully completed.

ops$tkyte@DEV816>
ops$tkyte@DEV816> select * from TABLE ( cast( my_function() as mytableType ) )
2 /

X Y Z
---------- --------- -------------------------
1 16-SEP-00 record 1
2 17-SEP-00 record 2
3 18-SEP-00 record 3
4 19-SEP-00 record 4
5 20-SEP-00 record 5
6 21-SEP-00 record 6
7 22-SEP-00 record 7
8 23-SEP-00 record 8
9 24-SEP-00 record 9
10 25-SEP-00 record 10

10 rows selected.

ops$tkyte@DEV816>
ops$tkyte@DEV816> exec null;
9 24-SEP-00 record 9
8 23-SEP-00 record 8
7 22-SEP-00 record 7
6 21-SEP-00 record 6
5 20-SEP-00 record 5
4 19-SEP-00 record 4
3 18-SEP-00 record 3
2 17-SEP-00 record 2
10 25-SEP-00 record 10
1 16-SEP-00 record 1

PL/SQL procedure successfully completed.

Rating

  (9 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

question about table type

mike, September 20, 2002 - 4:21 am UTC

I've used "select from table_type" in my program and I follow exactly your syntax. I can compile it but when I run it, the following error returns. Can you tell me why?

ORA-00600: internal server code, arguments:[15419], [severe error during PL/SQL execution]
ORA-06544: PL/SQL: internal error
ORA-06553: PLS-801: internal error
create or replace package body pk_test is

procedure sp_test
is
type myTableType is table of char(2);
l_data myTableType := myTabletype();

ls_item_desc char(20);
begin
dbms_output.enable(50000);

l_data.extend;
l_data(1) := '01';

select item_desc
into ls_item_desc
from item
where item_code in
( select * from
table(cast(l_data as mytableType )));

dbms_output.put_line('item desc: ' || ls_item_desc);

end sp_test;

end pk_test;




Tom Kyte
September 20, 2002 - 8:12 am UTC

don't know what version you are using but it is probably related to this:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create or replace procedure sp_test
  2  is
  3       type myTableType is table of char(2);
  4       l_data    myTableType := myTabletype();
  5
  6       ls_item_desc char(20);
  7  begin
  8       dbms_output.enable(50000);
  9
 10       l_data.extend;
 11       l_data(1) := '01';
 12
 13       select dummy item_desc
 14         into ls_item_desc
 15         from dual
 16        where dummy in
 17          ( select * from
 18             table(cast(l_data as mytableType )));
 19
 20       dbms_output.put_line('item desc: ' || ls_item_desc);
 21
 22  end sp_test;
 23  /

Warning: Procedure created with compilation errors.

ops$tkyte@ORA817DEV.US.ORACLE.COM> show err
Errors for PROCEDURE SP_TEST:

LINE/COL ERROR
-------- -----------------------------------------------------------------
13/6     PL/SQL: SQL Statement ignored
18/18    PLS-00642: local collection types not allowed in SQL statements


<b>
the types must be defined at the SQL level for SQL to operate on them.
</b>


create or replace type myTableType as table of char(2)
/

create or replace procedure sp_test ......


create the type OUTSIDE the procedure/package. 

Can you cast from a PLSQL type to a SQL type?

Andrew, February 09, 2005 - 3:13 pm UTC

Is it possible to cast from a PLSQL RECORD to a SQL TYPE having the same fields, and/or from a PLSQL "index-by" TABLE to a SQL "nested" table?

These would be very useful to have. I suppose I could write a function to do it, but in the case of the table this would be pretty inefficient.



Tom Kyte
February 09, 2005 - 3:32 pm UTC

no, such a feature does not exist, not any more than a cast from a char * to a sql type does...

sql is the 'bottom'

all else is layered on top of it...




Pls. help

A reader, February 10, 2005 - 6:05 pm UTC

Tom,

The following PL/SQL block gives me an error :
create or replace type myRecordType
as object
( x int,
y date,
z varchar2(25)
)
/
create or replace type myTableType
as table of myRecordType
/
declare
some_data mytabletype := mytabletype();
my_data myrecordtype;
begin
some_data.extend;
some_data(1) := myrecordtype(1,sysdate,'test');
some_data.extend;
some_data(2) := myrecordtype(2,sysdate,'test');
some_data.extend;
some_data(3) := myrecordtype(3,sysdate,'test');

22 : select *
into my_data
24 : from table (cast (some_data as mytabletype))
where rownum = 1;
end;
/
ORA-06550: line 24, column 4:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 22, column 2:
PL/SQL: SQL Statement ignored

However, the following code works correctly and prints :

1 10-FEB-05 test

declare
some_data mytabletype := mytabletype();
my_data myrecordtype;
begin
some_data.extend;
some_data(1) := myrecordtype(1,sysdate,'test');
some_data.extend;
some_data(2) := myrecordtype(2,sysdate,'test');
some_data.extend;
some_data(3) := myrecordtype(3,sysdate,'test');

for x in (
select a.*
from table (cast (some_data as mytabletype)) a
where rownum = 1 )
loop
dbms_output.put_line(x.x || ' ' || x.y || ' ' || x.z);
end loop;
end;
/

Pls advise

Tom Kyte
February 11, 2005 - 7:49 pm UTC

my_data is an object.


select * from .... returns a row full of scalars.


select myrecordtype( x, y, z )
into my_data
from table....


fetches a single object type from the table() ...


12 select myRecordType( x, y, z )
13 into my_data
14 from table (cast (some_data as mytabletype))
15 where rownum = 1;
16 end;
17 /

PL/SQL procedure successfully completed.



Almost perfect

Daniel, February 28, 2005 - 2:08 pm UTC

Nice solution.

It solves most of my problems, but I would like to know if is it possible to fill a table type with a select query directly.

Thanks!

Tom Kyte
February 28, 2005 - 2:24 pm UTC

ops$tkyte@ORA9IR2> create or replace type X as object (a int, b int)
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2> create or replace type Y as table of X
  2  /
 
Type created.
 
ops$tkyte@ORA9IR2>
ops$tkyte@ORA9IR2> declare
  2          l_data Y;
  3  begin
  4          select x( rownum, user_id )
  5            bulk collect into l_data
  6            from all_users;
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
 

Great

Daniel, March 01, 2005 - 8:42 am UTC

Thanks for the prompt answer.

mg, January 16, 2006 - 8:57 am UTC

Hi Tom,

When we try to create following package we got error:

CREATE OR REPLACE PACKAGE FONDSRPT.query_idx_tst AUTHID DEFINER
AS
TYPE idx_his_wgh_1_t IS RECORD (
idx_cus VARCHAR (10),
idx_dat_ref DATE,
idx_dat_ref_val NUMBER,
idx_from DATE,
idx_from_factor NUMBER,
idx_nam VARCHAR (10),
fx_cur VARCHAR (3),
wgh NUMBER,
asof DATE,
val NUMBER,
fx_rate NUMBER
);

TYPE idx_his_wgh_1_ref_t IS REF CURSOR
RETURN idx_his_wgh_1_t;

TYPE idx_his_wgh_1_tab_t IS TABLE OF idx_his_wgh_1_t;

TYPE idx_his_yld_t IS RECORD (
idx_cus VARCHAR2 (10 BYTE),
idx_dat_ref DATE,
idx_dat_ref_val NUMBER,
idx_from DATE,
idx_from_factor NUMBER,
idx_nam VARCHAR2 (10 BYTE),
asof DATE,
yield NUMBER
);

TYPE idx_his_yld_tab_t IS TABLE OF idx_his_yld_t;

TYPE fun_his_dis_t IS RECORD (
--fund history distribution type (Fonds Zeitreihe Ausschüttung)
fon_nr NUMBER,
tra_id NUMBER,
as_of DATE,
price NUMBER,
factor NUMBER
);

TYPE fun_his_dis_ref_t IS REF CURSOR
RETURN fun_his_dis_t;

TYPE fun_his_dis_tab_t IS TABLE OF fun_his_dis_t;

--History Calendar
TYPE his_cal_t IS RECORD(
his_asof DATE,
his_YTD DATE,
his_12M DATE,
his_06M DATE,
his_03M DATE,
his_01M DATE,
his_05D DATE,
his_02D DATE);

TYPE his_cal_tab_t IS TABLE OF his_cal_t;

TYPE his_cal_in_t IS RECORD(his_from DATE, his_to DATE);

TYPE his_cal_in_ref_t IS REF CURSOR RETURN his_cal_in_t;

--

--History Index Calendar
TYPE his_idxcal_in_t IS RECORD (
idx VARCHAR2(10),
asof DATE);

TYPE his_idxcal_out_t IS RECORD (
idx VARCHAR2(10),
asof DATE,
asof_YTD DATE,
asof_12M DATE);

TYPE his_idxcal_in_ref_t IS REF CURSOR RETURN his_idxcal_in_t;

TYPE his_idxcal_out_tab_t IS TABLE OF his_idxcal_out_t;

--

FUNCTION idx_his_wgh_1 (c_idx_his idx_his_wgh_1_ref_t)
RETURN idx_his_wgh_1_tab_t PIPELINED;

FUNCTION idx_his_yld (c_idx_his idx_his_wgh_1_ref_t)
RETURN idx_his_yld_tab_t PIPELINED;

FUNCTION fun_his_dis (c_fun_his_dis fun_his_dis_ref_t)
RETURN fun_his_dis_tab_t PIPELINED;

FUNCTION his_cal(c_his_cal_in_ref_t his_cal_in_ref_t)
RETURN his_cal_tab_t PIPELINED;

FUNCTION his_cal_param (d_from IN DATE, d_to IN DATE)
RETURN his_cal_tab_t PIPELINED;

-- FUNCTION his_idxcal(c_asof IN his_idxcal_in_t, c_YTD IN his_idxcal_in_t) return his_idxcal_out_t PIPELINED;--, c_YTD IN his_idxcal_in_t, c_xxx IN his_idxcal_in_t)
-- RETURN his_idxcal_out_t PIPELINED;
END;
/

CREATE OR REPLACE PACKAGE FONDSRPT.query_idx_tst AUTHID DEFINER
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [kokvxsql1], [], [], [], [], [], [],
[]
ORA-00600: internal error code, arguments: [17012], [0x13C4D820], [], [], [],
[], [], []

But this was as existing package which was working fine. recently our development team report they have above problem and they have done nothing changed on the system.

So could you please give me the hint what could be the reason for above error.

Note:
----
We are running oracle on Windows 2000 advanced server. Version as :
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production
CORE 9.2.0.1.0 Production
TNS for 32-bit Windows: Version 9.2.0.1.0 - Production
NLSRTL Version 9.2.0.1.0 - Production


Tom Kyte
January 16, 2006 - 9:47 am UTC

ora 600
ora 7445
ora 3113

contact support....

I've a feeling it is to do with the pipelined function and the implicit types it created (you might try dropping the package and then recreating the package).

They will likely ask you to patch up since I do see some issues with this early on in 9iR2 that were fixed a while ago

similar error, but answer not working

steve m, January 26, 2012 - 9:20 am UTC

Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production, Linux

I'm trying to pass a collection to a pipelined function, bulk collecting the results into another collection. I was getting ORA-00947: not enough values; however, applying the solution above gives ORA-00904: invalid identifier.

create type xmfr_rect is object(client_co_id    number,
                                valcheck varchar2(1));
                            
create type xmfr_tt is table of xmfr_rect;

create type isp_rect is object(client_co_id    number,
                                message varchar2(30));

create type isp_tabt is table of isp_rect;

create or replace package pkg_test
as

function xfmr_coll(p_xmfr_coll in xmfr_tt)
return isp_tabt pipelined;

end pkg_test;
/

create or replace package body pkg_test
as

function xfmr_coll(p_xmfr_coll in xmfr_tt)
return isp_tabt pipelined
is

   v_step              varchar2(30);
   vrec_test isp_rect;
   vtab_test isp_tabt;

begin

   vtab_test := isp_tabt();
  
   v_step := 'Starting...';
   
   for l_rec in p_xmfr_coll.first .. p_xmfr_coll.last
   loop
   
      vtab_test.extend;
               
      vtab_test(vtab_test.last).client_co_id := rpad(p_xmfr_coll(l_rec).client_co_id, 3,3);
      vtab_test(vtab_test.last).message := case when p_xmfr_coll(l_rec).valcheck = 'Y' then 'YES' else 'NO' end;
                  
   end loop;
      
   for l_rec in vtab_test.first .. vtab_test.last
   loop
               
      vrec_test := vtab_test(l_rec);
      pipe row (vrec_test);
                  
   end loop;
   
   return;

end xfmr_coll;
end pkg_test;
/

I then run:
declare

   vrec_isp_tt    isp_tabt  := isp_tabt();
   vrec_xfmr_tt   xmfr_tt   := xmfr_tt();     
   v_cnt number;
   
begin
   
   vrec_xfmr_tt.extend;
   
   vrec_xfmr_tt(vrec_xfmr_tt.last) := xmfr_rect(10, 'Y');

   vrec_xfmr_tt.extend;
   
   vrec_xfmr_tt(vrec_xfmr_tt.last) := xmfr_rect(20, 'N');
   
   for lx in 1 .. vrec_xfmr_tt.count
   loop
      dbms_output.put_line ('client_co_id: ' || vrec_xfmr_tt(lx).client_co_id);
      dbms_output.put_line ('valcheck: ' || vrec_xfmr_tt(lx).valcheck);
   end loop;
   
   select isp_rect(client_co_id, message) 
   bulk collect into vrec_isp_tt
   from table(cast(pkg_test.xfmr_coll(vrec_xfmr_tt) as xmfr_tt)); 
   
   if vrec_isp_tt.count > 0
   then
      for lx in 1 .. vrec_isp_tt.count
      loop
         dbms_output.put_line('client_id: ' || vrec_isp_tt(lx).client_co_id || '~' || vrec_isp_tt(lx).message);
      end loop;
      
   else
      dbms_output.put_line('no rows returned');
      
   end if;
   
   vrec_xfmr_tt.delete;

end;
/

I've tried varied permutations of writing the bulk collection query, but just haven't hit upon the right one. What am I not seeing or understanding correctly?
Thank you.
Tom Kyte
January 31, 2012 - 8:28 am UTC

<ocde>
select isp_rect(client_co_id, message)
bulk collect into vrec_isp_tt
from table(cast(pkg_test.xfmr_coll(vrec_xfmr_tt) as xmfr_tt));
</code>

casting doesn't work like that between object types. that function returns a isp_tabt. and the thing you are fetching into is an isp_tabt.


I'm not really sure what you are trying to do here. Your pipelined function is a bit strange, I don't know why you just don't pipe the row in the first loop - why you fill an array up first and then pipe each element?

this is what your code should look like:

ops$tkyte%ORA11GR2> create or replace package body pkg_test
  2  as
  3  
  4  function xfmr_coll(p_xmfr_coll in xmfr_tt) return isp_tabt pipelined
  5  is
  6  begin
  7      for l_rec in p_xmfr_coll.first .. p_xmfr_coll.last
  8      loop
  9          PIPE ROW ( isp_rect( rpad(p_xmfr_coll(l_rec).client_co_id, 3,3), case when p_xmfr_coll(l_rec).valcheck = 'Y' then 'YES' else 'NO' end ) );
 10      end loop;
 11     return;
 12  end xfmr_coll;
 13  
 14  end pkg_test;
 15  /

Package body created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> declare
  2     vrec_isp_tt    isp_tabt;
  3     vrec_xfmr_tt   xmfr_tt   := xmfr_tt( xmfr_rect(10,'Y'), xmfr_rect(20,'N') );
  4  begin
  5      for lx in 1 .. vrec_xfmr_tt.count
  6      loop
  7          dbms_output.put_line ('client_co_id: ' || vrec_xfmr_tt(lx).client_co_id);
  8          dbms_output.put_line ('valcheck: ' || vrec_xfmr_tt(lx).valcheck);
  9      end loop;
 10  
 11      select isp_rect(client_co_id, message)
 12        bulk collect into vrec_isp_tt
 13        from table( (pkg_test.xfmr_coll(vrec_xfmr_tt) ));
 14  
 15      dbms_output.put_line( 'printing ' || nvl(vrec_isp_tt.count,0) || ' records...' );
 16      for lx in 1 .. nvl(vrec_isp_tt.count,0)
 17      loop
 18          dbms_output.put_line('client_id: ' || vrec_isp_tt(lx).client_co_id || '~' || vrec_isp_tt(lx).message);
 19      end loop;
 20  end;
 21  /
client_co_id: 10
valcheck: Y
client_co_id: 20
valcheck: N
printing 2 records...
client_id: 103~YES
client_id: 203~NO

PL/SQL procedure successfully completed.

solved

steve m, February 03, 2012 - 9:09 am UTC

Thanks!
I was able to apply your method successfully to my complete code.

In reply to your questions, for those reading through this thread...
What I was trying to do is convert an existing pipe-lined function, which had 8 parameters as input, to take a collection as input. I was building upon existing code that filled the array and piped the element, I don't know why this had been done, but only can guess they didn't want to list out the elements. However, I found the listing of elements helpful in that I can see what is being sent.

Thanks again,
Steve

Using rownum as subscript

A.Refa'at, February 12, 2012 - 5:46 am UTC

Hi Tom;
I need to select from a PL collection type, so I tried this

CREATE OR REPLACE PROCEDURE prc_get_employees_data (
result_cursor IN OUT sys_refcursor
)
AS
TYPE rec$employee IS RECORD (
employee_id NUMBER (6),
first_name VARCHAR2 (20 BYTE),
last_name VARCHAR2 (25 BYTE),
email VARCHAR2 (25 BYTE),
phone_number VARCHAR2 (20 BYTE),
hire_date DATE,
job_id VARCHAR2 (10 BYTE),
salary NUMBER (8, 2),
commission_pct NUMBER (2, 2),
manager_id NUMBER (6),
department_id NUMBER (4)
);

TYPE table$employee IS TABLE OF rec$employee INDEX BY pls_INTEGER;
--
vreftype rec$employee;
vreftable table$employee ;--:= table$employee();
--
j NUMBER := 0;
x NUMBER;

--
CURSOR cur
IS
SELECT e.employee_id, e.first_name, e.last_name, e.email, e.phone_number, e.hire_date,
e.job_id, e.salary, e.commission_pct, e.manager_id, e.department_id
FROM employees e;

--
BEGIN
--vreftype.delete;
FOR rec IN cur
LOOP
j := j + 1;
vreftype.employee_id := rec.employee_id;
vreftype.first_name := rec.first_name;
vreftype.last_name := rec.last_name;
vreftype.email := rec.email;
vreftype.phone_number := rec.phone_number;
vreftype.hire_date := rec.hire_date;
vreftype.job_id := rec.job_id;
vreftype.salary := rec.salary;
vreftype.commission_pct := rec.commission_pct;
vreftype.manager_id := rec.manager_id;
vreftype.department_id := rec.department_id;

--vreftable.extend;
vreftable (j) := vreftype;
END LOOP;

/*OPEN result_cursor FOR
SELECT *
FROM TABLE (CAST (vreftable AS table$employee));*/ -->> PL/SQL: ORA-00902: invalid datatype

OPEN result_cursor FOR
SELECT rownum,
vreftable(rownum).employee_id employee_id,
vreftable(rownum).first_name first_name,
vreftable(rownum).last_name last_name,
vreftable(rownum).email email,
vreftable(rownum).phone_number phone_number,
vreftable(rownum).hire_date hire_date,
vreftable(rownum).job_id job_id,
vreftable(rownum).salary salary,
vreftable(rownum).commission_pct commission_pct,
vreftable(rownum).manager_id manager_id,
vreftable(rownum).department_id department_id
FROM DUAL
CONNECT BY ROWNUM <= j
;
END;
/

But it I get ORA-01403: no data found
and after Investigating the problem I foun that the "rownum" is always equal zero when used as subscript of "vreftable", though if I used it as a column even in the same row with "vreftable(rownum)" it shows the right value.

I tried this because I don't want to use any object out of the procedure, like using TABLE(CAST) with database object but I need everything in one object.

Thank You..
Tom Kyte
February 13, 2012 - 8:00 am UTC

I tried this because I don't want to use any object out of the procedure, like
using TABLE(CAST) with database object but I need everything in one object.


why not? your types are totally private, hidden. SQL cannot *see* them, SQL (which resides sort of "below" plsql) doesn't have any clue about them. No metadata exists for them. If you used a package, and exposed these types in the spec/body - we could generate types for you - but then your schema would be littered with things like:

TYPE         SYS_PLSQL_108114_24_1
             SYS_PLSQL_108114_9_1
             SYS_PLSQL_108114_DUMMY_1


while will undoubtedly lead to questions like "what are these".

I highly recommend you do it right and you define a type and a table of that type. Think of them like "views" - a bunch of metadata. And now you can document them.


Also - you should just select .. BULK COLLECT ... into your collection - no need for a loop and an assignment attribute by attribute.

More to Explore

DBMS_OUTPUT

More on PL/SQL routine DBMS_OUTPUT here