Forum Stats

  • 3,784,143 Users
  • 2,254,897 Discussions
  • 7,880,709 Comments

Discussions

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

user1163882
user1163882 Member Posts: 67 Blue Ribbon
edited Apr 25, 2014 7:20AM in SQL & PL/SQL

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;

Tagged:
«1

Answers

  • GregV
    GregV Member Posts: 3,077 Gold Crown

    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;

    GregV
  • user1163882
    user1163882 Member Posts: 67 Blue Ribbon

    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
    GregV Member Posts: 3,077 Gold Crown

    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
    Hoek Member Posts: 16,076 Gold Crown

    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
    user1163882 Member Posts: 67 Blue Ribbon

    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
    GregV Member Posts: 3,077 Gold Crown

    Yes, it will give the error.

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

  • Twinkle
    Twinkle Member Posts: 740 Silver Badge
    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
    user1163882 Member Posts: 67 Blue Ribbon

    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
    Hoek Member Posts: 16,076 Gold Crown

    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

    Hoek
  • user1163882
    user1163882 Member Posts: 67 Blue Ribbon

    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.

This discussion has been closed.