0

I have two select, how can I do the same using a loop and a variable in PL SQL Developer. I have:

select 1 as nm from dual;
select 2 as nm from dual;

I need something similar:

DECLARE x NUMBER;
BEGIN
 FOR X IN 1..2
  LOOP
   EXECUTE IMMEDIATE ('SELECT ' || X || ' AS NM FROM DUAL;');
  END LOOP;
END;

In PL SQL Developer, each select is displayed in a separate tab:

enter image description here

3
  • Please clarify your specific problem or provide additional details to highlight exactly what you need. As it's currently written, it's hard to tell exactly what you're asking.
    – Community Bot
    Sep 8 at 13:35
  • How to make the loop work so that the result is similar to the two select from the first example. The result in PL SQL Developer should be the same as in the screenshot
    – Depeche
    Sep 9 at 5:05
  • What output are you expecting? And what kind of output(dbms_output debug message, refcursor, PL/SQL Developer results grid that you can export to Excel etc)? Sep 10 at 14:06
0

If you need to join the result of two queries you can use UNION ALL clause:

select 1 as nm from dual
union all
select 2 as nm from dual;

It would be additional DISTINCT step using UNION without ALL. We don't need this in this case.

If you need to generate record set with subsequential records, good solution is to "misuse" CONNECT BY clause:

select level nm from dual connect by level <= 2; -- Instead of 2 may be any number

But this structure is not efficient in large scale. Let say you have to generate 10 millions records. In this case the best solution is to use CARTESIAN JOIN on smaller subqueries:

select rownum rn
from
(select level rn from dual connect by level <= 1000) t1,
(select level rn from dual connect by level <= 1000) t2,
(select level rn from dual connect by level <= 10) t3;

Total rowcount will be multiplication the rowcounts of all subqeries: 1,000 * 1,000 * 10 = 10,000,000.

2
  • I apologize for the incomprehensible description. I need to use a loop to get the same results as the sequential execution of two "Select .. from". My code with a loop doesn't work.
    – Depeche
    Sep 9 at 7:15
  • It's still hard to me to understand your requirements. Maybe table functions are the solution to your problem?. Please try to explain the problem otherways. Sep 9 at 10:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.