Forum Stats

  • 3,827,359 Users
  • 2,260,763 Discussions
  • 7,897,218 Comments

Discussions

how to fetch data into the cursor.

User_5U3UI
User_5U3UI Member Posts: 12 Green Ribbon

Hi using cursor, i need to fetch the end date from fnd_user table and update it in XXDEV_CU165_HR_APPR_LIMITS table.How to achieve this.

PROCEDURE XX_PROC IS


CURSOR CUR_INACTIVE_USER IS


 SELECT fu.user_id,fu.end_date

 FROM fnd_user fu, cu165_hr_appr_limits hral,

 where fu.employee_id =hral.person_id

 AND fu.end_date IS NOT NULL;


BEGIN

 FOR rec_inactive_user IN cur_inactive_user LOOP


     UPDATE XXDEV_CU165_HR_APPR_LIMITS HRAL

SET HRAL.END_DATE_ACTIVE = (

                              SELECT FU.END_DATE

                                FROM FND_USER FU

                                WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID

                             )

  WHERE EXISTS(

               SELECT 1

                FROM FND_USER FU

                WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID

                AND FU.END_DATE IS NOT NULL

              )

   AND (

           HRAL.END_DATE_ACTIVE IS NULL

        OR

           HRAL.END_DATE_ACTIVE > sysdate

       );

 END LOOP;

 COMMIT;


END XX_PROC;

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,482 Red Diamond
    edited May 10, 2022 12:27PM

    Using PL/SQL for something that can be done via relatively simple SQL statement is in most cases inefficient. Anyway:

    CREATE OR REPLACE
      PROCEDURE XX_PROC
        IS
            CURSOR CUR_INACTIVE_USER
              IS
                SELECT  FU.EMPLOYEE_ID,
                        FU.END_DATE
                  FROM  FND_USER FU,
                        CU165_HR_APPR_LIMITS HRAL,
                  WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID
                    AND FU.END_DATE IS NOT NULL
                    AND (
                            HRAL.END_DATE_ACTIVE IS NULL
                         OR
                            HRAL.END_DATE_ACTIVE > SYSDATE
                        );
        BEGIN
            FOR REC_INACTIVE_USER IN CUR_INACTIVE_USER LOOP
              UPDATE XXDEV_CU165_HR_APPR_LIMITS HRAL
                 SET HRAL.END_DATE_ACTIVE = REC_INACTIVE_USER.END_DATE
               WHERE HRAL.PERSON_ID = REC_INACTIVE_USER.EMPLOYEE_ID;
            END LOOP;
            COMMIT;
    END XX_PROC;
    /
    

    SY.

  • BEDE
    BEDE Oracle Developer Member Posts: 2,427 Gold Trophy

    Why do you need the cursor? One update is enough and is definitely more efficient than that cursor loop.

  • User_5U3UI
    User_5U3UI Member Posts: 12 Green Ribbon

    Here the requirement is

    We need the closing of NASA user access to simultaneously close the same users “ fuldmagter” in the Fuldmagtsregister in NASA. Consequently the closing functionality in the FMP will have to be changed to immediately closing instead of closing the “ Fuldmagt” within the change to a new date, and we need the Fuldmagtsregister table to be linked to the NASA user Table.

    Here cu165_hr_appr_limits is a Fuldmagtsregister table and fnd_user is the NASA user Table.so i thought of using cursor loop to update.


    Is this the efficient way to achieve above requriment


    CREATE OR REPLACE
      PROCEDURE XX_PROC
        IS
            CURSOR CUR_INACTIVE_USER
              IS
                SELECT  FU.EMPLOYEE_ID,
                        FU.END_DATE
                  FROM  FND_USER FU,
                        CU165_HR_APPR_LIMITS HRAL,
                  WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID
                    AND FU.END_DATE IS NOT NULL
                    AND (
                            HRAL.END_DATE_ACTIVE IS NULL
                         OR
                            HRAL.END_DATE_ACTIVE > SYSDATE
                        );
        BEGIN
            FOR REC_INACTIVE_USER IN CUR_INACTIVE_USER LOOP
              UPDATE XXDEV_CU165_HR_APPR_LIMITS HRAL
                 SET HRAL.END_DATE_ACTIVE = REC_INACTIVE_USER.END_DATE
               WHERE HRAL.PERSON_ID = REC_INACTIVE_USER.EMPLOYEE_ID;
            END LOOP;
            COMMIT;
    END XX_PROC;
    /
    


  • Mike Kutz
    Mike Kutz Member Posts: 6,195 Silver Crown


    Is this the efficient way to achieve above requriment

    no. Use MERGE instead.

    merge into REC_INACTIVE_USER hral
    using ( /* sql from cursor */
                SELECT  FU.EMPLOYEE_ID,
                        FU.END_DATE
                  FROM  FND_USER FU,
                        CU165_HR_APPR_LIMITS HRAL,
                  WHERE FU.EMPLOYEE_ID = HRAL.PERSON_ID
                    AND FU.END_DATE IS NOT NULL
                    AND (
                            HRAL.END_DATE_ACTIVE IS NULL
                         OR
                            HRAL.END_DATE_ACTIVE > SYSDATE
                        )
    ) REC_INACTIVE_USER
    on ( HRAL.PERSON_ID = REC_INACTIVE_USER.EMPLOYEE_ID ) /* same WHERE clause */ 
    When matched then update set
      HRAL.END_DATE_ACTIVE = REC_INACTIVE_USER.END_DATE; /* same SET clause */