Skip to Main Content

Java and JavaScript in the Database

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How to convert java code into PL/SQL stored Proc /Function

Srinivasa Reddy KalagotlaFeb 7 2024 — edited Feb 7 2024

Hello Everybody,

I am looking for help in converting the below Java code to PL/SQL. I don't know Java. Can someone please help me?
Please convert this Java code into Oracle PL/SQL.

Urgent help is required.

Thanks in advance!!!

###################################################################

import com.ibm.db2.app.UDF;
import java.time.*;
import java.time.temporal.ChronoUnit;

public class JavaSnapshotUtils extends UDF {

int rowPosition = 0;

public class LogDates {
private long startDateTime;
private Long endDateTime;

   public LogDates(long startDateTime, Long endDateTime) {  
       this.startDateTime = startDateTime;  
       this.endDateTime = endDateTime;  
   }

   public long getStartDateTime() {  
       return startDateTime;  
   }

   public void setStartDateTime(long startDateTime) {  
       this.startDateTime = startDateTime;  
   }

   public Long getEndDateTime() {  
       return endDateTime;  
   }

   public void setEndDateTime(Long endDateTime) {  
       this.endDateTime = endDateTime;  
   }  

}

public LogDates getLogStartEndDateTime(long date, String timeZoneName, String twentyFourHourPeriodStart, long effectiveDate) {
LocalTime twentyFourHourPeriodStartInTime = get24HourPeriodStartInTime(twentyFourHourPeriodStart);
ZoneId dateTimeZone = getDateTimeZone(timeZoneName);
ZonedDateTime effectiveDateTime = ZonedDateTime.ofInstant(Instant.ofEpochMilli(effectiveDate), ZoneOffset.UTC);

   ZonedDateTime first24HPeriodStart = mirrorDateTimeInNewTimeZone(effectiveDateTime, dateTimeZone).with(twentyFourHourPeriodStartInTime);  
   ZonedDateTime firstLogDate = calculateLogDate(first24HPeriodStart.toInstant().toEpochMilli(), dateTimeZone, twentyFourHourPeriodStartInTime);

   ZonedDateTime logDate = ZonedDateTime.ofInstant(Instant.ofEpochMilli(date), ZoneOffset.UTC);

   ZonedDateTime startDateTimeTemp = mirrorDateTimeInNewTimeZone(logDate, dateTimeZone).with(twentyFourHourPeriodStartInTime);  
   ZonedDateTime endDateTimeTemp = startDateTimeTemp.plusDays(1).minusNanos(1);

   if (firstLogDate.toInstant().toEpochMilli() == logDate.truncatedTo(ChronoUnit.DAYS).toInstant().toEpochMilli() || startDateTimeTemp.isBefore(effectiveDateTime)) {  
       startDateTimeTemp = ZonedDateTime.ofInstant(effectiveDateTime.toInstant(), dateTimeZone);  
   }

   long startDateTime = startDateTimeTemp.toInstant().toEpochMilli();  
   long endDateTime = endDateTimeTemp.toInstant().toEpochMilli();

   return new LogDates(startDateTime, endDateTime);  

}

public void getLogStartEndDateTime(long date, String timeZoneName, String twentyFourHourPeriodStart, long effectiveDate, long startDateTime, long endDateTime) throws Exception {
int callType = getCallType();
switch(callType) {
case SQLUDF_TF_FIRST:
break;
case SQLUDF_TF_OPEN:
rowPosition = 0;
break;
case SQLUDF_TF_FETCH:
if (rowPosition == 0) {
LogDates tempLogDates = getLogStartEndDateTime(date, timeZoneName, twentyFourHourPeriodStart, effectiveDate);
startDateTime = tempLogDates.getStartDateTime();
endDateTime = tempLogDates.getEndDateTime();
set(5, startDateTime);
set(6, endDateTime);
rowPosition++;
} else {
setSQLstate("02000");
}
break;
case SQLUDF_TF_CLOSE:
break;
case SQLUDF_TF_FINAL:
break;
default:
throw new Exception("UNEXPECT call type of "+callType);
}
}

public static ZonedDateTime calculateLogDate(long dateTimeInMillis, ZoneId dateTimeZone, LocalTime twentyFourHourPeriodStart) {
ZonedDateTime localDateTime = ZonedDateTime.ofInstant(Instant.ofEpochMilli(dateTimeInMillis), dateTimeZone);

   // make datetime that does not take into account DST offset when a day/hour/minute/... is added.  
   // It prevents the following problem: if transition happens EST->DST: 2020-03-08 2:00AM -> 2020-03-08 3:00AM  
   // then the result of the following operation in joda DateTime 2020-03-08 3:00AM minus 3 hours will be 2020-03-07 23:00.  
   // but we need 2020-03-08 00:00  
   ZonedDateTime dateTimeIgnoringDst = mirrorDateTimeInNewTimeZone(localDateTime, ZoneOffset.UTC);  
   ZonedDateTime dateTimeWithout24HPS = dateTimeIgnoringDst.minus(twentyFourHourPeriodStart.toSecondOfDay(), ChronoUnit.SECONDS);  
   return dateTimeWithout24HPS.truncatedTo(ChronoUnit.DAYS);  

}

public static LocalTime get24HourPeriodStartInTime(String twentyFourHourPeriodStart) {
LocalTime localTime = LocalTime.NOON;
if (twentyFourHourPeriodStart != null && twentyFourHourPeriodStart.matches("\\d{6}")) {
int hours = twentyFourHourPeriodStart != null ? Integer.parseInt(twentyFourHourPeriodStart.substring(0, 2)) : 0;
int minutes = twentyFourHourPeriodStart != null ? Integer.parseInt(twentyFourHourPeriodStart.substring(2, 4)) : 0;
int seconds = twentyFourHourPeriodStart != null ? Integer.parseInt(twentyFourHourPeriodStart.substring(4, 6)) : 0;
localTime = LocalTime.of(hours, minutes, seconds);
}

   return localTime;  

}

public static ZoneId getDateTimeZone(String timeZoneId) {
return timeZoneId != null ? ZoneId.of(timeZoneId) : ZoneOffset.UTC;
}

public static ZonedDateTime mirrorDateTimeInNewTimeZone(ZonedDateTime dateTime, ZoneId dateTimeZone) {
return ZonedDateTime.of(dateTime.getYear(), dateTime.getMonthValue(), dateTime.getDayOfMonth(), dateTime.getHour(), dateTime.getMinute(), dateTime.getSecond(), dateTime.getNano(), dateTimeZone);
}
}

#############################################################

Comments
Post Details
Added on Feb 7 2024
6 comments
1,090 views