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

3631507

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

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

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

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

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

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

3631507

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

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

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

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.