Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

PL/SQL: ORA-22905: cannot access rows from a non-nested table item

3631507Oct 31 2019 — edited Nov 12 2019

I tried to create the following trigger:

create or replace trigger rating

after insert on loanapplication

begin

  select * from table(rqEval(NULL,'select 1 dum from dual', 'IRS'));

end;

and got the error PL/SQL: ORA-22905: cannot access rows from a non-nested table item

IRS is a Oracle R function and dum is just a dummy data frame. I posted this in the R Technologies room but haven't gotten a response.  I've also web-searched nested tables but can't find a solution.

Any help is appreciated.
This post has been answered by padders on Nov 2 2019
Jump to Answer

Comments

Your GRANT ALL to test for all tables provides that account with access to all schemas on the server.
If you want to keep test out of salaries but allow others, you first need to set up the grants on the employees.salaries table before blocking the test account. You have to do the equivalent of building the fence around the property before barring them or there is no way to block their access.
https://dev.mysql.com/doc/refman/8.0/en/grant.html#grant-column-privileges

dvohra21

The SELECT privilege can only be revoked at the level it is granted. Use
mysql> REVOKE SELECT ON employees.salaries FROM 'test'@'%';

1 - 2

Post Details

Added on Oct 31 2019
#trigger
37 comments
24,077 views