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

This is  a slightly expanded version of the rqScript. It'll take a new credit applicant record, process data using a risk model and write the results into a table ApplicantRating. All these are done in R within the IRS function.

begin

1. sys.rqScriptCreate('IRS',

'function() {

library(ROracle)

drv <- dbDriver("Oracle")

con <- dbConnect(drv, username = "rquser", password="password",  dbname = "ORCLPDB", prefetch = FALSE,external_credentials = FALSE)

applicant <- dbGetQuery(con,"select * from loanapplication where rownum = (select max(rownum) from loanapplication)")

.

.

applicantrating <- applicantrating[c(1,16:28,43)]

dbWriteTable(con, "APPLICANTRATING", applicantrating, field.types = NULL, row.names = FALSE, overwrite = FALSE, append = TRUE, allow.keywords = FALSE)

dum <- 1

1. data.frame(dum = dum)

}',

v_overwrite => TRUE);

end;

/

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

I tried the cursor and loop (probably unneeded) as follows:

create or replace trigger rating

after insert on loanapplication

declare

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

lv_dum dual1.dum%type; --dual1 has been created

begin

  open IRS;

  loop

    fetch IRS into lv_dum;

    dbms_output_put_line ("dum:" || lv_dum);

  end loop;

  close IRS;

end;

and got even more errors on top of the earlier one about non-nested table item.

Thanks for suggestion. It doesn't work as shown below.

--Insert failed for row  1

--ORA-04098: trigger 'RQUSER.RATING' is invalid and failed re-validation

--Row 1

applicant <- dbGetQuery(con,"select * from loanapplication where rownum = (select max(rownum) from loanapplication)") is to select the most recent row into the data frame applicant. The trigger assigns a credit rating using a model and writes results into table with dbWriteTable. There's no need for any result in the SQL.

I've read this and others and have no problems getting ORE to work. So far, there's no reference or Oracle documentation on executing rqScript using a trigger where the problem is. Nobody in the R Technologies room of this forum responded to my post on this.

The rqScript has been fully tested and everything works without errors in a query browser. The output is written to the ApplicantRating table just as the script is designed to.  It works even if I just run the statement select * from table(rqEval(NULL,'select 1 dum from dual', 'IRS')); alone after inserting a row into LoanApplication. It's only when used in a trigger that it fails.