This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 19, 2013 1:51 PM by Doug Go to original post RSS
  • 15. Re: PLSQL Function in APEX
    Howard (... in Training) Pro
    Currently Being Moderated
    Progress? Ah. So it's more complex than passing data. The function is more complex. Let's proceed slowly.

    1) You know this function "works" because you've used it before. Right?
    2) It's not working the way we are using it in APEX. Right?

    Sounds like the type of data or the way the data is being passed in APEX is the problem.

    To make debugging easier, how about testing in the APEX SQL Commands window. Run
    SELECT numofdays(:P160_DKTNR,:P160_PAYOFF_DATE) FROM DUAL;
    You will have to enter the literal values. Does that work?

    And then
    SELECT numofdays(vrecdate,vlastdate) FROM DUAL;
    But, what are vrecdate and vlastdate in APEX? How do they get a value? I'd expect a syntax error. I'm not sure how it can identify these.

    Howard
  • 16. Re: PLSQL Function in APEX
    Doug Newbie
    Currently Being Moderated
    Howard,

    Here's something that works. Not entirely, but some progress.
    declare
      vdays number(4);
      vlastdate date;
      vcurdate date;
      cursor getdate_cur is
       select prindate from ffmisc
       where dktnr = :P160_DKTNR;
    begin
      open getdate_cur;
        fetch getdate_cur into vlastdate;
      close getdate_cur;
      vcurdate := :P160_PAYOFF_DATE;
      vdays := to_date(vcurdate) - to_date(vlastdate);
      RETURN (vdays);
    end;
    It returns the number of days to the :P160_DAYS item on page load. But I need to be able to select a date for the :P160_PAYOFF_DATE item using the date picker. Nothing happens when I do that. I created a dynamic action to refresh :P160_DAYS when there is a change in the payoff_date item, but nothing happened.

    Appreciate your help.

    Edited by: Doug on Apr 4, 2013 2:59 PM
  • 17. Re: PLSQL Function in APEX
    Howard (... in Training) Pro
    Currently Being Moderated
    Remember, the date picker is going to return a character string for :P160_DKTNR. I recommend doing explicit date conversion so you know exactly what you are getting. dktnr is a DATE, right? TO_DATE(:P160_DKTNR,'<date picker format>)

    (more) You should only TO_DATE for character strings, you've defined vcurdate and vlastdate as dates already.

    to_date(vcurdate) - to_date(vlastdate); Doesn't seem right to me!

    Need to leave and many chores tonight.

    Later,
    Howard
  • 18. Re: PLSQL Function in APEX
    Doug Newbie
    Currently Being Moderated
    The question is not answered, but I found another way of fixing the problem. The problem was that javascript gave me a wrong day count by a minus one day on the day after day light savings time went into effect. I found this could be corrected by switching from math.floor to math.round.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points