Forum Stats

  • 3,728,515 Users
  • 2,245,641 Discussions
  • 7,853,563 Comments

Discussions

How can a procedure act differently for APEX than in the database??!!

grog
grog Member Posts: 5 Blue Ribbon

When I run my code from the database, and I use the same numeric parameter, it runs and populates the entire table. When I run it from APEX, it runs, but only populates the first step of the data!! There is only one commit at the end of the process being run.

Apex 20.1

Database 19c

The only thing I can think of is that I am calling it from a form page where the batch record is being updated.

Is that the problem?

Tagged:

Best Answer

  • grog
    grog Member Posts: 5 Blue Ribbon
    Accepted Answer

    OK. I found it... It's a little weird.

    In the procedure that is called by apex, it calls a function with a date parameter.

    That date parameter was specified as "TO_DATE ('31-mar-' || fiscal_year)", and this works fine using TOAD version 14.0.

    To get the apex call to work, I had to change the specification to "TO_DATE ('31-MAR-' || fiscal_year,'DD-MON-YYYY')" which allowed it to work. I've never added the 'DD-MON-YYYY' to my to_date calls, because I'm using the standard format, and it might be the mar -to- MAR that fixed it, but I'm not going back now to check.

    I've never seen this kind of behavior before, but you know,... I've been an Oracle developer long enough that I should not be surprised when I learn something new. 😀

Answers

  • grog
    grog Member Posts: 5 Blue Ribbon

    Well, after further examination, once I removed all of the commits, I got no functionality.

    I'm going back into it to put commits in and see where it failed.

  • grog
    grog Member Posts: 5 Blue Ribbon
    edited March 17

    So, it works right up to the point where the main procedure calls a function in the same package, then it just dies.

    When I switch the action to Wait for Result = True then I get this at the front end:

    Ajax call returned server error ORA-01843: not a valid month for Execute PL/SQL Code.

    Now the package IS running, and completing a set of actions using the correct parameter right up to when it stops working.

    And when I run it with the same parameter from PL/SQL in TOAD.. Exact same call, it does the entire process with no errors.

  • grog
    grog Member Posts: 5 Blue Ribbon
    Accepted Answer

    OK. I found it... It's a little weird.

    In the procedure that is called by apex, it calls a function with a date parameter.

    That date parameter was specified as "TO_DATE ('31-mar-' || fiscal_year)", and this works fine using TOAD version 14.0.

    To get the apex call to work, I had to change the specification to "TO_DATE ('31-MAR-' || fiscal_year,'DD-MON-YYYY')" which allowed it to work. I've never added the 'DD-MON-YYYY' to my to_date calls, because I'm using the standard format, and it might be the mar -to- MAR that fixed it, but I'm not going back now to check.

    I've never seen this kind of behavior before, but you know,... I've been an Oracle developer long enough that I should not be surprised when I learn something new. 😀

  • jariola
    jariola Member Posts: 10,221 Bronze Crown

    You have different NLS settings when running procedure in TOAD and that's why your date conversion fails

Sign In or Register to comment.