Forum Stats

  • 3,840,319 Users
  • 2,262,585 Discussions
  • 7,901,216 Comments

Discussions

Categories

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

3631507
3631507 Member Posts: 45
edited Nov 12, 2019 3:14AM in SQL & PL/SQL

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.

Tagged:
William Robertson
· Share on TwitterShare on Facebook

Best Answer

  • padders
    padders Member Posts: 1,081 Silver Trophy
    edited Nov 2, 2019 7:49AM 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)")

    William Robertson
    · Share on TwitterShare on Facebook
«134

Answers

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Oct 31, 2019 4:50AM

    Your trigger syntax is incorrect. What do you want to do with the result of the call of the nested table?

    · Share on TwitterShare on Facebook
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,861 Silver Crown
    edited Oct 31, 2019 7:18AM

    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/

    · Share on TwitterShare on Facebook
  • 3631507
    3631507 Member Posts: 45
    edited Oct 31, 2019 9:08AM

    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.

    · Share on TwitterShare on Facebook
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,861 Silver Crown
    edited Oct 31, 2019 9:18AM

    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.

    · Share on TwitterShare on Facebook
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,861 Silver Crown
    edited Oct 31, 2019 9:22AM

    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

    · Share on TwitterShare on Facebook
  • padders
    padders Member Posts: 1,081 Silver Trophy
    edited Oct 31, 2019 12:15PM

    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

    · Share on TwitterShare on Facebook
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Oct 31, 2019 12:21PM

    https://docs.oracle.com/cd/E67822_01/OREUG/GUID-1B733E9C-D6C6-4BF0-98CC-CDC2F4D694A5.htm#OREUG-GUID-1B733E9C-D6C6-4BF0-98CC-CDC2F4D694A5

    · Share on TwitterShare on Facebook
  • padders
    padders Member Posts: 1,081 Silver Trophy
    edited Oct 31, 2019 12:25PM

    Thanks, from that link...

    Return Value

    Function rqEval returns a table that has the structure specified by the OUT_QRY parameter value.

    So I'm assuming this function is indeed RTTI or polymorphic table function.

    · Share on TwitterShare on Facebook
  • 3631507
    3631507 Member Posts: 45
    edited Oct 31, 2019 7:51PM

    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.

    · Share on TwitterShare on Facebook
  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,861 Silver Crown
    edited Oct 31, 2019 8:51PM 
    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.

    · Share on TwitterShare on Facebook