DBMS_OUTPUT.PUT_LINE not printing

Question

When executing the following code, it just says the procedure is completed and doesn't print the infomation i want it to (firstName, lastName) and then the other values from the select query in a table below.

 CREATE OR REPLACE PROCEDURE PRINT_ACTOR_QUOTES (id_actor char) AS CURSOR quote_recs IS SELECT a.firstName,a.lastName, m.title, m.year, r.roleName ,q.quotechar from quote q, role r,    rolequote rq, actor a, movie m where rq.quoteID = q.quoteID AND rq.roleID = r.roleID  AND r.actorID = a.actorID AND r.movieID = m.movieID AND  a.actorID = id_actor; BEGIN FOR row IN quote_recs LOOP DBMS_OUTPUT.PUT_LINE('a.firstName' || 'a.lastName');  end loop; END PRINT_ACTOR_QUOTES; /  

When setting server output on, I get

a.firstNamea.lastName a.firstNamea.lastName a.firstNamea.lastName a.firstNamea.lastName 

multiple times!

Answer 1

What is "it" in the statement "it just says the procedure is completed"?

By default, most tools do not configure a buffer for dbms_output to write to and do not attempt to read from that buffer after code executes. Most tools, on the other hand, have the ability to do so. In SQL*Plus, you'd need to use the command set serveroutput on [size N|unlimited]. So you'd do something like

SQL> set serveroutput on size 30000; SQL> exec print_actor_quotes( <<some value>> ); 

In SQL Developer, you'd go to View | DBMS Output to enable the DBMS Output window, then push the green plus icon to enable DBMS Output for a particular session.

Additionally, assuming that you don't want to print the literal "a.firstNamea.lastName" for every row, you probably want

FOR row IN quote_recs LOOP   DBMS_OUTPUT.PUT_LINE( row.firstName || ' ' || row.lastName ); END LOOP;