How to eliminate subtype dependency?

Question

In the example below I have written one to_str() function and one set() procedure for every pls_integer subtype. The functions and procedures are almost identical except the type.

How I can eliminate the need to write yet another to_str() and set() for a new subtype without giving up the constraint provided by the subtype ?

Falling back to varchar2 like

procedure set(list in varchar2, prefix in varchar2)

and then calling it as

set(to_str(list), 'foos:')

doesn't sound too great idea and I still need to provide to_str() for each subtype.

I'm open for all kind of different proposals as I'm Oracle newbie and new Oracle features suprise me almost daily.

I'm running 11.2.0.1.0.

create table so1table (
  id number,
  data varchar(20)
);

create or replace package so1 as
  subtype foo_t is pls_integer range 0 .. 4 not null;
  type foolist is table of foo_t;
  procedure set(id_ in number, list in foolist default foolist(1));

  subtype bar_t is pls_integer range 5 .. 10 not null;
  type barlist is table of bar_t;
  procedure set(id_ in number, list in barlist default barlist(5));
end;
/
show errors

create or replace package body so1 as
  /* Do I have always to implement these very similar functions/procedures for
  every single type ? */
  function to_str(list in foolist) return varchar2 as
    str varchar2(32767);
  begin
    for i in list.first .. list.last loop
      str := str || ' ' || list(i);
    end loop;
    return str;
  end;

  function to_str(list in barlist) return varchar2 as
    str varchar2(32767);
  begin
    for i in list.first .. list.last loop
      str := str || ' ' || list(i);
    end loop;
    return str;
  end;

  procedure set(id_ in number, list in foolist default foolist(1)) as
    values_ constant varchar2(32767) := 'foos:' || to_str(list);
  begin
    insert into so1table (id, data) values (id_, values_);
  end;

  procedure set(id_ in number, list in barlist default barlist(5)) as
    values_ constant varchar2(32767) := 'bars:' || to_str(list);
  begin
    insert into so1table (id, data) values (id_, values_);
  end;
end;
/
show errors

begin
  so1.set(1, so1.foolist(0, 3));
  so1.set(2, so1.barlist(5, 7, 10));
end;
/

SQLPLUS> select * from so1table;

        ID DATA
---------- --------------------
         1 foos: 0 3
         2 bars: 5 7 10

Exhilarant

Based on the response (18 up-votes, 4 favorites and two not so precise answers) so far I think this is a PL/SQL's pain point and not probably possible with subtypes. Maybe I have to use a bigger hammer: Using PL/SQL With Object Types ? – Exhilarant

Answer 1

create table so1table (
    id number,
    data varchar(20)
);


create or replace type parent_type as object
(
    v_number number,
    --Prefix probably belongs with a list, not an individual value.
    --For simplicity, I'm not adding another level to the objects.
    v_prefix varchar2(10)
) not instantiable not final;
/

create or replace type parentlist as table of parent_type;
/


create or replace type foo_type under parent_type
(
    constructor function foo_type(v_number number) return self as result
);
/

--The data must be stored as a NUMBER, since ADTs don't support
--PL/SQL specific data types.  The type safety is enforced by the
--conversion in the constructor.
create or replace type body foo_type is
    constructor function foo_type(v_number number) return self as result
    as
        subtype foo_subtype is pls_integer range 0 .. 4 not null;
        new_number foo_subtype := v_number;
    begin
        self.v_number := new_number;
        self.v_prefix := 'foos:';
        return;
    end;
end;
/

create or replace type foolist as table of foo_type;
/


create or replace type bar_type under parent_type
(
    constructor function bar_type(v_number number) return self as result
);
/

create or replace type body bar_type is
    constructor function bar_type(v_number number) return self as result
    as
        subtype bar_subtype is pls_integer range 5 .. 10 not null;
        new_number bar_subtype := v_number;
    begin
        self.v_number := new_number;
        self.v_prefix := 'bars:';
        return;
    end;
end;
/

create or replace type barlist as table of bar_type;
/



create or replace package so1 as
    procedure set(id_ in number, list in parentlist);
end;
/

create or replace package body so1 as

    function to_str(list in parentlist) return varchar2 as
        v_value VARCHAR2(32767);
    begin
        for i in list.first .. list.last loop
            if i = 1 then
                v_value := list(i).v_prefix;
            end if;
            v_value := v_value || ' ' || list(i).v_number;
        end loop;

        return v_value;
    end to_str;

    procedure set(id_ in number, list in parentlist) as
        values_ constant varchar2(32767) := to_str(list);
    begin
        insert into so1table (id, data) values (id_, values_);
    end set;
end so1;
/


begin
    --You probably don't want to mix foos and bars, but it is allowed. 
    so1.set(1, parentlist(foo_type(0), foo_type(3)));
    so1.set(2, parentlist(bar_type(5), bar_type(7), bar_type(10)));

    --These would generate "ORA-06502: PL/SQL: numeric or value error"
    --so1.set(1, parentlist(foo_type(5)));
    --so1.set(1, parentlist(bar_type(4)));

end;
/

select * from so1table;

Transaction

Finally an answer that makes sense. Thanks ! At the moment I don't like that all those object types can't be hidden inside a package, but pollute user's namespace. I guess that's the way with Oracle. – Exhilarant

Answer 2

This might not answer your question, but why not put the data in a regular table, then concatenate them, as you show, using the wm_concat aggregation function?

i.e.,

> select * from myTable;

ID  Category  Value
--- --------- ------
1   foo       0
2   foo       3
3   bar       5
4   bar       7
5   bar       10

> select   Category||'s: '||replace(wm_concat(Value),',',' ') Data
  from     myTable
  group by Category;

Data
-------------
bars: 5 7 10
foos: 0 3

wm_concat is type independent, so there's no need for you to overload your functions. Moreover, there are other methods that can be used; the analytical function method looks good, but I don't have 11g to test with!

(Edit Otherwise, I think you can achieve what you are looking for using Oracle's object model; specifically polymorphism. However, this is beyond me...so maybe someone else can chime in.)

Interloper

Answer 3

The following answer is actually to how you'd do it in postgresql (and plpgsql), and I also do not know about oracle subtypes, but I assume they are similar enough that at the very least it will lead you to your answer.

create function add (anynonarray,anynonarray) returning anynonarray
as 'begin return $1 + $2; end';

I know I botched the syntax, but it should show what I want to show with it, anyway.

The idea is that it will substitute "anynonarray" or any of the alternatives with the type of the parameter of the call. One restriction is that all the "anynonarray" in the example above will be of the same type.

The documentation referred to this as polymorphism.

Beora