Oracle recommends and creates packages, procedures and views in APPS schema. Some customers create these objects in the custom schema like xxont, xxap, xxinv, etc.
It does not impact working of package or procedure. If there are calls to many standard built-in API which are under apps schema, you need to provide grants of all those objects to schema owner of the custom package.
grant execute on standard_api to xxont
It is fine if the grant is only for one or two objects, what if there are many?
Packages are always executed with the privilege of the creator. You can use AUTHID CURRENT_USER clause in package specification which will execute the package using the privilege of the current_user.
Use below script to do it in bulk. Copy all specification in one of UNIX directory and execute it. It will add AUTHID CURRENT_USER for all package specification.
This script is helpful during Oracle Apps Upgrade from 11.5.10 to 12.1.3 if you have not used AUTHID.
## Please test it before using ls | while read file_name do echo "$file_name" abc=`grep -ic AUTHID $file_name` if [ $abc -eq 0 ]; then sed -e '1,/AS/s/\<AS\>/AUTHID CURRENT_USER AS/' $file_name > temp mv temp $file_name fi done
This is the way it works,
- List and read each file in current directory
- Check if AUTHID is present
- If no use SED Unix command to add it.
Using above script you can add AUTHID CURRENT_USER to all package specification in bulk. It saves a lot of development time.
Please do let me know if you find this helpful in the comments section below.