Today, while writing the stored procedure, I found a very strange problem, stored in the process of a simple select dynamic SQL statement. After writing, execute this stored procedure, always prompt ora-00942:table or view does not exist (ERROR-942 ORA-00942: The table or view does not exist) this error, but I copy this SQL statement and run it alone at the command line , without any mistakes. Very tangled, very puzzled, very no solution, a good SELECT statement, in the command line can be executed, put the stored procedure will be wrong, how is wrong.
Then after Google, analysis and summary, finally found the answer. To satisfy some readers is to find the answer to the problem, and do not need the next lengthy analysis, I will give the answer here first.
Roles are invalidated in functions, stored procedures, and triggers, that is, permissions that a user inherits from a role that cannot be used in functions, stored procedures, triggers. In functions, stored procedures, triggers, if you want to access other users ' objects, you need to explicitly grant access to the user.
It also says that because of the DBA role given to the user jelly2, and the role permissions are invalidated in the stored procedure, in order to prevent access rights from being invalidated, we need to display the permissions that the Jelly2 user gives access to the Jelly.tb_student table.
Grant Select any table to Jelly2;