Store data from table into object table

Question

How would you go about transferring data into an object table?

Say you have a table:

create table thisTable(
    column1 varchar2(20),
    column2 varchar2(20),
    column3 varchar2(20)
)
/

And you have a new object table:

create table oo_thisTable(
    object1 object1_t
)
/
create type object1_t as object (
    column1 varchar2(20),
    column2 varchar2(20),
    column3 varchar2(20)
)
/

How would you transfer the data from thisTable to oo_thisTable?

declare
    cursor c1 is
        select * from thisTable;
begin
    open c1;
    loop
        fetch c1 into column1, column2, column3;
        exit when c1%notfound;
        ...

Answer 1

No need of using PL/SQL, you could do it in pure SQL.

INSERT INTO oo_thistable SELECT object1_t(column1, column2, column3) FROM thistable;

Demo

Create the required type and tables:

SQL> create table thisTable(
  2      column1 varchar2(20),
  3      column2 varchar2(20),
  4      column3 varchar2(20)
  5  )
  6  /

Table created.

SQL> create type object1_t as object (
  2      column1 varchar2(20),
  3      column2 varchar2(20),
  4      column3 varchar2(20)
  5  )
  6  /

Type created.

SQL> create table oo_thisTable(
  2      object1 object1_t
  3  )
  4  /

Table created.

Insert few rows in thistable:

SQL> INSERT INTO thistable VALUES('a','b','c');

1 row created.

SQL> INSERT INTO thistable VALUES('d','e','f');

1 row created.

SQL> INSERT INTO thistable VALUES('g','h','i');

1 row created.

Now we want to insert all rows from thistable into oo_thistable:

SQL> INSERT INTO oo_thistable SELECT object1_t(column1, column2, column3) FROM thistable;

3 rows created.

Validate:

SQL> SELECT * FROM oo_thistable;

OBJECT1(COLUMN1, COLUMN2, COLUMN3)
--------------------------------------------------------------------------------
OBJECT1_T('a', 'b', 'c')
OBJECT1_T('d', 'e', 'f')
OBJECT1_T('g', 'h', 'i')

You have all the rows inserted.