I am attempting to grab a variable max date from a table, then use that variable to insert the records into another table that are greater than the variable max date. I have created the procedure and tested it but it only inserts 1 record each time I run the procedure as a scheduled job through dbms_scheduler to run every 30 minutes. My test case allowed for the first run to insert 6 rows, after the first job run it only inserted 1 record of the 6 records. Then the next run inserted 1 record...etc. I am testing this to ultimately be used in concept to insert append a few thousand rows every 30 minutes as a scheduled job. What is the most effective way to run this type of procedure quickly and bulk insert the rows. I was considering altering the table to nologging and dropping any indexes and rebuild them after the insert. What is the best approach, thank you in advance.
Here is my code:
create or replace procedure update_cars
AS
v_date date;
begin
execute immediate 'alter session set NLS_DATE_FORMAT='DD-MON-YY HH24:MI:SS'';
select max(inventory_date) into v_date from car_equipment;
insert /*+APPEND*/ into car_equipment(count_cars,equipment_type,location,inventory_date,count_inventory)
select count_cars,equipment_type,location,inventory_date,count_inventory
from car_source where inventory_date > v_date;
end;
Why are you altering session? What benefit do you expect from it?
Code you wrote can be "simplified" to
create or replace procedure update_cars
as
begin
insert into car_equipment (count_cars,, equipment_type, ...)
select s.count_cars, s.equipment_type, ...
from car_source s
where inventory_date > (select max(e.inventory_date) from car_equipment e);
end;
If code inserts only one row, then check date values from both car_equipment and car_source tables. Without sample data, I'd say that everything is OK with code (at least, it looks OK to me).
If you'll be inserting a few thousand rows every 30 minutes, that shouldn't be a problem as Oracle is capable of handling that easily.