I'm hoping somebody can point me in the right direction, I can't figure this out at the moment.
Running a dev environment Oracle Database Enterprise 19c Version 126.96.36.199.0 on Redhat Linux 7 enterprise.
I get error PLS-00201L identifier 'DBMS_SODA_ADMIN' must be declared when calling a package that executes PL/SQL SODA operations from another package, procedure, function, trigger etc.
My user has the role SODA_APP and it can describe the package DBMS_SODA_ADMIN. There is a public synonym called DBMS_SODA_ADMIN owned by XDB.
I can execute the package with the SODA operations in SQL but when it is executed from a third package/function/trigger it bombs out with the error:
qp_soda_courses.qp_soda_qll_courses.others.ORA-06550: line 1, column 8:
PLS-00201: identifier 'DBMS_SODA_ADMIN' must be declared
Has anybody else come across this situation?
Roles don't work in stored procedures/functions with definer's rights:
All roles are disabled in any named PL/SQL block (stored procedure, function, or trigger) that executes with definer's rights. Roles are not used for privilege checking and you cannot set roles within a definer's rights procedure.
Here is the link to the Oracle 19 docs: