Forum Stats

  • 3,760,186 Users
  • 2,251,659 Discussions
  • 7,871,017 Comments

Discussions

Running ORE from PL/SQL

765466
765466 Member Posts: 10
edited Oct 19, 2017 12:59PM in R Technologies

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

Best Answer

  • rtiran
    rtiran Member Posts: 74 Bronze Badge
    edited May 24, 2017 5:01AM Accepted Answer

    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

Answers

  • rtiran
    rtiran Member Posts: 74 Bronze Badge
    edited May 24, 2017 5:01AM Accepted Answer

    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

This discussion has been closed.