Array DML with objects

[zobbi1]

Hi all,

Does direct oracle access (or even oracle itself, I am not sure) support array DML using objects (mdsys.sdo_geometry in my case) ?

thanks

george

 

[Marco Kalter]

You cannot use object variables for Array DML, only scalar variable types.

Can you let me know what SQL statement you want to execute? Maybe there is a workaround.

------------------
Marco Kalter
Allround Automations

 

[zobbi1]

essentially what i want to do is batch insert mdsys.sdo_geometry objects in a table.

table definition
create table dummy (id number not null,
geom mdsys.sdo_geometry);

i want to insert ~ 1000000 records into this table something like
insert into dummy id, :geom);
where :id is an array of ids and :geom is an array of objects mdsys.sdo_geometry.

do you think i can do it with direct path loading ?

cheers

george

 

[Marco Kalter]

This is only possible if you can break down the sdo_geometry constructor into scalar data. For example:

insert into dummy (id, geom)
values (:id,
        mdsys.sdo_geometry(:sdo_gtype,
                           :sdo_srid,
                           :sdo_point,
                           mdsys.sdo_elem_info_array(),
                           mdsys.sdo_ordinate_array()
                          )
       )

One additional complication is that the sdo_elem_info and sdo_ordinates attributes are collections (I have set them to 'empty' in my example). If you need to fill them as well, you need a second Array DML process for this.

------------------
Marco Kalter
Allround Automations