Inserting data into Object type table from another existing table..Oracle 11g

Question

I am trying to insert data into an object type table from another existing table in a stored procedure. I created the "object" type and it's type "table" as below example.

CREATE OR REPLACE TYPE EXAMPLE AS OBJECT
(
ALL THE COLUMNS,
);
/
CREATE OR REPLACE TYPE EXAMPLE_TABLE AS TABLE OF EXAMPLE;

trying to use this type table to get the data inserted from another table.

---
BEGIN
INSERT INTO EXAMPLE_TABLE (ALL THE COLUMS FROM EXAMPLE) 
VALUES (SELECT * FROM EMP_TABLE);

is this possible? let me know, or should I use the "CURSOR" to insert one by one.. please let me know how to achieve this,in the form of syntax

--thank you in advance

Answer 1

Example table is a type so you can't insert anything into it, first you would need to define a variable that would be an instance of your example_table. Then you can use a bulk collect to actually populate your table.

declare
  example_tab_inst EXAMPLE_TABLE;
begin
  select 
   EXAMPLE(e.col1,e.col2,e.col3) 
  bulk collect into example_tab_inst 
  from emp_table e;
 .
 .
 .