This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Apr 19, 2013 1:51 PM by Doug RSS

PLSQL Function in APEX

Doug Newbie
Currently Being Moderated
I have a PLSQL function stored in the database. It returns the number of days between two dates. I want it to fire and return a number to a number item when a user enters a date into a date item. How do I make it fire when the user enters the date?

Also, does anyone know generally why javascript would return a number 1 day less than a PLSQL function for the number of days between two dates?
  • 1. Re: PLSQL Function in APEX
    Howard (... in Training) Pro
    Currently Being Moderated
    On the edit screen, right click on your date item and select create Dynamic Action. For the DA, give it a name. On the next screen the default event will be Change for the date item. All that is fine. On the next screen, set the Action selector to execute PL/SQL code. Then when the screen repaints, make sure Fire on Page Load is unchecked. In the PL/SQL Code window put
    Begin
      :px_number_item := <function call here>;
    End;
    A little further down on that screen, select this item to be submitted to the DB.

    That should return the function value to your item when the date changes. But you may want a validation to kick in before this happens just to make sure it's a valid date! perhaps that can be done on this same DA. Make it an action with a lower sequence number. Or make it a separate validation -- I'm not clear on that.

    Enjoy,
    Howard
  • 2. Re: PLSQL Function in APEX
    Doug Newbie
    Currently Being Moderated
    Howard, thank you for your suggestion. Here is what I put into the PL/SQL Code of the DA:

    begin
    :P63_NUMBEROFDAYS := numofdays(:P63_DKTNR,:P63_RECDATE);
    end; --------(Sorry, I've forgotten how to html code)

    And in the "Page Items to Submit," I put " :P63_NUMBEROFDAYS".

    This did not produce the number of days in the :P63_NUMBEROFDAYS item, either with or without the "Page Items to Submit" field being completed.

    The saved and compiled numofdays function is as follows:

    create or replace FUNCTION numofdays (vdktnr varchar2,vrecdate date) return number is
    vlastdate date;
    vdays number(4);
    cursor getdate_cur is
    select prindate from ffmisc
    where dktnr = vdktnr;
    BEGIN
    open getdate_cur;
    fetch getdate_cur into vlastdate;
    close getdate_cur;
    vdays := trunc(vrecdate - vlastdate);
    return vdays;
    END;

    Any ideas? Is the call to the function correct?

    By the way, I should have mentioned that I am using APEX 4.0. Right clicking an item in the editor does not produce an option for a dynamic action.
  • 3. Re: PLSQL Function in APEX
    Nattu Explorer
    Currently Being Moderated
    And in the "Page Items to Submit," I put " :P63_NUMBEROFDAYS". 
    Remove the colon, you just need to give the item name only. Colon is just needed only if you use the page item in a plsql block or sql query.

    Your function seems to be correct.

    Regards,

    Natarajan
  • 4. Re: PLSQL Function in APEX
    Howard (... in Training) Pro
    Currently Being Moderated
    It is a real headache, at least at times, to determine what form(s) of the variable name is(are) correct. Web technology is an amalgam of rapidly evolving technologies each with their individual syntax definitions. Fortunately, in APEX, many times examples are provided on the page which will illustrate the correct form(s). (Mouse over the field/window name. If a "?" appears, there is more information. Sometimes it's helpful! Look for "Examples" under the field or window.) Or there will be a selector provided. Though you may not need (or choose not) to use the selector, it can also show what the correct variable format (syntax!) is for that selection.

    Best wishes. Good hunting.
    Howard
  • 5. Re: PLSQL Function in APEX
    riedelme Expert
    Currently Being Moderated
    Howard (... in Training) wrote:
    It is a real headache, at least at times, to determine what form(s) of the variable name is(are) correct.
    Truer words were never typed :)
  • 6. Re: PLSQL Function in APEX
    Doug Newbie
    Currently Being Moderated
    Thanks everyone, but it is still not working. After doing as Howard suggested by clicking on the label for the "Page Items to Submit" item, I saw I had entered the item to which the function would return the value (which was wrong). So I entered the two items that would be passed to the database function (without colons). No cigar.
  • 7. Re: PLSQL Function in APEX
    Howard (... in Training) Pro
    Currently Being Moderated
    Question:

    Okay. Let's step back and double check.

    1) You have this function call, right? :P63_NUMBEROFDAYS := numofdays(:P63_DKTNR,:P63_RECDATE);
    2) :P63_DKTNR and :P63_RECDATE are character strings, so your function converts these to dates before subtracting, right?

    Have you tried with two character string constants to make sure that works?
    -- for example: :P63_NUMBEROFDAYS := numofdays('20130403','20130401'); or '20130401','20130403' ???
    Because if you can't get it to work with constants, it will likely never work!

    Let us know where you are at here.

    Regards,
    Howard
  • 8. Re: PLSQL Function in APEX
    Doug Newbie
    Currently Being Moderated
    Well, this is getting pretty frustrating. I hope that I am not doing something incredibly stupid and simple. But here is where I am.

    I created a test function:

    create or replace FUNCTION testdays (vday1 number,vday2 number) return number is
    vdays number(4);
    begin
    vdays := (vday1) - (vday2);
    return vdays;
    end testdays;

    I also created another page P_160 so that I could delete any other javascript interference. On that page is an item :P160_DAYS. Into the Source for that item, I put the following:

    begin
    :P160_DAYS := testdays('203145','203140');
    end;

    For Source Type I used "PL/SQL Function Body."

    The result: nothing.
  • 9. Re: PLSQL Function in APEX
    Howard (... in Training) Pro
    Currently Being Moderated
    Ah. If I'm not mistaken, you have no "return" so this is actually a procedure, not a function.
    Try Type PL/SQL Expression with
    testdays('203145','203140') 
    As a test I just tried
    TO_CHAR(sysdate,'YYYY')
    which gave 2013.

    Howard
  • 10. Re: PLSQL Function in APEX
    Nattu Explorer
    Currently Being Moderated
    Could create this issue in apex.oracle.com?
  • 11. Re: PLSQL Function in APEX
    Doug Newbie
    Currently Being Moderated
    The "return" is vdays,

    vdays := (vday1) - (vday2);
    return vdays;
    end testdays;

    By the way, what markup do you use to get code to appear in the gray background?
  • 12. Re: PLSQL Function in APEX
    Howard (... in Training) Pro
    Currently Being Moderated
    [I didn't see what you had just written until I had posted this.]

    Here's another issue. There is a set of value for each page item. The HTML page value and the Session State value -- if it exists. I describe it this way. When a page is rendered the HTML iinstantiates the page (screen) values you see. When a Submit Page is done, those values are "pushed" into Session State. So if you change the values on the screen, they will be different from any values previously "pushed" into Session State. (You might also have corresponding database columns for each page item -- but let's not go there now!)

    When you do an APEX Process or Computation, it also pushes any "assigned" values into the Session State (example, :P3_ITEM1 = 'Yes'; ).

    So, if you want your :P160_DAYS placed into Session State at the same time that the value is "Sourced", use a function and also assign the value as a side effect -- <horrors>, <gasp>.
    (See, thanks to Scott!:    {message:id=1192348})
    Example:
    begin
      :P160_DAYS := testdays('203145','203140');
      RETURN testdays('203145','203140');
    end;
    Howard
  • 13. Re: PL/SQL Function in APEX
    Howard (... in Training) Pro
    Currently Being Moderated
    {noformat}The "code" background comes from using the
    ...
    tag to surround the code.{noformat}
    -- This is how code is displayed.
      -- Note spacing is maintained
      :P1_ITEM := 'Yes';
      If <condition> THEN
        :P1_ITEM := 'No';
      END IF
    {noformat}And you can "quote" with the
    ...
    tag pair.{noformat}
    This is how "quoted" text is displayed.
    See: https://forums.oracle.com/forums/help.jspa

    Of course, it's more distintive on a white background!

    -- Howard

    Edited by: Howard (... in Training) on Apr 4, 2013 11:34 AM
  • 14. Re: PLSQL Function in APEX
    Doug Newbie
    Currently Being Moderated
    OK, this looks like, if not progress, then potential for it. The literals returned a value. But in the real world we are not using literals.
    begin
      :P160_DAYS := numofdays(:P160_DKTNR,:P160_PAYOFF_DATE);
      RETURN numofdays(vrecdate,vlastdate);
    end;
    We pass the dktnr and the payoff date to the function, and we want it to return the number of days between two dates. One of the dates is passed and the other isqueried form a table via a cursor in the function. In the literal example, you copied the literals in the return line from the literals in the pass line. Above, I put in the variables for the two dates used in the function. That was a mimic of the literals example. But it did not return the days, nor, when I tried it, did the variable" vdays", also in the function.

    Stick with me Howard. What's next?

    Edited by: Doug on Apr 4, 2013 2:23 PM
1 2 Previous Next

Legend

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