How to insert Data into a custom type oracle table from a source table

Question

I have a source data table like below:

create table source_qtr (
 id   int
,qtr1 NUMBER(20,9)
,qtr2 NUMBER(20,9)
,qtr3 NUMBER(20,9)
,qtr4 NUMBER(20,9)     );

insert scripts for sample data

insert into source_qtr values (1,1.982,2.3453,5.3453,4.64564);
insert into source_qtr values (2,2.93482,2.3453,5.3453,4.64564);
insert into source_qtr values (3,3.982,2.3453,5.3453,4.64564);
insert into source_qtr values (4,7.982,2.3453,5.3453,4.64564);
insert into source_qtr values (5,5.982,2.3453,5.3453,4.64564);
insert into source_qtr values (6,9.982,2.3453,5.3453,4.64564);
insert into source_qtr values (7,11.982,2.3453,5.3453,4.64564);
insert into source_qtr values (8,12.982,2.3453,5.3453,4.64564);
insert into source_qtr values (9,21.982,2.3453,5.3453,4.64564);
insert into source_qtr values (10,41.982,2.3453,5.3453,4.64564);

And my target table structure depends on few more objects those are:

create type obj_qtr as Object   ( qtr_id      int
                                , qtr_value   NUMBER(20,9)    );

CREATE TYPE VARR_QTR IS VARRAY(4) OF obj_qtr;

CREATE TABLE TAR_QTR            ( SRC_id       int
                                , QTR          VARR_QTR);

I can insert the data in to this target table by using manually generated insert scripts:

insert into TAR_QTR values (1, VARR_QTR(obj_qtr('1','1.982'),obj_qtr('2','2.3453'),obj_qtr('3','5.3453'),obj_qtr('4','4.64564')));

This process is taking more time. To avoid that, we are trying to create a procedure to insert this data from the source table:

create or replace procedure Data_Load_proc
is
begin
       for x in (   select   sq.id
                    ,        'VARR_QTR     (     obj_qtr(''1'',''' || sq.qtr1 || ''')
                                           ,     obj_qtr(''2'',''' || sq.qtr2 || ''')
                                           ,     obj_qtr(''3'',''' || sq.qtr3 || ''')
                                           ,     obj_qtr(''4'',''' || sq.qtr4 || ''')
                                           )' as a            
                    from     source_qtrsq
                ) loop
           insert into TAR_QTR (SRC_id,QTR) values (x.id,cast(x.a as VARR_QTR));
       end loop;
       commit;
End;

When I execute this procedure I got error:

Error starting at line : 60 in command - 
BEGIN procedure Data_Load_proc; END; 
Error report - 
ORA-06550: line 1, column 7: 
PLS-00103: Encountered the symbol "PROCEDURE" when expecting one of the following: 

          ( begin case declare exit for goto if loop mod null pragma 
            raise return select update while with <an identifier> 
            <a double-quoted delimited-identifier> <a bind variable> << 
            continue close current delete fetch lock insert open rollback 
            savepoint set sql execute commit for all merge pipe purge 
ORA-06550: line 1, column 36: 
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following: 

   ; 
06550. 00000 - "line %s, column %s:\n%s" 
*Cause: Usually a PL/SQL compilation error. 
*Action:

How to resolve this issue? Or is there any other way to insert this data?

Note: not able to use 'External table' or 'sql Loader' due to some security reason.

Thanks.

Answer 1

Your problem is you need to cast the source table rows into OBJ_QTR types, and that table is not structured to do that nicely. So you need a query which changes the projection to fit the target structure:

select  id, 1 as qtr_id, qtr1 as qtr_value from source_qtr union all
select  id, 2 as qtr_id, qtr2 as qtr_value from source_qtr union all
select  id, 3 as qtr_id, qtr3 as qtr_value from source_qtr union all
select  id, 4 as qtr_id, qtr4 as qtr_value from source_qtr

Now you can use that to populate type instances and multiset() to populate your VARRAY. Something like this:

insert into tar_qtr 
select m.id,
        CAST (MULTISET (select OBJ_QTR(qtr_id, qtr_value) as qtr 
           from (
                  select  id, 1 as qtr_id, qtr1 as qtr_value from source_qtr union all
                  select  id, 2 as qtr_id, qtr2 as qtr_value from source_qtr union all
                  select  id, 3 as qtr_id, qtr3 as qtr_value from source_qtr union all
                  select  id, 4 as qtr_id, qtr4 as qtr_value from source_qtr
               ) q where q.id = m.id) as VARR_QTR)
from    (select distinct id from source_qtr) m
/