Skip to Main Content
  • Questions
  • How to create a cursor of select * from dual with input arguments?

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Jie.

Asked: February 07, 2019 - 9:56 pm UTC

Last updated: February 12, 2019 - 1:13 am UTC

Version: 18.0

Viewed 10K+ times! This question is

You Asked

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?

and Connor said...

You should be able to just open the ref cursor in isolation and then pass it in as a regular parameter, eg

SQL> create or replace
  2  function ff(rc sys_refcursor) return int is
  3    x int;
  4  begin
  5    fetch rc into x;
  6    return 1;
  7  end;
  8  /

Function created.

SQL>
SQL> declare
  2    rc sys_refcursor;
  3    q int;
  4  begin
  5    open rc for select 99 from dual;
  6    q := ff(rc);
  7  end;
  8  /

PL/SQL procedure successfully completed.


Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

the function requires a cursor as an input

Jie Liu, February 11, 2019 - 5:58 pm UTC

Hi Connor,

Thank you very much. I think the main reason I am struggling here is that this API rqEval requires a cursor as its input:
rqEval (
PAR_CUR REF CURSOR IN
OUT_QRY VARCHAR2 IN)
EXP_NAM VARCHAR2 IN)


I am not sure if I can still do a fetch * into in this case.

I tried the following but it gets stuck forever.

DECLARE
partition_method VARCHAR2(100);
query_type VARCHAR2(100);
part_param NUMBER;
selectivity NUMBER;
pred NUMBER;
v_Success NUMBER;
cursorInput SYS_REFCURSOR;
cursorOutput SYS_REFCURSOR;
BEGIN
partition_method:= 'HASH';
query_type := '=';
part_param := 10;
selectivity := 0.1;
OPEN cursorInput FOR SELECT 'x2' "run_environ", partition_method "partition_method", query_type "query_type", part_param "part_param", selectivity "selectivity" FROM dual;
OPEN cursorOutput FOR SELECT * FROM table(rqEval(cursorInput, 'SELECT 1 SCORE FROM DUAL', 'par_score_scripts'));
FETCH cursorOutput INTO v_Success;

DBMS_OUTPUT.PUT_LINE('Score: '|| v_Success||'!');
END;
/

I am thinking about declaring a cursor by

CURSOR cur IS SELECT input_argument from DUAL;

But input_argument is something defined later.

Thanks,
Jie

Connor McDonald
February 12, 2019 - 1:13 am UTC

My point was, you can open a cursor separately from the function call. So in your case, it would be:

open rc for 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(rc, 'SELECT 1 SCORE FROM DUAL', 'par_score_scripts'));   


Thanks

Jie Liu, February 12, 2019 - 9:13 pm UTC

Hi,

Thanks a lot. I think that makes sense.

Actually I find that it is the 2nd part of the code that causes the hanging.

OPEN cursorOutput FOR SELECT * FROM table(rqEval(CURSOR(SELECT 'x2' "run_environ", 'HASH' "partition_method", '=' "query_type", 10 "part_param", 0.1 "selectivity" FROM dual), 'SELECT 1 SCORE FROM DUAL', 'par_score_scripts'));
DBMS_OUTPUT.PUT_LINE('Cursor opened!');
FETCH cursorOutput INTO v_Success;
CLOSE cursorOutput;

But running directly from sql does not hang:
select * from table(rqEval(CURSOR(SELECT 'x2' "run_environ", 'HASH' "partition_method", '=' "query_type", 10 "part_param", 0.1 "selectivity" FROM dual), 'SELECT 1 SCORE FROM DUAL', 'par_score_scripts'));
I will probably open another question for that issue.

Thanks,
Jie

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library