How to show result when table is my type in Oracle

Question

I created a simple Oracle type:

create or replace TYPE MY_TYPE AS OBJECT (ID NUMBER(30), NAME VARCHAR2(20));

Then, I created a second table type:

create or replace TYPE MY_TYPE_TABLE AS TABLE OF MY_TYPE;

Finally, I created a simply function:

create or replace FUNCTION my_function(line_id IN NUMBER) RETURN MY_TYPE_TABLE 
AS
  return_data MY_TYPE_TABLE := MY_TYPE_TABLE();
BEGIN
  return_data.EXTEND;
  return_data(return_data.count) := (MY_TYPE(10, 'BOB')) ;
  return_data.EXTEND;
  return_data(return_data.count) := (MY_TYPE(11, 'ALAN')) ;
  RETURN return_data;
END SETTLEMENT_NET_TRACKING;

My question: How to run this function that result like this:

10 BOB
11 ALAN

Hot to do it?

Answer 1

If you're looking for logging in that method you could use DBMS_OUTPUT to log to the standard output or use UTL_FILE to log to a file. I've suggested in previous questions that if you're designing anything bigger than a trivial application you'll want to create a custom logging package.

For your problem it would look something like this:

dbms_output.enable;
for i in return_data.first..return_data.last loop
  dbms_output.put_line(return_data(i).id || ' ' || return_data(i).name);
end loop;

You'll need to enable output in your client application. For SQLPlus you'd use SET SERVEROUT ON before you call your API.

Answer 2

You can also do

select * from table(my_function(30))