Forum Stats

  • 3,836,754 Users
  • 2,262,182 Discussions
  • 7,900,094 Comments

Discussions

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

24

Answers

  • 3631507
    3631507 Member Posts: 45
    edited Oct 31, 2019 9:02PM

    The block will not work as it doesn't comply with the rqScript syntax. The key is that the SELECT statement works to execute the rqScript which is what I want to accomplish. That works outside of a trigger. I would like to find a way for it to do the same inside a trigger.

  • 3631507
    3631507 Member Posts: 45
    edited Oct 31, 2019 9:28PM

    If you've ORE installed, you can test the following and it will work.

    begin

    sys.rqScriptCreate('demo',

    'function() {

    library(ROracle)

    drv <- dbDriver("Oracle")

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

    df <- dbGetQuery(con,"select * from any_table where rownum = (select max(rownum) from any_table)")

    dum <- 1

    data.frame(dum = dum)

    }',

    v_overwrite => TRUE);

    end;

    /

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

    You'll see a 1 for the dum variable on the screen only but the R data frame df is created and can be used for further processing.

    You can also run this alone in a browser and it'll also work.

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

  • L. Fernigrini
    L. Fernigrini Data Engineer Sr Consultant Member Posts: 3,851 Silver Crown
    edited Oct 31, 2019 9:36PM

    We already told you, Gaz mentioned it on the first answer, you need to use the INTO clause to "receive" the results of the SELECT query. You cannot use SELECT without INTO in a PL/SQL block.

  • 3631507
    3631507 Member Posts: 45
    edited Oct 31, 2019 9:47PM

    I tried the INTO by creating a table DUAL1 and modified the SELECT statement

    select * INTO DUAL1 from table(rqEval(NULL,'select 1 dum from dual', 'demo'));

    The rqScript wouldn't run.

  • 3631507
    3631507 Member Posts: 45
    edited Oct 31, 2019 10:24PM

    I also tried calling the entire sql script

    @...\IRS.sql

    which runs fine as a statement in a browser but fails in a trigger.

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

    Now, after all the advice you have received what does your current trigger code look like?

  • 3631507
    3631507 Member Posts: 45
    edited Oct 31, 2019 11:27PM

    I tried these 3 and none works. (3) runs with no errors but no result either which's what I expected as it only compiles the rqScript without executing

    (1)

    create or replace trigger analytics

    after insert on loanapplication

    begin

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

    end;

    (2)

    create or replace trigger analytics

    after insert on loanapplication

    begin

      @...\IRS.sql

    end;

    (3)

    create or replace trigger analytics

    after insert on loanapplication

    declare rating varchar(20);

    begin

      rating := IRS();

    end;

    BTW, I fully understand the conventional SQL syntax wrt INTO as I've done a lot of T-SQL and have successfully created triggers and stored procedures before but ORE has its own syntax that requires strict compliance else the rqScript wouldn't run.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Nov 1, 2019 6:00AM

    1 doesn't work for the reason everyone keeps telling you -- you need an INTO clause.

    2 doesn't work because @ is a sqlplus command to run a file. It is not a PL/SQL or SQL command, the database doesn't understand it.

    What do you want to have happen with the results of the call to rqEval?

  • William Robertson
    William Robertson Member Posts: 9,568 Bronze Crown
    edited Nov 1, 2019 6:12AM
    3631507 wrote:I tried the INTO by creating a table DUAL1 and modified the SELECT statementselect * INTO DUAL1 from table(rqEval(NULL,'select 1 dum from dual', 'demo'));The rqScript wouldn't run.

    No, that’s still not right. The target of SELECT INTO can’t be a table name. It needs to be a variable. There are probably some examples in the tutorials posted earlier.

    You might also try it as a cursor FOR loop, which would let you loop through the results without needing to capture them into an array etc.

  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410 Gold Trophy
    edited Nov 1, 2019 6:38AM

    For loop would probably be simplest (how did I not think of that?) however the current error suggests it's not currently complaining about the lack of an into.

    William Robertson