Forum Stats

  • 3,853,608 Users
  • 2,264,246 Discussions
  • 7,905,414 Comments

Discussions

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

13

Answers

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Nov 1, 2019 7:12AM

    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).

  • 3631507
    3631507 Member Posts: 45
    edited Nov 1, 2019 8:36PM

    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.

  • 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
  • 3631507
    3631507 Member Posts: 45
    edited Nov 3, 2019 7:53PM

    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.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Nov 3, 2019 8:43PM

    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

  • 3631507
    3631507 Member Posts: 45
    edited Nov 3, 2019 10:53PM

    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.

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Nov 4, 2019 12:08AM
    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.

  • 3631507
    3631507 Member Posts: 45
    edited Nov 4, 2019 1:20AM

    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.

  • 3631507
    3631507 Member Posts: 45
    edited Nov 4, 2019 1:28AM

    This is how the result looks like when run in SQL Developer.

    pastedImage_0.png

  • Gaz in Oz
    Gaz in Oz Member Posts: 3,785 Bronze Crown
    edited Nov 4, 2019 2:16AM

    Do it in sqldev in an anonymous PL/SQL block.