Get_release Oracle API returns "Unknown" result instead of the correct version

Question

When the get_release function is executed below, the correct version is returned from application users: 12.1.3 , but when it is executed, an Unknown result is returned from another user:

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 restrictions when 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 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. 

Has anyone encountered this problem before ?!

Answer 1

As far as I can see, there are 3 possibilities:

• The FND_RELEASE package compiled with invoker_rights_clause ( AUTHID CURRENT_USER ). And it uses a different source for the apps user and for any other user.
• the FND_RELEASE.get_release function uses context for access.
• Your system uses the private database option. He is able to rewrite requests and add conditions to the current user. (Change point 2, but implicitly)

Answer 2

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

In this software package, FND_RELEASE declared as AUTHID CURRENT_USER (i.e. it uses invoker rights). This can be seen in the first line of the package specification.

This means that the table FND_PRODUCT_GROUPS considered in this query ...

 select release_name from fnd_product_groups order by product_group_id; 

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

Since this user probably does not have the FND_PRODUCT_GROUPS table, FND_RELEASE generates error ORA-00942: table or view does not exist when trying to execute this query.

This causes it to go to its WHEN OTHERS exception handler, which causes it to return false when the output parameters are set to Unknown.

One way to solve this problem is to create a custom wrapper for FND_RELEASE that uses certain 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 this and change 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; 

SEQUENTIAL WORK

If you cannot or do not want to create a new wrapper package in the APPS scheme, you can simply create a synonym for FND_PRODUCT_GROUPS in your other custom scheme.

For instance,

 create or replace synonym fnd_product_groups FOR apps.fnd_product_groups; 

... do it in your other circuit and your original script will work.

The disadvantage of this alternative is that the Oracle patch can change the FND_RELEASE logic to introduce other queries into other tables, as a result of which this solution will break until you create any additional synonyms.