Is it possible to perform a select into table of object type?

Question

So i have a type

create or replace type body T_Some_type is object
  (...fields)

and i have a table type made of rows of type T_Some_type

create or replace TYPE T_Some_Table IS TABLE OF T_Some_type;

and i would like to select rows from some view to this T_Some_Table

select * 
        into T_Some_Table
        from V_Some_View

Is this even possible, and are there any cons for doing it this way (if possible at all). Do columns in T_Some_Type have to be in same order as in V_Some_View or will plsql do binding here if names of columns are the same?

If not possible, what would be alternative?

Edit : Having bodies defined is assumed, they are not here as they are not relevant to the question.

Answer 1

Yes, it is. You need to wrap your columns in the object's constructor and use the BULK COLLECT option in the SELECT statement:

CREATE OR REPLACE TYPE t_some_type AS OBJECT(f varchar2(10))
/

CREATE OR REPLACE TYPE t_some_table IS TABLE OF t_some_type
/

DECLARE
   v_some_table t_some_table;
BEGIN
   SELECT t_some_type (dummy)
   BULK   COLLECT INTO v_some_table
   FROM   DUAL;
END;

As an aside, you also need to make sure that you create the object specification, not just the body (as in your example).

Columns in the SELECT must be in the same order as they're found in the object's constructor. If you have not explicitly defined a constructor, one explicitly exists with each column in the order declared in the specification.

The only downside to using this functionality is that a large number of rows will result in heavy memory usage. If you expect to use this to process a large number of rows, you should use a loop with the LIMIT clause.

---

It is possible to specify an explicit constructor, in addition to the column list found in the specification. The constructor can have whatever input you define, so, obviously, when you use an explicit constructor, you have to follow it's argument list. Here's an example:

CREATE OR REPLACE TYPE t_some_type AS OBJECT
(
   f1 VARCHAR2 (10),
   CONSTRUCTOR FUNCTION t_some_type (p_length NUMBER, p_value VARCHAR2)
      RETURN SELF AS RESULT
);
/

CREATE OR REPLACE TYPE BODY t_some_type AS
   CONSTRUCTOR FUNCTION t_some_type (p_length NUMBER, p_value VARCHAR2)
      RETURN SELF AS RESULT IS
   BEGIN
      self.f1 := LPAD (p_value, p_length, p_value);
      RETURN;
   END t_some_type;
END;
/

CREATE OR REPLACE TYPE t_some_table IS TABLE OF t_some_type
/

DECLARE
   v_some_table t_some_table;
BEGIN
   --Explicit Constructor
   SELECT t_some_type (10, dummy)
   BULK   COLLECT INTO v_some_table
   FROM   DUAL;
   DBMS_OUTPUT.put_line (v_some_table (1).f1);

   --Implicit Constructor
   SELECT t_some_type (dummy)
   BULK   COLLECT INTO v_some_table
   FROM   DUAL;
   DBMS_OUTPUT.put_line (v_some_table (1).f1);
END;

Answer 2

It is possible, more or less how the OP wanted to do it:

SELECT *
  BULK COLLECT INTO v_some_table
  FROM <some_source_table>;

For more detail, see: https://blogs.oracle.com/oraclemagazine/on-bulk-collect - worked for me in Oracle 12.1.