Discussions
Categories
- 197K All Categories
- 2.5K Data
- 546 Big Data Appliance
- 1.9K Data Science
- 450.9K 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
- 556 SQLcl
- 4K SQL Developer Data Modeler
- 187.2K SQL & PL/SQL
- 21.4K SQL Developer
- 296.4K Development
- 17 Developer Projects
- 139 Programming Languages
- 293.1K Development Tools
- 111 DevOps
- 3.1K QA/Testing
- 646.1K Java
- 28 Java Learning Subscription
- 37K Database Connectivity
- 161 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
- 205 Java User Groups
- 24 JavaScript - Nashorn
- Programs
- 475 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

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.
Best Answer
-
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)")
Answers
-
Your trigger syntax is incorrect. What do you want to do with the result of the call of the nested table?
-
When using PL/SQL, the SELECT sentence must return the data INTO something (variable, record, etc)
Here are some examples:
https://www.oracletutorial.com/plsql-tutorial/plsql-select-into/
-
Thanks for the quick response. The SELECT statement is required to execute the rqScript IRS. All the I/O's are in that script. The statement runs fine if it's at the end of the rqScript like
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)")
dum <- 1
data.frame(dum = dum)
}',
v_overwrite => TRUE);
end;
/
select * from table(rqEval(NULL,'select 1 dum from dual', 'IRS'));
It can be run stand alone in a query browser and the rqScript will still execute. Without the statement, the function will just compile successfully with no result.
It's only in a trigger that the statement can't work.
-
Again, the script you have run returns data on your screen, since you are using a SELECT (in SQL, not in PL/SQL) sentence on SQL*Plus or SQL*Developer.
If you want to use a SELECT inside a PL/SQL block you need to "store" whatever it returns INTO something (see various examples in the link I provided)
A trigger cannot "show" anything on the screen (it runs on the database), nor return data. Please explain what do you want to do with whatever is returned by your SELECT sentence.
-
To "confirm" that the problem is not the trigger, but actually any PL/SQL block (since SELECT in PL/SQL requires the INTO clause), try running this:
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)")
dum <- 1
data.frame(dum = dum)
}',
v_overwrite => TRUE);
select * from table(rqEval(NULL,'select 1 dum from dual', 'IRS'));
end;
/
You will get the an error
-
What is rqEval function? Is it RTTI or polymorphic table function?
If so then only the latest versions of PL/SQL (I believe) support static usage, possibly dynamic SQL might work.
Padders
-
-
Thanks, from that link...
Return Value
Function
rqEval
returns a table that has the structure specified by theOUT_QRY
parameter value.So I'm assuming this function is indeed RTTI or polymorphic table function.
-
Thanks for all the inputs.
I'm not IT trained and concepts like RTTI or polymorphic table function are totally new to me. All I'm trying to do is to use Oracle R Enterprise to automate a process of immediately assigning a credit rating once an application is received with a trigger. The rqScript posted (actual one is much longer) has been tested and works fine. The select * from table(rqEval(NULL,'select 1 dum from dual', 'IRS')); has to be placed in the position specified and cannot be inside the function (see https://blogs.oracle.com/r/introduction-to-ore-embedded-r-script-execution, for e.g.). Statement works even if used alone in a query browser as IRS has been stored as a procedure according to Oracle documentation. It just generates a screen result (1 for the variable dum) as intended. All I/O's wrt to Oracle tables are contained in the rqScript.
It's only in a trigger that the statement fails.
I'm using Oracle 12 and SQL Developer 4.1.4. A solution will be much appreciated.
-
It's only in a trigger that the statement fails.
No, that is false. It fails inside ANY pl/sql block, as I already mentioned twice, since PL/SQL cannot just return data to screen like SQL does (when using an IDE like SQL*Plus or SQL Developer).
Have you tested the block I posted? Does it work or fails? And it is not a trigger, it is just an anonymous PL/SQL block.
You need to get whatever is returned by the SELECT into (using the INTO clause) something, and then do whatever processing you want with those results, but you MUST use the INTO clause in SELECT inside a PL/SQL block.