convert below sqlcode to oracle

Question

I need to convert below code from sql to oracle PLSQL please help me

        Update A
         Set 
          A.ShiftName=@ShiftName,
          A.ShiftStartTime=DATEADD(day,DATEDIFF(day,0,AttendanceDate),
          CAST(S.ShiftStartTime as varchar(8))),
          A.ShiftEndTime=DATEADD(day,DATEDIFF(day,case when @ShiftName='NIGHT' then -1 else 0 end ,
          AttendanceDate),CAST(S.ShiftEndTime as varchar(8))),
          A.ModifiedBy=@UpdatedBy, 
          A.ModifiedOn=GETDATE()
          from Attendance A left join ShiftMaster S on S.[Shift]=@ShiftName
          where AttendanceId=@AttendanceId
          
    =========================================================================
    I need to convert below code from sql to oracle PLSQL  please help me 
=========================================================================
    UPDATE A
            SET A.ShiftName = dt.ShiftName, 
            A.ShiftStartTime=DATEADD(day,DATEDIFF(day,0,A.AttendanceDate),CAST(S.ShiftStartTime as varchar(8))),
            A.ShiftEndTime=DATEADD(day,DATEDIFF(day,case when dt.ShiftName='NIGHT' then -1 else 0 end ,A.AttendanceDate),CAST(S.ShiftEndTime as varchar(8))),
            A.ModifiedBy = @UpdatedBy,
            A.ModifiedOn = getdate()
            from Attendance A join @dtAttendance dt on A.LoginId = dt.LoginID and A.AttendanceDate = dt.AttendanceDate 
            left join ShiftMaster S on S.[Shift]=dt.ShiftName 
            where a.AttendanceDate between getdate()-1 and dateadd(day,+30,getdate())
            and A.LoginId in (Select LoginId from UserAttendanceTeam where TowerPk = @TowerPk and isActive = 'Y')

I need to convert above code from sql to oracle PLSQL please help me

Answer 1

Well, that's invalid piece of code; why did you expect it to work?

• CTE is lost in space - you need to do something with it (in my example, I opened a ref cursor)
• you have to terminate each statement
• fix v_date; don't rely on implicit datatype conversion (from string to date)
• recursive CTE has to have column list

Also, note that package body (you posted) requires package specification (body can't exist without it).

Code that looks better is

CREATE OR REPLACE PACKAGE BODY PKG_ATTENDANCE_DASHBOARD
AS
   PROCEDURE Proc_Dashboard (V_CommandType                VARCHAR2,
                             V_LoginId                    VARCHAR2,
                             V_SignType                   VARCHAR2,
                             V_AttendanceDate             DATE,
                             V_Year                       VARCHAR2,
                             V_Month                      VARCHAR2,
                             V_IPAddress                  VARCHAR2,
                             V_LogonUserName              VARCHAR2,
                             V_WorkLocation               VARCHAR2,
                             V_WorkLocationType           VARCHAR2,
                             V_Company                    VARCHAR2,
                             V_UserType                   VARCHAR2,
                             V_ShiftName                  VARCHAR2,
                             V_Self_Covid_Status          VARCHAR2,
                             V_Self_Covid_Remark          VARCHAR2,
                             V_Family_Covid_Status        VARCHAR2,
                             V_Family_Covid_Remark        VARCHAR2,
                             V_UpdatedBy                  VARCHAR2,
                             curGetCalendar           OUT SYS_REFCURSOR,
                             curGetCovidStatus        OUT SYS_REFCURSOR,
                             curListCompanyName       OUT SYS_REFCURSOR,
                             curListTowerName         OUT SYS_REFCURSOR,
                             curAttendanceListByDate  OUT SYS_REFCURSOR)
   AS
      v_Date  DATE := TO_DATE (V_Year || '-' || V_Month || '-01', 'yyyy-mm-dd');
   BEGIN
      IF V_CommandType = 'getCalendar'
      THEN
         OPEN curGetCalendar FOR
            WITH
               DaysInMonth (dates)
               AS
                  (SELECT SYSDATE AS dates FROM DUAL
                   UNION ALL
                   SELECT INTERVAL '1' DAY (5) + dates
                     FROM DaysInMonth
                    WHERE EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM v_date))
            SELECT dates
              FROM DaysInMonth;
      END IF;
   END Proc_Dashboard;
END PKG_ATTENDANCE_DASHBOARD;

Now, it depends on what you pass to it (all parameters are obligatory) so I'm not going to do that for you. CTE, as is, works:

SQL> WITH
  2     DaysInMonth (dates)
  3     AS
  4        (SELECT SYSDATE AS dates FROM DUAL
  5         UNION ALL
  6         SELECT INTERVAL '1' DAY (5) + dates
  7           FROM DaysInMonth
  8          WHERE EXTRACT (MONTH FROM dates) = EXTRACT (MONTH FROM DATE '2023-01-01'))
  9  SELECT dates
 10    FROM DaysInMonth;

DATES
----------
2023-10-26

SQL>

---

[EDIT], based on your comment:

Code

WITH DaysInMonth (dates) AS ( SELECT V_Date AS dates FROM DUAL UNION ...

won't work because there's no V_DATE column in DUAL table. If you used SYSDATE function, it would execute, but still return wrong result (missing 1st day of current month) so you'll have to fix that. Also, if you want to omit the time part of date, use trunc(sysdate).

On the other hand, here's somewhat simpler code you might be interested in:

SQL> select trunc(sysdate, 'mm') + level - 1 as dates
  2  from dual
  3  connect by level <= last_day(sysdate) - trunc(sysdate, 'mm') + 1;

DATES
----------
01.11.2023
02.11.2023
03.11.2023
<snip>
29.11.2023
30.11.2023

30 rows selected.

SQL>