SQL & PL/SQL

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

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

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.

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.

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.

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'));

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.

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.

I also tried calling the entire sql script

@"...\IRS.sql"

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

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

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.

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?

3631507 wrote:
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.

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.

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.

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

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.

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.

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.

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.

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.

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

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

> Thanks for suggestion. It doesn't work as shown below.

"ORA-04098: trigger 'RQUSER.RATING' is invalid and failed re-validation" shows us nothing except that the trigger is invalid. Show us the trigger code and the compilation error message.

Also, does the PL/SQL block I posted work or not and if not, what is the error message.

DECLARE

   dum INTEGER;

BEGIN

   EXECUTE IMMEDIATE

      q'{SELECT * FROM TABLE (rqEval (NULL, 'SELECT 1 dum FROM dual', 'IRS'))}'

      INTO dum;

END;

/

> 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

I figured that was what it is for. It is nonsense. The expression max (rownum) does not identify the latest row in a table it is essentially just COUNT(*). The predicate 'where rownum = n' doesn't return a row if n > 1 and even if it did it wouldn't work as ROWNUM is not the number of a row in a table, it is a pseudocolumn generated as rows are returned.

It's the same error as in the trigger

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

here is the simplest anonymous PL/SQL block I could come up with that you can experiment with, with your... requirement:

SQL> declare
  2    num number;
  3  begin
  4    execute immediate 'select 1 from dual' into num;
  5  end;
  6  /
PL/SQL procedure successfully completed.
SQL>

Notice how the result of the arbitrary select statement return value is put INTO the variable using EXECUTE IMMEDIATE syntax.

You need to do something like that. When you have THAT working put it into your trigger to test.

Thanks padders, your block

DECLARE

   dum INTEGER;

BEGIN

   EXECUTE IMMEDIATE

      q'{SELECT * FROM TABLE (rqEval (NULL, 'SELECT 1 dum FROM dual', 'IRS'))}'

      INTO dum;

END;

/

works now when didn't before. I can't explain why.

Thanks for all who helped with inputs and suggestions.

Well, good to know that you were able to solve the issue!

Thought issue was completely solved till I ran the trigger and encountered a quirk. It fires but processes the previous insert each time. For instance, I inserted rows A, B and C one at a time. Trigger processes A after B is inserted and then B after C is inserted. The trigger is clearly for AFTER INSERT. Searched the web and can't find a similar encounter posted.

Well, it appears that the R function creates a separate connection to the database. This separate connection will not see uncommitted transactions in other sessions (including the transaction which fired your trigger).

I don't know if R is able to inherit/be part of the invoking connection, you could investigate that possibility.

An alternative would be to make the R function call asynchronous, a simple 'traditional' way would be to submit (but not commit) a job via DBMS_JOB to execute the R function call. When the transaction is committed the job will be committed also. Advanced Queuing (DBMS_AQ) could be used similarly. However note that these asynchronous approaches would mean the job (and hence the R function) executes in its own transaction a short time after the table update is committed so there will be a period of time (however short) where the R data is not up to date.