Having trouble when looking for a row by date having date as a parameter

Question

I am trying to execute a procedure that has the following structure:

PROCEDURE GENERATE_TICKETS_TODAY (generating_date DATE)
IS
    CURSOR observs_today
    IS 
    SELECT * 
    FROM observations 
    WHERE (EXTRACT(YEAR FROM OBSERVATIONS.odatetime)) = (EXTRACT(YEAR FROM generating_date)) 
    AND (EXTRACT(MONTH FROM OBSERVATIONS.odatetime)) = (EXTRACT(MONTH FROM generating_date)) 
    AND (EXTRACT(DAY FROM OBSERVATIONS.odatetime)) = (EXTRACT(DAY FROM generating_date));

    obs_analysed observs_today%ROWTYPE;
    previous_obs_car observations%rowtype;
    previous_obs_radar observations%rowtype;
    owner_ticket VARCHAR2(9);


BEGIN

    OPEN observs_today;

    LOOP
        FETCH observs_today INTO obs_analysed;
        EXIT WHEN observs_today%NOTFOUND;

        SELECT OWNER into owner_ticket 
        FROM VEHICLES WHERE nPlate=obs_analysed.nPlate;

        previous_obs_car := prev_obs_car(obs_analysed.nPlate, obs_analysed.odatetime);
        previous_obs_radar := prev_obs_radar(obs_analysed.odatetime, obs_analysed.road, obs_analysed.km_point, obs_analysed.direction);

        --si hay infraccion de velocidad puntual: obs1_veh, obs1_date, tik_type, NULL, NULL, sent_date, NULL, NULL, amount, debtor, state

        IF ticket_fee_maxspeed(obs_analysed.nPlate, obs_analysed.odatetime) > 0 THEN
        INSERT 
        INTO TICKETS 
        VALUES (obs_analysed.nPlate, obs_analysed.odatetime, 'S', NULL, NULL, generating_date, NULL, NULL, ticket_fee_maxspeed(obs_analysed.nPlate, obs_analysed.odatetime), owner_ticket, DEFAULT);
        END IF;


        --si hay infraccion de velocidad DE TRAMO: obs1_veh, obs1_date, tik_type, obs2_veh, obs2_date, sent_date, NULL, NULL, amount, debtor, state

        IF ticket_fee_sectspeed(obs_analysed.nPlate, previous_obs_car.odatetime, obs_analysed.odatetime) > 0 THEN
        INSERT INTO TICKETS 
        VALUES (obs_analysed.nPlate, previous_obs_car.odatetime ,'T', obs_analysed.nPlate, obs_analysed.odatetime, generating_date, NULL, NULL, ticket_fee_sectspeed(obs_analysed.nPlate, previous_obs_car.odatetime, obs_analysed.odatetime),owner_ticket, DEFAULT);
        END IF;

        --si hay infraccion de DISTANCIA: obs1_veh, obs1_date, tik_type, obs2_veh, obs2_date, sent_date, NULL, NULL, amount, debtor, state

        IF ticket_fee_distance(obs_analysed.road, obs_analysed.km_point, obs_analysed.direction, obs_analysed.odatetime) > 0 THEN
        INSERT INTO TICKETS 
        VALUES (previous_obs_radar.nPlate, previous_obs_radar.odatetime, 'D', obs_analysed.nPlate, obs_analysed.odatetime, generating_date, NULL, NULL, ticket_fee_distance(obs_analysed.road, obs_analysed.km_point, obs_analysed.direction, obs_analysed.odatetime), owner_ticket, DEFAULT);
        END IF;

    END LOOP;

    CLOSE observs_today;


END;

When I try the execution through an unnamed block like this:

DECLARE
   x date;
BEGIN
   x := TO_DATE('20/05/2009', 'DD/MM/YYYY');
    DGT.GENERATE_TICKETS_TODAY(x);
END; 

I get the following execution error:

I think this has to do with how the procedure gets the parameter date and performs the SELECT query with it, in order to retrieve all the timestamps that have that date. So, I would love to know how to do this in order that it works.

I basically need to consult the table OBSERVATIONS and save in a CURSOR all the rows that have a timestamp with date generating date (procedure's parameter). Could you help me please?? Thank you :)

Answer 1

The ORA-01847 error indicates a data conversion failure: somewhere in your code you are casting a string to a date but the day is invalid; for instance, this would hurl that error: to_date('31-FEB-2018').

What we can see of your code there are no calls to to_date() so it seems like you have a implicit date conversion somewhere. This is bad practice precisely because it exposes us to these sort of conversion errors when the stringified date is not in the default format.

Beyond that, there's not much we can do to help here: you have the table structures, you have the input data, we don't. So you have the information you need to debug your code.

I will also repeat my previous advice (which you rejected). Write a small piece of code and get that working. Obviously you need all the code to deliver the full set of features but it really is much easier (and, counter-intuitively, quicker) to built complex functionality by incrementally building small units of code and linking them together. The apparent velocity of writing hundreds of lines of code all at once evaporates in the time spent debugging the monolith.

So: focus on one bit say the cursor. Make sure that works. Then add in one the insert statements. Get that working. Add in another. Repeat until complete.