Attention! Are you using the Drop in replacement for V and NV function?

If you are using my Drop in replacement for V and NV function and you are upgrading to Oracle APEX 3.1 then you have to alter the functions to reference the new schema FLOWS_030100!!!

If you are not doing this your applications will not work because the functions are trying to read the session state from the old FLOWS_030000 schema, but there no values can be found for the current session which are stored in FLOWS_030100.

Just to let you know that you are not searching to long why your application doesn’t work after the upgrade.

Note: You may have installed this drop in replacements as optional part of the ApexLib Framework!

7 thoughts on “Attention! Are you using the Drop in replacement for V and NV function?

  1. Hi Patrick,

    I had a customer run into this last week, took a bit to track it down, they assumed at first it was a failed 3.1 upgrade, which wasn’t an unreasonable assumption as they noticed it right after they upgraded (the app worked well before). It was almost by chance that I thought to check if they’d installed the drop-ins.

    Is it possible perhaps (I haven’t dived into it too deeply to check) to check the installed version of APEX (and store it) when your drop-ins are installed and then when they are called and the session state isn’t available then it checks to see the current version of APEX and compares it against the version that was installed and propagates an exception up if they’re different (which could be more easily caught at the front end).

    So something like –

    1) User installs the drop-ins
    2) Drop-ins record (in a table or something) that they were installed against version 3.0
    3) All works fine…
    4) Environment upgraded to newer version of APEX
    5) Drop-ins cannot find item in session state, compare the current version of APEX against the version stored in 2 and barks an exception up the chain to let the user know what the problem is.

    Admittedly we’re talking about introducing an extra overhead in the drop-ins, but it could really help to narrow down the problem in future and that overhead should only become apparent when the items aren’t found in session state (which should in theory be more of an exception than the rule).

    It’s one of those things, you can end up on a wild-goose chase if you’re looking in the wrong place 😉

    Thanks Patrick,


  2. I’m sure Oracle support and the people supporting customers on the forum (that definately includes both you and John) would appreciate the addition of John’s suggestion.


  3. Hi Patrick,

    I ran into this myself during my upgrade. Not only is there a V() function in the APEXLIB schema, but there is another one in my parsing schema.

    As a suggestion, how difficult would it be to change the functions to include something like:
    SELECT max(username) from all_users where username like ‘FLOWS_0%’
    and use this to determine which schema to use? It ‘should’ always return the most current Apex schema, unless they change the format of the naming convention.

    Bill Ferguson

  4. Hi Bill,

    this would involve an extra SQL statement and a dynamic pl/sql call each time the function is invoked. I think the overhead would be to high.

    But I have already implemented a solution which does a version check and raise an exception so that the developer knows he has to change something.

    I just have not released it yet, because I have some more tests to do and I’m in the middle of the final preparation for my training next week.

    So in a week I should be able to release something.

  5. Hi Patrick,

    Let me ask a general comment here (and I see you already asked this in 2006 on the OTN forum). Why shouldn’t the DETERMINISTIC keyword be added to the v() function? This way, it will obviate your replacement functions and also ease the pain for customers upon upgrade.

    Sure – APEX-internal package variables are referenced by this function, but for a given invocation in a DML statement, I don’t believe any of these globals would change.

    With all this said, though, look at the text of the error message for ORA-30553: “Any user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future.”

    Strictly speaking, I believe the v() function is not deterministic. If you call v(‘P1_ITEM’) now, you aren’t guaranteed that v(‘P1_ITEM’) will return the same value in the future.


  6. That’s for sure. A call to v(‘P1_ITEM’) now and the same call in the future will likely return a different value. But it will be the same value within the execution of a SQL/DML statement, regardless if it’s called for the first or the last record.

    See also my original posting where I already raised the question about DETERMINISTIC and statement level execution.

    But you probably have more internal information if my assumption is dangerous or not. But I think it would performance improve a lot of APEX code.


Comments are closed.