PL/SQL Runs on the Server, not on the Client

I read in a book that PL/SQL runs on the server, not the client so I decided to check this out. I started a Command prompt on my Windows PC and used a SQL*Plus session to connect to a database on a UNIX server. Then I kicked off the loop below:

 

SQL> ed

Wrote file afiedt.buf

 

  1  declare

  2   a number;

  3  begin

  4   for b in 1..1000000000

  5    loop

  6    a := a + 1;

  7    end loop;

  8* end;

SQL> /

 

After it had been running for a while, I looked at the CPU usage on the Windows PC but it was hardly showing any at all (as usual, click on the image to enlarge it and bring it into focus):

The server, on the other hand, was showing over 95% of the CPU was being used for this one task so it seems that the book was right: 

Oracle 9: ps aux|more

USER            PID %CPU %MEM   VSZ  RSS TTY      S    STARTED         TIME COMMAND

oracle       503287 95.4  0.5  299M 4.7M ??       R    18:34:17     4:52.64 oracleLIVDPT1 (LOCAL=NO)

%NOTFOUND

This simple example was tested on Oracle 11.2. It uses SQL*Plus to list the tables owned by a user and counts the rows in each one:

SQL> select table_name

  2  from dba_tables

  3  where owner = 'ANDREW'

  4  /

 

TABLE_NAME

------------------------------

T1

T2

T3

 

SQL> select count(*) from andrew.t1

  2  /

 

  COUNT(*)

----------

      3082

 

SQL> select count(*) from andrew.t2

  2  /

 

  COUNT(*)

----------

      4851

 

SQL> select count(*) from andrew.t3

  2  /

 

  COUNT(*)

----------

     73942

 

SQL>

Then it does the same thing in PL/SQL. Note the use of %NOTFOUND to jump out of the loop when there is nothing more to return from the cursor:

SQL> DECLARE

  2   CURSOR c1 is

  3    SELECT owner||'.'||table_name fqn

  4    FROM dba_tables

  5    WHERE owner = 'ANDREW';

  6   c1_rec c1%ROWTYPE;

  7   row_count NUMBER;

  8   sql_statement VARCHAR2(200);

  9  BEGIN

10   OPEN c1;

11    LOOP

12     FETCH c1 INTO c1_rec;

13     EXIT WHEN c1%NOTFOUND;

14     sql_statement :=

15      'SELECT COUNT(*) FROM '||c1_rec.fqn;

16     DBMS_OUTPUT.PUT_LINE(sql_statement);

17     EXECUTE IMMEDIATE sql_statement

18      INTO row_count;

19     DBMS_OUTPUT.PUT_LINE(c1_rec.fqn||

20      ' has '||row_count||' rows');

21    END LOOP;

22   CLOSE c1;

23  END;

24  /

SELECT COUNT(*) FROM ANDREW.T1

ANDREW.T1 has 3082 rows

SELECT COUNT(*) FROM ANDREW.T2

ANDREW.T2 has 4851 rows

SELECT COUNT(*) FROM ANDREW.T3

ANDREW.T3 has 73942 rows

 

PL/SQL procedure successfully completed.

 

SQL>

PL/SQL Loop Labels

This was tested on Oracle 11.2. PL/SQL loops can have labels, which must appear just before the LOOP statement. They must be undeclared identifiers enclosed by double angle brackets. If you wish, you can repeat the label at the end of the loop:

SQL> DECLARE

  2   X NUMBER;

  3   Y NUMBER;

  4  BEGIN

  5   X := 1;

  6   <<OUTER_LOOP>>

  7   LOOP

  8    Y := 1;

  9    <<INNER_LOOP>>

 10    LOOP

 11     DBMS_OUTPUT.PUT_LINE('X = '||X||' and Y = '||Y);

 12     Y := Y + 1;

 13     EXIT WHEN Y > 2;

 14    END LOOP INNER_LOOP;

 15    X := X + 1;

 16    EXIT WHEN X > 2;

 17   END LOOP OUTER_LOOP;

 18  END;

 19  /

X = 1 and Y = 1

X = 1 and Y = 2

X = 2 and Y = 1

X = 2 and Y = 2

PL/SQL procedure successfully completed.

SQL> DECLARE

  2   X NUMBER;

  3   Y NUMBER;

  4  BEGIN

  5   X := 1;

  6   <<OUTER_LOOP>>

  7   LOOP

  8    Y := 1;

  9    LOOP

 10     DBMS_OUTPUT.PUT_LINE('X = '||X||' and Y = '||Y);

 11     Y := Y + 1;

 12     EXIT WHEN Y > 2;

 13     EXIT OUTER_LOOP WHEN X + Y > 6;

 14    END LOOP;

 15   X := X + 1;

 16   END LOOP;

 17  END;

 18  /

X = 1 and Y = 1

X = 1 and Y = 2

X = 2 and Y = 1

X = 2 and Y = 2

X = 3 and Y = 1

X = 3 and Y = 2

X = 4 and Y = 1

X = 4 and Y = 2

X = 5 and Y = 1

PL/SQL procedure successfully completed.

SQL>

SERVEROUTPUT

Set serveroutput off/on can be used in SQL*Plus to control the output from the PL/SQL dbms_output.put_line statement. When you first login the default is for serveroutput to be off:

SQL> show serveroutput

serveroutput OFF

SQL>

If serveroutput is set to off, you will not get any output from dbms_output.put_line:

SQL> begin

  2  dbms_output.put_line('12345678901234567890');

  3  end;

  4  /

 

PL/SQL procedure successfully completed.

 

SQL>

Setting it to on changes this:

SQL> set serveroutput on

SQL> show serveroutput

serveroutput ON size 2000 format WORD_WRAPPED

SQL> begin

  2  dbms_output.put_line('12345678901234567890');

  3  end;

  4  /

12345678901234567890

 

PL/SQL procedure successfully completed.

 

SQL>

There is also a size associated with serveroutput. This specifies the volume of output allowed. In Oracle 9 this can have any value between 2000 and 1000000:

SQL> set serveroutput on size 1999

SP2-0547: size option 1999 out of range (2000 through 1000000)

SQL> set serveroutput on size 1000001

SP2-0547: size option 1000001 out of range (2000 through 1000000)

SQL>

I will set it to 2000 for the example below although, as you can see above, this appears to be the default. When you reach the limit set by the size parameter, Oracle displays an error and stops:

SQL> set serveroutput on size 2000

SQL> declare

  2  a number;

  3  begin

  4  a := 1;

  5  while a < 100

  6  loop

  7  dbms_output.put_line(a||' Andrew Reid, International DBA');

  8  a := a + 1;

  9  end loop;

10  end;

11  /

1 Andrew Reid, International DBA

2 Andrew Reid, International DBA

3 Andrew Reid, International DBA

4 Andrew Reid, International DBA

5 Andrew Reid, International DBA

6 Andrew Reid, International DBA

7 Andrew Reid, International DBA

8 Andrew Reid, International DBA

9 Andrew Reid, International DBA

10 Andrew Reid, International DBA

11 Andrew Reid, International DBA

12 Andrew Reid, International DBA

13 Andrew Reid, International DBA

14 Andrew Reid, International DBA

15 Andrew Reid, International DBA

16 Andrew Reid, International DBA

17 Andrew Reid, International DBA

18 Andrew Reid, International DBA

19 Andrew Reid, International DBA

20 Andrew Reid, International DBA

21 Andrew Reid, International DBA

22 Andrew Reid, International DBA

23 Andrew Reid, International DBA

24 Andrew Reid, International DBA

25 Andrew Reid, International DBA

26 Andrew Reid, International DBA

27 Andrew Reid, International DBA

28 Andrew Reid, International DBA

29 Andrew Reid, International DBA

30 Andrew Reid, International DBA

31 Andrew Reid, International DBA

32 Andrew Reid, International DBA

33 Andrew Reid, International DBA

34 Andrew Reid, International DBA

35 Andrew Reid, International DBA

36 Andrew Reid, International DBA

37 Andrew Reid, International DBA

38 Andrew Reid, International DBA

39 Andrew Reid, International DBA

40 Andrew Reid, International DBA

41 Andrew Reid, International DBA

42 Andrew Reid, International DBA

43 Andrew Reid, International DBA

44 Andrew Reid, International DBA

45 Andrew Reid, International DBA

46 Andrew Reid, International DBA

47 Andrew Reid, International DBA

48 Andrew Reid, International DBA

49 Andrew Reid, International DBA

50 Andrew Reid, International DBA

51 Andrew Reid, International DBA

52 Andrew Reid, International DBA

53 Andrew Reid, International DBA

54 Andrew Reid, International DBA

declare

*

ERROR at line 1:

ORA-20000: ORU-10027: buffer overflow, limit of 2000

bytes

ORA-06512: at "SYS.DBMS_OUTPUT", line 35

ORA-06512: at "SYS.DBMS_OUTPUT", line 198

ORA-06512: at "SYS.DBMS_OUTPUT", line 139

ORA-06512: at line 7

 

SQL>