0

I am trying to create a procedure to insert missing rows from another table by max(date). I would then schedule a job to run every 5 minutes to update a remote table.

I am unable to get my procedure to compile. I would like to add date format change as part of the session too. Then how would I use job scheduler to call it to run every 5 minutes.

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) from car_equipment into v_date;
return v_date;
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;
/

Error(6,47): PL/SQL: ORA-00933: SQL command not properly ended

Gilly
  • 45
  • 7
  • 1
    The correct syntax is `select max(inventory_date) into v_date from car_equipment;`. Note that RETURN hands flow back to the calling program. The INSERT statement after the RETURN will not be executed. – APC Sep 19 '19 at 16:50
  • Thank you APC, I can't believe I missed it. Must be jetlag. – Gilly Sep 19 '19 at 16:56
  • It's easy enough to avoid simple syntax bloomers like this by consulting the PL/SQL Reference. For instance, [here is the section on SELECT INTO](https://docs.oracle.com/database/121/LNPLS/fundamentals.htm#LNPLS00205). It will be worth bookmarking the documentation for future emergencies. – APC Sep 19 '19 at 16:56

1 Answers1

0
  1. SELECT expression INTO variable FROM ... as APC mentioned.
  2. RETURN is probably misplaced, as APC mentioned
  3. Date format change? car_equipment.inventory_date is probably already a date, since you select it into a date variable. Is car_source.inventory_date a date column? If it is, than a date is a date is a date: nothing to do. If it is not a date (bad idea!) then use the appropriate to_char(inventory_date, 'DD/MM/YYYY') in the select.
JochenVdB
  • 31
  • 3