Can you exit a PL-SQL loop from within an execute immediate statement?

Question

I have the following snippet (simplified to exclude extraneous details):

<<cursor_loop>> 
LOOP 
  fetch c1 into somerecord;
  EXECUTE IMMEDIATE 'begin EXIT cursor_loop WHEN 1 = 1; end;';
END LOOP cursor_loop;

When I run this, it fails with a PLS-00201: identifier 'CURSOR_LOOP' must be declared error.

If I leave the loop label out of the execute immediate, I get PLS-00376: illegal EXIT/CONTINUE statement; it must appear inside a loop.

Obviously the latter is wrong, but it's not clear why the former is.

Can this loop be exited from within the statement inside the execute-immediate?

A hack: raise an exception in the `execute immediate` statement, that will exit the loop. — Shannon Severance, Jul 30 '15 at 17:13

score 4 · Accepted Answer · answered Jul 30 '15 at 18:32

You can't reference the cursor_loop directly in your dynamic SQL because of the scope, as other have already said. If you are stuck with this pattern then you could use a bind variable flag to pass state information from the dynamic code back to the static code; something like:

DECLARE
  break_loop pls_integer;
...
  break_loop := 0;
  <<cursor_loop>> 
  LOOP 
    fetch c1 into somerecord;
    EXECUTE IMMEDIATE 'begin if 1 = 1 then :break_loop := 1; end if; end;'
      USING OUT break_loop;
    EXIT cursor_loop WHEN break_loop = 1;
  END LOOP cursor_loop;
...

Slightly more complete, though obviously still horribly contrived, example:

DECLARE
  break_loop pls_integer;
  somevar number;
  c1 sys_refcursor;
BEGIN
  OPEN c1 FOR
    select 1 from dual
    union all select 2 from dual
    union all select 3 from dual;

  break_loop := 0;
  dbms_output.put_line('before loop, break_loop is ' || break_loop);
  <<cursor_loop>> 
  LOOP 
    fetch c1 into somevar;
    exit when c1%notfound;
    dbms_output.put_line('got ' || somevar);
    EXECUTE IMMEDIATE 'begin if :somevar = 2 then :break_loop := 1; end if; end;'
      USING somevar, OUT break_loop;
    EXIT cursor_loop WHEN break_loop = 1;
  END LOOP cursor_loop;
  dbms_output.put_line('after loop, break_loop is ' || break_loop);
END;
/

PL/SQL procedure successfully completed.

before loop, break_loop is 0
got 1
got 2
after loop, break_loop is 1

The loop is exited because of the dynamic check, before the value '3' is fetched.

Note that there's no need to name the loop with this method, though I'm wondering whether I should start naming loops as standard practice now that I'm aware that it can be done. — John O, Jul 30 '15 at 18:38
@JohnO - I wasn't sure if you had nested loops and wanted to jump out of more than one level, so I left that in. If you only have a single loop in the block then you can initialise the flag in the declare section too, but if you (may) have more than one then making sure it's set correctly before the first one doesn't hurt. — Alex Poole, Jul 30 '15 at 18:41
You could also throw an exception as ShannonSeverance suggested, but then you need another block and exception handler somewhere to stop it exiting the whole thing. — Alex Poole, Jul 30 '15 at 18:43

score 3 · Answer 2 · answered Jul 30 '15 at 17:13

3

No, you can't.

The dynamic SQL statement runs in a separate scope-- it cannot refer to variables defined in the calling block or manipulate their values (unless, of course, your dynamic statement has bind variables that allow you to explicitly create an interface between the two with the USING and INTO clauses of EXECUTE IMMEDIATE to pass in and return values). Similarly, it cannot reference a loop name since that name is not in scope when the dynamic statement is executed.

In this case, it's unclear why you'd want to use EXECUTE IMMEDIATE in the first place rather than coding the EXIT as just a normal part of your loop.

answered Jul 30 '15 at 17:13

Justin Cave

227,342
24
367
384

I have dozens of cursors defined in third party mode, and I need to be able to reference them by name. I've been experimenting with opening/closing them with execute immediate. While there are other (better) solutions, those would all involve changing those defined cursors to strings which could then be used in a dynamic cursor, but I can't do that. Nor do I want to define them twice, once as a cursor and again as a string. I'm able to open, fetch, and close the cursor with execute immediate... but can't set up the exit when notfound. – John O Jul 30 '15 at 18:17
@JohnO - not sure I quite follow your process or restrictions; but why can't you set a flag within your dynamic statement (via a bind variable) that the static code then uses to decide whether it should exit the loop? – Alex Poole Jul 30 '15 at 18:22
@Alex Poole Tunnel vision. It never occurred to me. Trying that now... if you want to write that as an answer, looks like it's working, I'll accept it. – John O Jul 30 '15 at 18:34

score 2 · Answer 3 · answered Jul 30 '15 at 17:10

2

I think not. EXECUTE IMMEDIATE is for running a SQL statement, not an anymous block. From the docs:

The EXECUTE IMMEDIATE statement builds and executes a dynamic SQL
statement in a single operation. It is the means by which native dynamic SQL processes most dynamic SQL statements.

https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/executeimmediate_statement.htm

answered Jul 30 '15 at 17:10

Gary_W

9,933
1
22
40

3

Although you can indeed execute an anonymous block dynamically, the problem here is that the dynamic statement in `EXECUTE IMMEDIATE` creates its own execution context, which knows nothing about the caller context, including variables or blocks in it. – mustaccio Jul 30 '15 at 17:11

Can you exit a PL-SQL loop from within an execute immediate statement?

3 Answers3