Error: Single-Row subquery returns more than one row’ error.

user1163882

Hello,

I am calling below procedure and sometimes it gives error as mentioned above:

below is part of procedure :

CURSOR cEmpEvents (

        p_person_id   IN NUMBER) IS

       SELECT pe.event_id, pe.object_version_number, ppr.performance_review_id, ppr.object_version_number

        FROM per_events pe, per_performance_reviews ppr, per_assignments_x pax

        WHERE pax.person_id = p_person_id

        AND pax.assignment_id = pe.assignment_id

        AND ppr.event_id = pe.event_id

        AND TRUNC(SYSDATE) < pe.date_start;

BEGIN

/*

fnd_global.APPS_INITIALIZE(

    p_last_updated_by,

    20536,

    800,

    0,

    -1);

*/

BEGIN

SELECT period_of_service_id,

      object_version_number,

      last_standard_process_date

INTO  l_period_of_service_id,l_object_version_num,l_last_standard_process_date1

FROM per_periods_of_service pps

WHERE 1=1

AND person_id = p_person_id

AND actual_termination_date is NULL;

SELECT *

INTO l_last_standard_process_date1, l_final_proccess_date1

FROM

  (SELECT pp.end_date ,

    lead(pp.end_date) over (order by pp.end_date) next_end_date

  FROM per_all_assignments_f p ,

    per_time_periods pp

  WHERE trunc(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date

  AND p.person_id      = p_person_id

  AND p.payroll_id     = pp.payroll_id

  AND pp.end_date >= TRUNC(TO_DATE(TO_CHAR(TO_DATE(p_actual_termination_date,'YYYY-MM-DD'),'DD-MON-YYYY'))) --

  AND rownum          <= 2

  ) inner_q

WHERE rownum=1;

EXCEPTION WHEN OTHERS THEN

l_error_flag := 'E';

l_error_message := 'Error fetching the employee period of service details.'||sqlcode||'   '||SUBSTR(sqlerrm,12,200);

l_error_code := sqlcode; -- to handle the exception propertly in webcenter.

END;

l_termination_date1 := TO_DATE(TO_CHAR(TO_DATE(p_actual_termination_date,'YYYY-MM-DD'),'DD-MON-YYYY'));

--   l_last_standard_process_date1 := TO_DATE(TO_CHAR(TO_DATE(p_last_standard_process_date,'YYYY-MM-DD'),'DD-MON-YYYY'));

BEGIN

p_supervisor_warning               := NULL;

p_event_warning                    := NULL;

p_interview_warning                := NULL;

p_review_warning                   := NULL;

p_recruiter_warning                := NULL;

p_asg_future_changes_warning       := NULL;

p_entries_changed_warning          := NULL;

p_pay_proposal_warning             := NULL;

p_dod_warning                      := NULL;

p_org_now_no_manager_warning       := NULL;

      --mvadera initialize the session before starting DB transaction

      init_user_session(p_last_updated_by);

-- A.Lanz -> Delete all the futures events and perf Reviews if any.

OPEN cEmpEvents(p_person_id);

  LOOP

      FETCH cEmpEvents INTO l_event_id, l_event_object_version_number, l_per_id, l_per_object_version_number;

        EXIT WHEN cEmpEvents%NOTFOUND;

         IF l_per_id is not  null THEN

          hr_perf_review_api.delete_perf_review(p_performance_review_id => l_per_id, p_object_version_number => l_per_object_version_number);

         END IF;

         IF l_event_id is not null THEN

          per_events_api.delete_event(p_event_id => l_event_id,p_object_version_number => l_event_object_version_number);

         END IF;

  END LOOP;

CLOSE cEmpEvents;

GregV

Hi,

To what you posted it should come from this query:

SELECT period_of_service_id,

      object_version_number,

      last_standard_process_date

INTO  l_period_of_service_id,l_object_version_num,l_last_standard_process_date1

FROM per_periods_of_service pps

WHERE 1=1

AND person_id = p_person_id

AND actual_termination_date is NULL;

So check PER_PERIODS_OF_SERVICE for the same person_id having several actual_termination_date with NULL values:

SELECT person_id

FROM per_periods_of_service

WHERE actual_termination_date is NULL

GROUP BY person_id

HAVING COUNT(*) > 1;

user1163882

Hi GregV,

It's not giving any result. also if it gives it will be "exact fetch returns more than requested number of rows" ?

GregV

No it should give you person_id with more than 1 actual_termination_date that is null.

Is the code posted complete? If not, check the SELECT INTO instructions. They must return 1 row only.

Hoek

If you remove your WHEN OTHERS exception handler and rerun the code again after that, then when it errors out, you'll get the exact line number where the code failed and you'll know what statement needs adjustment.

Don't catch errors you do not expect.

user1163882

Yes the query you provided will give that result but actual query is

SELECT period_of_service_id,

      object_version_number,

      last_standard_process_date

INTO  l_period_of_service_id,l_object_version_num,l_last_standard_process_date1

FROM per_periods_of_service pps

WHERE 1=1

AND person_id = p_person_id

AND actual_termination_date is NULL;

so if there are multiple person_id where actual_termination_date is NULL it will give exact fetch returns more than requested number of rows.

GregV

Yes, it will give the error.

As Hoek pointed out, you need to reconsider your exception handling to get the statement involved.

Twinkle

user1163882 wrote:

Yes the query you provided will give that result but actual query is

SELECT period_of_service_id,
      object_version_number,
      last_standard_process_date
INTO  l_period_of_service_id,l_object_version_num,l_last_standard_process_date1
FROM per_periods_of_service pps
WHERE 1=1
AND person_id = p_person_id
AND actual_termination_date is NULL;

so if there are multiple person_id where actual_termination_date is NULL it will give exact fetch returns more than requested number of rows.

But you are passing specific input person ids.

Twinkle

user1163882

Hi Hoek,

The issue is we are getting this exception in production. so i can't test it as of now by removing WHEN OTHERS.

below is the full procedure if you can figure out something. thanks.

PROCEDURE terminate_employee(--p_period_of_service_id IN NUMBER

             --       ,p_object_version_number         IN OUT NUMBER

                 p_person_id IN NUMBER

                ,p_actual_termination_date       IN     VARCHAR2

                ,p_leave_reason IN VARCHAR2 DEFAULT NULL

                --,p_last_standard_process_date    IN OUT VARCHAR2

                ,p_last_updated_by IN NUMBER

                ,p_status OUT VARCHAR2

             )

IS

l_termination_date1 DATE;

l_last_standard_process_date1 DATE;

l_final_proccess_date1 DATE;

p_supervisor_warning                boolean;

p_event_warning                     boolean;

p_interview_warning                 boolean;

p_review_warning                    boolean;

p_recruiter_warning                 boolean;

p_asg_future_changes_warning        boolean;

p_entries_changed_warning           varchar2(100);

p_pay_proposal_warning              boolean;

p_dod_warning                       boolean;

p_org_now_no_manager_warning        boolean;

l_error_flag                     VARCHAR2(100);

l_error_message                  VARCHAR2(4000);

l_period_of_service_id           NUMBER(10);

l_object_version_num             NUMBER(10);

l_error_code                     NUMBER(10);

-- l_last_standard_process_date1     DATE;

l_event_id                      NUMBER(10);

l_event_object_version_number    NUMBER(10);

l_per_id                        NUMBER(10);

l_per_object_version_number     NUMBER(10);

-- Fetch future events

  CURSOR cEmpEvents (

        p_person_id   IN NUMBER) IS

       SELECT pe.event_id, pe.object_version_number, ppr.performance_review_id, ppr.object_version_number

        FROM per_events pe, per_performance_reviews ppr, per_assignments_x pax

        WHERE pax.person_id = p_person_id

        AND pax.assignment_id = pe.assignment_id

        AND ppr.event_id = pe.event_id

        AND TRUNC(SYSDATE) < pe.date_start;

BEGIN

/*

fnd_global.APPS_INITIALIZE(

    p_last_updated_by,

    20536,

    800,

    0,

    -1);

*/

BEGIN

SELECT period_of_service_id,

      object_version_number,

      last_standard_process_date

INTO  l_period_of_service_id,l_object_version_num,l_last_standard_process_date1

FROM per_periods_of_service pps

WHERE 1=1

AND person_id = p_person_id

AND actual_termination_date is NULL;

SELECT *

INTO l_last_standard_process_date1, l_final_proccess_date1

FROM

  (SELECT pp.end_date ,

    lead(pp.end_date) over (order by pp.end_date) next_end_date

  FROM per_all_assignments_f p ,

    per_time_periods pp

  WHERE trunc(SYSDATE) BETWEEN p.effective_start_date AND p.effective_end_date

  AND p.person_id      = p_person_id

  AND p.payroll_id     = pp.payroll_id

  AND pp.end_date >= TRUNC(TO_DATE(TO_CHAR(TO_DATE(p_actual_termination_date,'YYYY-MM-DD'),'DD-MON-YYYY'))) -- Same as Hitachi did to avoid conflicts.

  AND rownum          <= 2

  ) inner_q

WHERE rownum=1;

EXCEPTION WHEN OTHERS THEN

l_error_flag := 'E';

l_error_message := 'Error fetching the employee period of service details.'||sqlcode||'   '||SUBSTR(sqlerrm,12,200);

l_error_code := sqlcode; -- to handle the exception propertly in webcenter.

END;

l_termination_date1 := TO_DATE(TO_CHAR(TO_DATE(p_actual_termination_date,'YYYY-MM-DD'),'DD-MON-YYYY'));

--   l_last_standard_process_date1 := TO_DATE(TO_CHAR(TO_DATE(p_last_standard_process_date,'YYYY-MM-DD'),'DD-MON-YYYY'));

BEGIN

p_supervisor_warning               := NULL;

p_event_warning                    := NULL;

p_interview_warning                := NULL;

p_review_warning                   := NULL;

p_recruiter_warning                := NULL;

p_asg_future_changes_warning       := NULL;

p_entries_changed_warning          := NULL;

p_pay_proposal_warning             := NULL;

p_dod_warning                      := NULL;

p_org_now_no_manager_warning       := NULL;

      --mvadera initialize the session before starting DB transaction

      init_user_session(p_last_updated_by);

OPEN cEmpEvents(p_person_id);

  LOOP

      FETCH cEmpEvents INTO l_event_id, l_event_object_version_number, l_per_id, l_per_object_version_number;

        EXIT WHEN cEmpEvents%NOTFOUND;

         IF l_per_id is not  null THEN

          hr_perf_review_api.delete_perf_review(p_performance_review_id => l_per_id, p_object_version_number => l_per_object_version_number);

         END IF;

         IF l_event_id is not null THEN

          per_events_api.delete_event(p_event_id => l_event_id,p_object_version_number => l_event_object_version_number);

         END IF;

  END LOOP;

CLOSE cEmpEvents;

hr_ex_employee_api.actual_termination_emp

            ( p_validate                      =>false

              ,p_effective_date                => SYSDATE

              ,p_period_of_service_id          => l_period_of_service_id

              ,p_object_version_number         => l_object_version_num

              ,p_actual_termination_date       => l_termination_date1

              ,p_last_standard_process_date    => l_last_standard_process_date1

              ,p_leaving_reason                => p_leave_reason

              ,p_supervisor_warning               => p_supervisor_warning

              ,p_event_warning                    => p_event_warning

              ,p_interview_warning                => p_interview_warning

              ,p_review_warning                   => p_review_warning

              ,p_recruiter_warning                => p_recruiter_warning

              ,p_asg_future_changes_warning       => p_asg_future_changes_warning

              ,p_entries_changed_warning          => p_entries_changed_warning

              ,p_pay_proposal_warning             => p_pay_proposal_warning

              ,p_dod_warning                      => p_dod_warning

              );

hr_ex_employee_api.final_process_emp

          (

               p_validate                      => false

              ,p_period_of_service_id          => l_period_of_service_id

              ,p_object_version_number         => l_object_version_num

              ,p_final_process_date            => l_final_proccess_date1

              ,p_org_now_no_manager_warning    =>  p_org_now_no_manager_warning

              ,p_asg_future_changes_warning    =>  p_asg_future_changes_warning

              ,p_entries_changed_warning       =>  p_entries_changed_warning

          );

              l_error_flag := 'S';

              COMMIT;

        --mvadera reset user session;

            reset_user_session;

EXCEPTION WHEN OTHERS THEN

  l_error_flag := 'E';

  l_error_message := l_error_message||'Error in the API call:'||SQLERRM;

      --mvadera reset user session in case of exception;

      reset_user_session;

END;

IF NVL(l_error_flag,' ') = 'E' THEN

    IF (l_error_code = 100) THEN

      p_status := 'Error: This employee has already been terminated. This will be visible within 24 hours of the date of termination';

    ELSE

      p_status := SUBSTR(l_error_message,1,200);

    END IF;

ELSIF NVL(l_error_flag,' ') = 'S' THEN

    p_status := 'Employee Terminated successfully';

ELSE

    p_status := 'No Changes to save';

END IF;

END terminate_employee;

Hoek

A WHEN OTHERS exception (without a RAISE) is nothing but a BUG in your production code!

This needs to be fixed asap, you're swallowing errors AND you're in trouble already now!

That code, the way you're handling exceptions, should have never ever been put in production!

Please read (and share it with your collegues):

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:1509245700346768268#tom1535781400346575552

user1163882

Hi Hoek,

you mean using WHEN OTHERS will give different error than actual one? sorry I am not into PL/SQL development.

is it related to what error we are getting? (single row subquery..)

can you give example how below should be handled? Thanks..

EXCEPTION WHEN OTHERS THEN

  l_error_flag := 'E';

l_error_message := 'Error fetching the employee period of service details.'||sqlcode||'   '||SUBSTR(sqlerrm,12,200);

l_error_code := sqlcode; -- to handle the exception propertly in webcenter.