Is it possible to output a SELECT statement from a PL/SQL block?

Upgraded SELECT output with Oracle 12.1+

If you're lucky enough to be vibing with Oracle 12c (12.1+), you've got a trick up your sleeve: DBMS_SQL.RETURN_RESULT. No need to fuss with DBMS_OUTPUT.

DECLARE
  c SYS_REFCURSOR;
BEGIN
  OPEN c FOR SELECT last_name, job_id FROM employees; --Including job_id for more flair
  DBMS_SQL.RETURN_RESULT(c); --Now you can spin the records directly
END;


SET AUTOPRINT ON command is your special pass to the backstage where SQL*Plus and Oracle SQL Developer users will find their REF CURSORs automatically printed. INSANE, right?

Getting technical with SQL code

Subquery for fancy footwork

Your SQL doing the "SQL Shuffle"? With subquery factoring (or even inline views), you can untangle your SQL’s awkward dance moves.

Solo performance (single-row)

If your SQL has a one-man band, opt for an implicit cursor. It avoids extraneous cursor management.

DECLARE
  v_name employees.last_name%TYPE; --Our solo artist
BEGIN
  SELECT last_name INTO v_name FROM employees WHERE employee_id = 100; --Let's keep it exclusive
  DBMS_OUTPUT.PUT_LINE('Employee name: ' || v_name); --Time for the solo!
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    DBMS_OUTPUT.PUT_LINE('Concert cancelled. No such employee. : (');
  WHEN TOO_MANY_ROWS THEN
    DBMS_OUTPUT.PUT_LINE('Festival alert! More than one performer. Sorry!');
END;


The whole band (multiple rows)

For multiple rows, you've got a whole band. An explicit cursor with a FOR loop is the ultimate band manager.

FOR record IN (SELECT last_name, department_id FROM employees) LOOP
  DBMS_OUTPUT.PUT_LINE(record.last_name || ', Lead singer in department ' || record.department_id);
END LOOP;


Text format results

To have your results sing in a text format, just riff through a FOR loop with DBMS_OUTPUT.PUT_LINE.

Advanced SQL Symphony Techniques

Harmonizing with pipelined functions

Performing for a large concert? Perhaps you've got a lot of complex data types. Let pipelined functions harmonize that chaos.

CREATE TYPE employee_name_list AS TABLE OF VARCHAR2(50); --Our concert playlist

-- Pipelined function
CREATE FUNCTION get_employee_names RETURN employee_name_list PIPELINED IS
BEGIN
  FOR record IN (SELECT last_name FROM employees) LOOP --Show must go on
    PIPE ROW(record.last_name); --Each name is a song in our concert
  END LOOP;
  RETURN; --Take a bow
END;


In a SELECT statement's FROM clause, use the function ASEASYAS:

SELECT column_value AS employee_name FROM TABLE(get_employee_names); --The concert review


Modular Code is like a Music Festival

Like a well-organized music festival, modular code keeps everything in its right place. Named procedures and pipelined functions are the perfect team.

Configurations and Errors

Light up the stage with server output

Light up the stage by enabling server output so your DBMS_OUTPUT can shine.

EXECUTE DBMS_OUTPUT.ENABLE(NULL); --Lights ON!
SET SERVEROUTPUT ON; --Is this mic ON?


Error Handling

Good bands need good management. Exception handling in your PL/SQL blocks ensures a smooth show must go on experience.