Getting an cursor as result when using begin and end block

Question

I'm trying to use the results of multiple slq statements for one result(cursor) in the firedac query editor:

Sql Command in editor

DECLARE
 MyVar1 varchar2(100);
 MyVar2 varchar2(100);

BEGIN
  SELECT USERINCDE INTO MyVar1 FROM P_USR WHERE USEREXCDE='PH1';
  SELECT USERINCDE INTO MyVar2 FROM P_USR WHERE USEREXCDE='PH2';
END;

SELECT MyVar1,MyVar2  AS TEST FROM DUAL

The above code wil not work as an select and begin and end block can not be combined. Has Anyone sugestions for a working code?

score 3 · Answer 1 · answered Jan 14 '15 at 16:25

Given your code, I assume each query returns one and only one value.

If this is correct, to achieve the desired result (1 row, 2 columns) you might use a CROSS JOIN :

  SELECT V1.USERINCDE MyVar1, V2.USERINCDE MyVar2 FROM
    (SELECT USERINCDE FROM P_USR WHERE USEREXCDE='PH1') V1
  CROSS JOIN
    (SELECT USERINCDE FROM P_USR WHERE USEREXCDE='PH2') V2;

Or simply two sub-queries:

  SELECT (SELECT USERINCDE FROM P_USR WHERE USEREXCDE='PH1') MyVar1,
         (SELECT USERINCDE FROM P_USR WHERE USEREXCDE='PH2') MyVar2 FROM DUAL

score 0 · Accepted Answer · answered Jan 14 '15 at 19:40

Generally it's a strange wish. If you wish to return some values from anonymous block, you should do something like this:

BEGIN
  SELECT USERINCDE INTO :MyVar1 FROM P_USR WHERE USEREXCDE='PH1';
  SELECT USERINCDE INTO :MyVar2 FROM P_USR WHERE USEREXCDE='PH2';
END;

But if you very-very need to use cursor, you can do something like this:

DECLARE
 MyVar1 varchar2(100);
 MyVar2 varchar2(100);
BEGIN
  SELECT USERINCDE INTO MyVar1 FROM P_USR WHERE USEREXCDE='PH1';
  SELECT USERINCDE INTO MyVar2 FROM P_USR WHERE USEREXCDE='PH2';
  OPEN :result FOR SELECT MyVar1, MyVar2 AS TEST FROM DUAL;
END;

In last case result would be ref cursor.

Getting an cursor as result when using begin and end block

2 Answers2