access problem with grant and CREATE_GROUP_FROM_QUERY

[hudo]

Hello,
I created a form which uses a hierarchical tree for navigation within the form.
The queries in the forms are based on stored procedures.
I created a role with grant execute on these stored procedures.
Then some other user are assigned to this role.

Problems arise by starting the form as a "non-owner-user", the errors
FRM-41072: Cannot create group
FRM-41076: Error filling the group
FRM-47322: Tree-source is norecord group
occur which are assigned to the group created for the navigation with
the hierarchical tree.
In general these errors occur when a group is created from a query, e.g.:



rg_navi := CREATE_GROUP_FROM_QUERY('rg_navi',
'SELECT 1 ,level ,thetext ,NULL ,TO_CHAR(nr) '||
'FROM NAVIGATION '||
'CONNECT BY PRIOR nr = lev '||

'START WITH UPPER(gohere) = ''STARTELEMENT'' ');

How can this problem be solved ?

 

[lewisp]

First, check you can run your SQL standalone in SQLPLUS connected as the user who is running your form.

Next, try granting execute privilege ditrectly to the user rather than through a role. I'm not sure if this is a 'feature' of Oracle, but granting execute of procedures to roles does not work.