get_release Oracle API returning "Unknown" result instead of correct release

Question

While executing below get_release function from apps user it’s returning the correct release: 12.1.3 , but when execute it from another user it’s returning Unknown result:

declare
l_release_name         varchar2(30);
l_other_release_info   varchar2(2000);
   begin
     if not apps.FND_RELEASE.get_release (l_release_name, l_other_release_info) then
         null;
     end if;
     dbms_output.put_line(l_release_name);
   end;

FND_RELEASE.get_release is a public package there should be no restriction on calling it from another user.

Below what I found in the function comments:

-- get_release() will usually return TRUE
  --  with RELEASE_NAME =
  --                    contents of RELEASE_NAME column in FND_PRODUCT_GROUPS
  --  and OTHER_RELEASE_INFO = null
  --
  -- If FND_PRODUCT_GROUPS.RELEASE_NAME contains imbedded spaces:
  --
  -- get_release() will return TRUE
  --  with RELEASE_NAME = FND_PRODUCT_GROUPS.RELEASE_NAME up to but
  --   not including the first imbedded space
  --  and OTHER_RELEASE_INFO = FND_PRODUCT_GROUPS.RELEASE_NAME
  --   starting with the first non-space character after the first
  --   imbedded space
  --
  -- On failure, get_release() returns FALSE. This will be a performance issue.
  --  Both RELEASE_NAME and OTHER_RELEASE_INFO will be set to 'Unknown'.
  --  This indicates that either:
  --  1) there are no rows in fnd_product_groups
  --     - this can be resolved by populating the row and it will
  --       be queried on the next call.
  --  2) there is more than one row in fnd_product_groups
  --     - delete all but the one correct row from fnd_product_groups and it
  --       will be queried on the next call. It's possible that the values
  --       returned by release_* and *_version routines are still correct if
  --       the first row in fnd_product_groups, ordered by product_group_id,
  --       if the currect row, but this will still be a performance problem.

Anybody face this problem before?!

Answer 1

As far as I see There is 3 possibilities:

1. Package FND_RELEASE compiled with invoker_rights_clause (AUTHID CURRENT_USER). And it use different source for user apps and for any other user.
2. function FND_RELEASE.get_release use context for access.
3. In your system used private database option. It able to rewrite queries and add condition to current user.(Variation of point 2 but implicitly)

Answer 2

I see you are using the Oracle e-Business Suite data model and packages.

In that software package, FND_RELEASE is declared as AUTHID CURRENT_USER (i.e., it uses invoker's rights). You can see this in the 1st line of the package specification.

That means that the FND_PRODUCT_GROUPS table being looked at in this query...

select   release_name
from     fnd_product_groups
order by product_group_id;

... is NOT APPS.FND_PRODUCT_GROUPS.. it is the FND_PRODUCT_GROUPS table owned by the current user.

Since that user probably doesn't have an FND_PRODUCT_GROUPS table, FND_RELEASE is raising an ORA-00942: table or view does not exist error when it tries to run that query.

That causes it to jump down to its WHEN OTHERS exception handler, which causes it to return false with the output parameters set to "Unknown".

One way around this issue is to create a custom wrapper for FND_RELEASE that uses definer's rights. Like this:

create or replace package xxcust_fnd_release AUTHID DEFINER IS

  function get_release (release_name       out nocopy varchar2,
                        other_release_info out nocopy varchar2)
  return boolean;
end xxcust_fnd_release;


create or replace package body xxcust_fnd_release IS

  function get_release (release_name       out nocopy varchar2,
                        other_release_info out nocopy varchar2)
  return boolean IS
  BEGIN
    return fnd_release.get_release (release_name, other_release_info);
  END;
end xxcust_fnd_release;

grant execute on xxcust_fnd_release to <your_other_username>;

If you do that, and modify your script as follows, it will work:

declare
l_release_name         varchar2(30);
l_other_release_info   varchar2(2000);
   begin
     if not apps.XXCUST_FND_RELEASE.get_release (l_release_name, l_other_release_info) then
         null;
     end if;
     dbms_output.put_line(l_release_name);
   end;

ALTERNATE WORKAROUND

If you cannot or do not want to create a new wrapper package in the APPS schema, you can just create a synonym for FND_PRODUCT_GROUPS in your other user's schema.

E.g.,

create or replace synonym fnd_product_groups FOR apps.fnd_product_groups;

... do that in your other schema and your original script will work.

The downside to this alternative is that an Oracle patch may change the logic of FND_RELEASE to introduce other queries on other tables, causing this solution to break until you create whatever additional synonyms are required.