CODEWITHSUNDEEP
sqloracle-databasestored-procedures
andandandand
andandandand

Reputation: 22270

Why do I see no output from this PL/SQL block?

I'd like a code sample.

I'm tryng this:

DECLARE
     var NUMBER;
 BEGIN
     /*N.B. for loop variables in pl/sql are new declarations, with scope only inside the loop */ 
     FOR var IN 0 .. 10 LOOP 
          DBMS_OUTPUT.put_line(var);
     END LOOP;

     IF (var IS NULL) THEN
          DBMS_OUTPUT.put_line('var is null');
     ELSE
          DBMS_OUTPUT.put_line('var is not null');
     END IF;
 END;

and getting no output (though I know it's not a infinite loop). Why is this one not printing?

edit: The not-printing code was fixed via the database manager interface.

Upvotes: 4

Views: 15324

Answers (4)

Gary Myers
Gary Myers

Reputation: 35401

A loop containing a DBMS_OUTPUT.PUT_LINE will not be infinite (if serveroutput is enabled) as, eventually, it will fill the entire output buffer or the available memory. The limit used to be about 1 million bytes so would get hit quite quickly. If it goes to fill up the entire computer memory, that can take quite some time.

On infinite loops, I went through a bad patch of forgetting to go to the next element in a table.

DECLARE
  type typ_tab is table of varchar2(10) index by pls_integer;
  t_tab typ_tab;
  v_ind number;
BEGIN
  t_tab(10) := 'A';
  t_tab(20) := 'B';
  v_ind := t_tab.first;
  WHILE v_ind IS NOT NULL LOOP
    dbms_output.put_line(t_tab(v_ind));
    v_ind := t_tab.next(v_ind); --Forget this and it loops forever
  END LOOP;
END;

Once they get into such a loop, the session may need to be killed by the DBA.

Upvotes: 3

Jay S
Jay S

Reputation: 7994

If your problem is that you are getting no output, then you may not have enabled DBMS OUTPUT yet. You can do that with:

set serveroutput on

Upvotes: 5

Justin Cave
Justin Cave

Reputation: 231851

A LOOP without an EXIT statement is one way to generate an infinite loop in PL/SQL

BEGIN
  LOOP
    null;
  END LOOP;
END;

You could also write a WHILE loop that never ends

BEGIN
  WHILE( true )
  LOOP
    NULL;
  END LOOP;
END;

Upvotes: 12

Quassnoi
Quassnoi

Reputation: 425813

Don't know why would you need it, but:

BEGIN
        WHILE  1 = 1
        LOOP
                NULL;
        END LOOP;
END;

Upvotes: 2

Related Questions