Hi Tom,
Recently I am using the rqEval function which is part of Oracle Advanced Analytics. The input of this function includes a cursor such as
SELECT *
FROM table(rqEval(cursor(SELECT 50 "divisor", 500 "numDots" FROM dual),
'SELECT 1 id, 1 val FROM dual',
'myRandomRedDots2'));
In my case, I need to run this within PL/SQL, and wish to pass input arguments and create a cursor from there. Here is what I did:
DECLARE
partition_method VARCHAR2(100);
query_type VARCHAR2(100);
part_param NUMBER;
selectivity NUMBER;
pred NUMBER;
v_Success NUMBER;
cursorInput REF CURSOR
BEGIN
partition_method:= 'HASH';
query_type := '=';
part_param := 10;
selectivity := 0.1;
cursorInput := cursor(SELECT 'x2' "run_environ", partition_method "partition_method", query_type "query_type", part_param "part_param", selectivity "selectivity" FROM dual);
SELECT * INTO v_Success FROM table(rqEval(cursorInput, 'SELECT 1 SCORE FROM DUAL', 'par_score_scripts'));
DBMS_OUTPUT.PUT_LINE('Score: '|| v_Success||'!');
END;
/
But then it throws an error:
Error report -
ORA-06550: line 14, column 27:
PLS-00103: Encountered the symbol "SELECT" when expecting one of the following:
( ) - + case mod new not null <an identifier>
<a double-quoted delimited-identifier> <a bind variable>
table continue avg count current exists max min prior sql
stddev sum variance execute multiset the both leading
trailing forall merge year month day hour minute second
timezone_hour timezone_minute timezone_region timezone_abbr
time timestamp interval date
<a string literal with character set specifica
ORA-06550: line 14, column 176:
PLS-00103: Encountered the symbol ")" when expecting one of the following:
. , @ ; for <an identifier>
<a double-quoted delimited-identifier> group having intersect
minus order partition start subpartition union where connect
sample
ORA-06550: line 19, column 0:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of the following:
end not pragma final instantiable persistable order
overriding static member constructor map
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
What could be the correct to construct a cursor from select var from dual, where var is input variables?