Discussions
Categories
- 197.1K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.7K Databases
- 221.9K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 31 Multilingual Engine
- 552 MySQL Community Space
- 479 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.1K ORDS, SODA & JSON in the Database
- 555 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.3K SQL Developer
- 296.3K Development
- 17 Developer Projects
- 139 Programming Languages
- 293K Development Tools
- 109 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 158 Java Community Process
- 105 Java 25
- 22.1K Java APIs
- 138.2K Java Development Tools
- 165.3K Java EE (Java Enterprise Edition)
- 19 Java Essentials
- 162 Java 8 Questions
- 86K Java Programming
- 81 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 204 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 466 LiveLabs
- 39 Workshops
- 10.2K Software
- 6.7K Berkeley DB Family
- 3.5K JHeadstart
- 5.7K Other Languages
- 2.3K Chinese
- 175 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 233 Portuguese
PL/SQL: ORA-22905: cannot access rows from a non-nested table item
Answers
-
True. The original error "ORA-22905: cannot access rows from a non-nested table item" means it couldn’t parse the returned object as a collection type, so maybe it needs an explicit CAST, or else it’s running into some limitation around polymorphic table functions (since they are a new and somewhat exotic feature).
-
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
- 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
- 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.
-
Generally the problem with these sorts of table functions is that you can't infer what the result set will be (and the PL/SQL compiler can't infer it either).
In the case of the rqEval function you are using the return type appears to be determined by the second parameter, in which case something like this might work.
DECLARE
dum INTEGER;
BEGIN
EXECUTE IMMEDIATE
q'{SELECT * FROM TABLE (rqEval (NULL, 'SELECT 1 dum FROM dual', 'IRS'))}'
INTO dum;
END;
/
Although I would prefer you didn't use * if the result set is known. If the result set is not determined by the parameters then dynamic SQL method 4 may be required.
By the way, the below query within your R function appears to be nonsense.
applicant <- dbGetQuery(con,"select * from loanapplication where rownum = (select max(rownum) from loanapplication)")
-
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.
-
Perhaps reading this article will help you get a better understanding of calling "embedded R" from PL/SQL
https://blogs.oracle.com/r/introduction-to-ore-embedded-r-script-execution
-
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.
-
I've read this and others and have no problems getting ORE to work.
Forget about Oracle triggers for a moment, does that mean you have manged to successfully call your R function from PL/SQL?
(The Oracle trigger body is coded using PL/SQL None of what you have posted so far is valid PL/SQL code).
Try getting the syntax correct in PL/SQL if you haven't already, then try using that code in your trigger.
-
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.
-
This is how the result looks like when run in SQL Developer.
-
Do it in sqldev in an anonymous PL/SQL block.