Inserting Rows in Database Table Example

 INSERT INTO sale_header
 VALUES (500, 
  (SELECT REF(C) FROM customer_obj_table
    c where CUST_ID = 1),
  89.45,3.58,15,108.03,'01-aug-00',
  detail_table()
);

This page contains a diagram that describes inserting rows using the PL/SQL Programming language

Location 1	The INSERT statement specifying the table
Location 2	The values of the columns in the order of the definition of columns during object table creation
Location 3	The sub query to select reference data
Location 4	The other column data
Location 5	The end of the INSERT statement

Using Compound DML Triggers with Bulk Insertion

A compound DML trigger is useful for accumulating rows destined for a second table so that you can periodically bulk-insert them. To get the performance benefit from the compound trigger, you must specify BULK COLLECT INTO in the FORALL statement (otherwise, the FORALL statement does a single-row DML operation multiple times). For more information about using the BULK COLLECT clause with the FORALL statement,

Bulk insert Example

Here is a working example of a bulk insert. It compares a traditional insert (for i in) to the bulk insert (forall i):

-- create a test table for the bulk insert
drop table test_objects;

create table  
test_objects  
tablespace users
as 
select object_name, object_type from dba_objects;

-- Populate the table into a array using bulk collect
DECLARE
TYPE t_tab IS TABLE OF test_objects%ROWTYPE;
objects_tab t_tab := t_tab();
start_time number; end_time number;
BEGIN
-- Populate a collection  
SELECT *  
BULK COLLECT  
INTO  
objects_tab  
FROM test_objects;

-- Time the population of the table with a bulk insert
EXECUTE IMMEDIATE 'TRUNCATE TABLE test_objects';
 Start_time := DBMS_UTILITY.get_time;
FORALL i in objects_tab.first .. objects_tab.last
 INSERT INTO test_objects VALUES objects_tab(i);
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Bulk Insert: '||to_char(end_time-start_time));

-- Populate the table without a bulk insert
EXECUTE IMMEDIATE 'TRUNCATE TABLE test_objects';
 Start_time := DBMS_UTILITY.get_time;
FOR i in objects_tab.first .. objects_tab.last LOOP
INSERT INTO test_objects (object_name, object_type)
VALUES (objects_tab(i).object_name, objects_tab(i).object_type);
END LOOP;
end_time := DBMS_UTILITY.get_time;
DBMS_OUTPUT.PUT_LINE('Conventional Insert: '||to_char(end_time-start_time));
COMMIT;
END; 
/