Introduction
PL/SQL is good for many things. It has a structured, Ada-like syntax and can have SQL statements seamlessly embedded in it. Although not compiled right down to machine code, it’s even fairly quick.
But one thing that most people initially have difficulty doing is displaying information on the screen. The first thing I did was to enter what I wanted into a temporary table and use SQL*Plus to select from it. This works well for reports at the like, but it doesn’t work at all if you want to display something independently of a transaction or halfway through a function.
I was frustrated that there wasn’t a better way of feeding back information to end users. I was wrong to be frustrated: there is a better way.
Hello world!
The best example I can think of doing is the world famous “Hello, World!” program, so here goes:
CREATE OR REPLACE PROCEDURE hello_world AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Hello, world!');
END hello_world;
/
In SQL*Plus you can run that procedure by typing “exec hello_world” and pressing return.
You may be surprised to see that it says “PL/SQL procedure successfully completed.” and nothing else; there’s no “hello world” text. Surely it’s lying, it hasn’t successfully executed anything, has it?!
As with most things Oracle, things are not quite as simple as they first appear. In this case you need to tell SQL*Plus that you’re going to use the DBMS_OUTPUT package. To do that, you need the following command:
set serveroutput on
You might want to put this in your “login.sql” file so that it gets executed every time you log in. You can also add the “size” parameter to the end to indicate how much output you’ll be producing. The most you’re allowed is around 1Mb so I usually type “set serveroutput on size 1000000”.
If you try executing the procedure now, you’ll find that it works correctly.
A longer example
For our second and final example, we’ll use one of the tables that Oracle installs by default. It’s called EMP and should be in the SCOTT schema (password TIGER). The important fact here is that there are a number of different columns types, NUMBER’s, VARCHAR2’s and DATE’s.
On to the example:
CREATE OR REPLACE PROCEDURE example2 AS
CURSOR emp_cur IS
SELECT e.empno, e.ename, e.sal, e.hiredate
FROM emp e
WHERE e.sal > 2500;
BEGIN
FOR current_employee IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE ('-------------------'); -- string literal
DBMS_OUTPUT.PUT_LINE (current_employee.empno); -- number(4)
DBMS_OUTPUT.PUT_LINE (current_employee.ename); -- varchar2(10)
DBMS_OUTPUT.PUT_LINE (current_employee.sal); -- number(7,2)
DBMS_OUTPUT.PUT_LINE (current_employee.hiredate); -- date
DBMS_OUTPUT.PUT_LINE ('Year: ' ||
TO_CHAR(current_employee.hiredate, 'YYYY'));
END LOOP;
END;
/
This code should be pretty much self-explanatory. We first start a loop over a table. Inside the loop we output the content of it. The first PUT_LINE statement shows that you can output a string literal (we know this as ‘Hello world’ is one too). The second shows that you can output numbers without first converting them to a string — the function is overloaded and will accept most standard types.
Following that theme, the third, fourth and fifth lines output a string, a different number and a date, in all cases using the default format string. The last PUT_LINE statement shows that you can output expressions, but note that it all needs to be of the same type (so in this case I convert a date to a string so I can concatenate it to another string).
On my machine, the output is as follows:
SQL> set serveroutput on SQL> exec example2 ------------------- 7566 JONES 2975 02-APR-81 Year: 1981 ------------------- 7698 BLAKE 2850 01-MAY-81 Year: 1981 ------------------- 7788 SCOTT 3000 19-APR-87 Year: 1987 ------------------- 7839 KING 5000 17-NOV-81 Year: 1981 ------------------- 7902 FORD 3000 03-DEC-81 Year: 1981 PL/SQL procedure successfully completed. SQL>
Conclusion
As we’ve come to expect from Oracle over the years, simply outputting a little text to the screen isn’t quite as easy as you might wish for.
And it’s not without it’s problems. If you want to send more than a megabyte to the screen, you’re still going to have difficulty and all the playing around with “set serveroutput” is just plain tedious.
However, the important point is that you can do it and, programatically, it’s (basically) just a single command.
If you want more information on this kind of thing, have a look in chapter 6 of Oracle Builtin Packages, a whole book dedicated to this kind of thing.