Running ORE from PL/SQL

765466

Is it possible to run embedded R script from PL/SQL? All the examples are for SQL.

When I try to run SQL (embedded R) in PL/SQL, I get this error:

ORA-06550: line 2, column 37:

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

ORA-06550: line 2, column 4:

PL/SQL: SQL Statement ignored

06550. 00000 -  "line %s, column %s:\n%s"

*Cause:    Usually a PL/SQL compilation error.

Code used:

begin

   select 1230000, 1, 123, val from table(rqTableEval(

    cursor(select 1,2,3 from dual),

    NULL,

    'select 1 val from dual',

    'PR12')

  );

end;

Working code:

select 1230000, 1, 123, val from table(rqTableEval(

    cursor(select 1,2,3 from dual),

    NULL,

    'select 1 val from dual',

    'PR12')

  );

# PR12 is just a simple function.

Thank you,
George

rtiran

Hello Georges,

It is possible but it's a little bit convoluted...

In short, you have to cast the output of rqTableEval to a globally defined type. Furthermore that type must be a nested table or a varray.

Let's consider that your PR12 R function is computing a product of its arguments:

SQL> BEGIN

  2      sys.rqScriptCreate ('PR12', 'function(x, param) {

  3                     df <- data.frame(x, stringsAsFactors=F)

  4                     as.data.frame(apply(df,1,prod))

  5                     }');

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

That function can be invoked directly in SQL by casting the result of rqTableEval to a relational table with the TABLE operator:

SQL> SELECT 1230000,

  2         1,

  3         123,

  4         val

  5    FROM TABLE (rqTableEval (CURSOR (SELECT 1, 2, 3 FROM DUAL),

  6                             NULL,

  7                             'select 1 val from dual',

  8                             'PR12'));

   1230000          1        123        VAL

---------- ---------- ---------- ----------

   1230000          1        123          6

SQL>

However, to use that function in PL/SQL, you have first to define a global type that match the expected output of rqTableEval (see T1 below). That type can be complex - with many members - if needed. Then a second type being a table of the first (see T2 below) has to be defined. Then you can cast the output of rqTableEval to that second type and access the data with regular PL/SQL processing:

SQL> CREATE TYPE T1 AS OBJECT (val NUMBER)

  2  /

Type created.

SQL> CREATE TYPE T2 AS TABLE OF T1

  2  /

Type created.

SQL>

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2      V1   NUMBER;

  3      V2   NUMBER;

  4      V3   NUMBER;

  5      V4   T2;

  6  BEGIN

  7      SELECT 1230000,

  8             1,

  9             123,

10             CAST (rqTableEval (CURSOR (SELECT 1, 2, 3 FROM DUAL),

11                                NULL,

12                                'select 1 val from dual',

13                                'PR12') AS T2)

14        INTO V1,

15             V2,

16             V3,

17             V4

18        FROM DUAL;

19

20      DBMS_OUTPUT.put_line ('V1:' || V1);

21      DBMS_OUTPUT.put_line ('V2:' || V2);

22      DBMS_OUTPUT.put_line ('V3:' || V3);

23      DBMS_OUTPUT.put_line ('V4(1):' || V4 (1).val);

24  END;

25  /

V1:1230000

V2:1

V3:123

V4(1):6

PL/SQL procedure successfully completed.

SQL>

If, instead of using "select 1, 2, 3 from dual" as rqTableEval argument, you use a real table. You'll have to iterate through the rows of the resulting rqTableEval dataset:

SQL> CREATE TABLE t

  2  (

  3      a   NUMBER,

  4      b   NUMBER,

  5      c   NUMBER

  6  );

Table created.

SQL> INSERT INTO t

  2       VALUES (4, 5, 6);

1 row created.

SQL> INSERT INTO t

  2       VALUES (7, 8, 9);

1 row created.

SQL> INSERT INTO t

  2       VALUES (10, 11, 12);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2      V1   NUMBER;

  3      V2   NUMBER;

  4      V3   NUMBER;

  5      V4   T2;

  6  BEGIN

  7      SELECT 1230000,

  8             1,

  9             123,

10             CAST (rqTableEval (CURSOR (SELECT * FROM t),

11                                NULL,

12                                'select 1 val from dual',

13                                'PR12') AS T2)

14        INTO V1,

15             V2,

16             V3,

17             V4

18        FROM DUAL;

19

20      DBMS_OUTPUT.put_line ('V1:' || V1);

21      DBMS_OUTPUT.put_line ('V2:' || V2);

22      DBMS_OUTPUT.put_line ('V3:' || V3);

23

24      FOR i IN V4.FIRST .. V4.LAST

25      LOOP

26          DBMS_OUTPUT.put_line ('V4(' || i || '):' || V4 (i).val);

27      END LOOP;

28  END;

29  /

V1:1230000

V2:1

V3:123

V4(1):120

V4(2):504

V4(3):1320

PL/SQL procedure successfully completed.

SQL>

Thast said, if the resultset returned by rqTableEVal is huge, that approach can be a memory hog.

In that case, another possibility is to use the workaround explained in  Can not access rows from a non-netsed table item in embedded R execution

Hope this helps!

Raphaël

rtiran

Hello Georges,

It is possible but it's a little bit convoluted...

In short, you have to cast the output of rqTableEval to a globally defined type. Furthermore that type must be a nested table or a varray.

Let's consider that your PR12 R function is computing a product of its arguments:

SQL> BEGIN

  2      sys.rqScriptCreate ('PR12', 'function(x, param) {

  3                     df <- data.frame(x, stringsAsFactors=F)

  4                     as.data.frame(apply(df,1,prod))

  5                     }');

  6  END;

  7  /

PL/SQL procedure successfully completed.

SQL>

That function can be invoked directly in SQL by casting the result of rqTableEval to a relational table with the TABLE operator:

SQL> SELECT 1230000,

  2         1,

  3         123,

  4         val

  5    FROM TABLE (rqTableEval (CURSOR (SELECT 1, 2, 3 FROM DUAL),

  6                             NULL,

  7                             'select 1 val from dual',

  8                             'PR12'));

   1230000          1        123        VAL

---------- ---------- ---------- ----------

   1230000          1        123          6

SQL>

However, to use that function in PL/SQL, you have first to define a global type that match the expected output of rqTableEval (see T1 below). That type can be complex - with many members - if needed. Then a second type being a table of the first (see T2 below) has to be defined. Then you can cast the output of rqTableEval to that second type and access the data with regular PL/SQL processing:

SQL> CREATE TYPE T1 AS OBJECT (val NUMBER)

  2  /

Type created.

SQL> CREATE TYPE T2 AS TABLE OF T1

  2  /

Type created.

SQL>

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2      V1   NUMBER;

  3      V2   NUMBER;

  4      V3   NUMBER;

  5      V4   T2;

  6  BEGIN

  7      SELECT 1230000,

  8             1,

  9             123,

10             CAST (rqTableEval (CURSOR (SELECT 1, 2, 3 FROM DUAL),

11                                NULL,

12                                'select 1 val from dual',

13                                'PR12') AS T2)

14        INTO V1,

15             V2,

16             V3,

17             V4

18        FROM DUAL;

19

20      DBMS_OUTPUT.put_line ('V1:' || V1);

21      DBMS_OUTPUT.put_line ('V2:' || V2);

22      DBMS_OUTPUT.put_line ('V3:' || V3);

23      DBMS_OUTPUT.put_line ('V4(1):' || V4 (1).val);

24  END;

25  /

V1:1230000

V2:1

V3:123

V4(1):6

PL/SQL procedure successfully completed.

SQL>

If, instead of using "select 1, 2, 3 from dual" as rqTableEval argument, you use a real table. You'll have to iterate through the rows of the resulting rqTableEval dataset:

SQL> CREATE TABLE t

  2  (

  3      a   NUMBER,

  4      b   NUMBER,

  5      c   NUMBER

  6  );

Table created.

SQL> INSERT INTO t

  2       VALUES (4, 5, 6);

1 row created.

SQL> INSERT INTO t

  2       VALUES (7, 8, 9);

1 row created.

SQL> INSERT INTO t

  2       VALUES (10, 11, 12);

1 row created.

SQL> COMMIT;

Commit complete.

SQL>

SQL> SET SERVEROUTPUT ON

SQL> DECLARE

  2      V1   NUMBER;

  3      V2   NUMBER;

  4      V3   NUMBER;

  5      V4   T2;

  6  BEGIN

  7      SELECT 1230000,

  8             1,

  9             123,

10             CAST (rqTableEval (CURSOR (SELECT * FROM t),

11                                NULL,

12                                'select 1 val from dual',

13                                'PR12') AS T2)

14        INTO V1,

15             V2,

16             V3,

17             V4

18        FROM DUAL;

19

20      DBMS_OUTPUT.put_line ('V1:' || V1);

21      DBMS_OUTPUT.put_line ('V2:' || V2);

22      DBMS_OUTPUT.put_line ('V3:' || V3);

23

24      FOR i IN V4.FIRST .. V4.LAST

25      LOOP

26          DBMS_OUTPUT.put_line ('V4(' || i || '):' || V4 (i).val);

27      END LOOP;

28  END;

29  /

V1:1230000

V2:1

V3:123

V4(1):120

V4(2):504

V4(3):1320

PL/SQL procedure successfully completed.

SQL>

Thast said, if the resultset returned by rqTableEVal is huge, that approach can be a memory hog.

In that case, another possibility is to use the workaround explained in  Can not access rows from a non-netsed table item in embedded R execution

Hope this helps!

Raphaël