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!

score 232 · Accepted Answer · answered May 03 '12 at 15:30

232

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;

answered May 03 '12 at 15:30

Justin Cave

227,342
24
367
384

Sorry, when executing the procedure above the message is PL/SQL procedure successfully completed. I am using SQL plus – dexter May 03 '12 at 15:32
10

@dexter - OK. Then you just need to add the `set serveroutput on` command before executing the procedure in SQL*Plus. – Justin Cave May 03 '12 at 15:34
@dexter - Right. See my comment at the end-- if you don't want that literal for every row, you'll probably want the syntax I posted at the end of my answer. – Justin Cave May 03 '12 at 15:38
Ok so now it displays the correct names but like 100times. how would i display title, year, rolename, quote in a table below instead of the 100s of name that appears – dexter May 03 '12 at 15:43
@dexter - If you are stating that each name is repeated hundreds of times, and that is not what you expect, that implies that your `SELECT` statement is incorrect and is probably missing a join condition. You'll want to refine your query (which you can run separately without the procedure) until the query returns the data you expect. – Justin Cave May 03 '12 at 15:50
thanks. How would i display the other values?thanks for your help – dexter May 03 '12 at 16:16
@dexter - You'd just concatenate more data to the string you build and send to dbms_output. For example `dbms_output.put_line( row.firstName || ' ' || row.lastName || ' ' || row.title || ' ' || row.year )`. Of course, in reality, you wouldn't rely on `dbms_output` being enabled-- you'd use `dbms_output` for debugging whatever your procedure is really supposed to do. – Justin Cave May 03 '12 at 16:19
Yes I would rather the table be outputted from a select statement in the loop. is that possible? As i want the headings of each column to show also. Thanks again – dexter May 03 '12 at 16:23
@dexter - You could return a `SYS_REFCURSOR` and the caller could then read from the ref cursor to display the data. Or you could create a view rather than a stored procedure and just query the view. Or you could just execute the SQL statement. – Justin Cave May 03 '12 at 16:28
I have executed the SQL statement but the table doesnt appear. – dexter May 03 '12 at 16:32
1

@dexter - I'm sorry, I don't understand. Are you saying that you typed the `SELECT` statement at the SQL*Plus command prompt, the SQL statement executed, data was returned, but the data was not displayed in SQL*Plus? That seems unlikely unless you were playing with `autotrace` settings in which case you might have seen a query plan and execution statistics rather than the query results. We're starting to get rather far from your original question, however. – Justin Cave May 03 '12 at 16:35
1

Maybe interesting for someone: I still didn't get the output with SQL Developer using this manual. After some time I found the reason. https://community.oracle.com/thread/4062562 There is a bug if you use an old database (Oracle 10g) with the current version of the SQL Developer. – David Gausmann Feb 02 '18 at 13:39

score 39 · Answer 2 · edited May 23 '16 at 08:28

39

Ensure that you have your Dbms Output window open through the view option in the menubar.
Click on the green '+' sign and add your database name.
Write 'DBMS_OUTPUT.ENABLE;' within your procedure as the first line. Hope this solves your problem.

edited May 23 '16 at 08:28

Usman Maqbool

3,351
10
31
48

answered May 23 '16 at 07:29

Atul Patel

543
4
11

Which application are these instructions intended for? – osullic Sep 11 '20 at 15:59
@osullic A bit late but its for sqldeveloper. lol : ) – rishi May 17 '23 at 18:18

score 36 · Answer 3 · answered Sep 03 '18 at 11:40

36

Set Query as below at first line

SET SERVEROUTPUT ON

answered Sep 03 '18 at 11:40

Sreenath S

669
6
9

even its not working .. Please provide any other alternative – Lova Chittumuri Aug 10 '19 at 18:12
1

Just to be extra clear. The line Sreenath S has suggested goes first and is outside all code blocks like DECLARE and BEGIN/END. I tried making it first in my DECLARE block which does not work. I am using SQL*Plus. – Grant Johnson Aug 26 '20 at 14:32

Jeevi · Answer 4 · 2016-07-13T13:01:11.110

19

this statement

DBMS_OUTPUT.PUT_LINE('a.firstName' || 'a.lastName');

means to print the string as it is.. remove the quotes to get the values to be printed.So the correct syntax is

DBMS_OUTPUT.PUT_LINE(a.firstName || a.lastName);

edited Jul 13 '16 at 13:01

answered May 09 '12 at 05:59

Jeevi

2,962
6
39
60

score 16 · Answer 5 · edited Jan 03 '22 at 10:21

16

In Oracle SQL Developer, you can follow steps by steps as the below image:

edited Jan 03 '22 at 10:21

Suraj Rao

29,388
11
94
103

answered Jan 03 '22 at 10:19

SoftwareEngineer

197
1
3

score 12 · Answer 6 · edited Dec 31 '20 at 10:18

12

For SQL Developer

You have to execute it manually

SET SERVEROUTPUT ON

After that if you execute any procedure with DBMS_OUTPUT.PUT_LINE('info'); or directly .

This will print the line

And please don't try to add this

 SET SERVEROUTPUT ON

inside the definition of function and procedure, it will not compile and will not work.

edited Dec 31 '20 at 10:18

Dharman

30,962
25
85
135

answered Dec 31 '20 at 10:12

CuriousDev

400
3
11

score 3 · Answer 7 · answered Jan 25 '19 at 05:21

I am using Oracle SQL Developer,

In this tool, I had to enable DBMS output to view the results printed by dbms_output.put_line

You can find this option in the result pane where other query results are displayed. so, in the result pane, I have 7 tabs. 1st tab named as Results, next one is Script Output and so on. Out of this you can find a tab named as "DBMS Output" select this tab, then the 1st icon (looks like a dialogue icon) is Enable DBMS Output. Click this icon. Then you execute the PL/SQL, then select "DBMS Output tab, you should be able to see the results there.

score 0 · Answer 8 · answered Sep 02 '14 at 07:25

All of them are concentrating on the for loop but if we use a normal loop then we had to use of the cursor record variable. The following is the modified code

 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;
    recd quote_recs%rowtype;
    BEGIN
    open quote_recs;
    LOOP
    fetch quote_recs into recs;
    exit when quote_recs%notfound;
    DBMS_OUTPUT.PUT_LINE(recd.firstName||recd.lastName);
    end loop;
    close quote_recs;
    END PRINT_ACTOR_QUOTES;
    /

DBMS_OUTPUT.PUT_LINE not printing

8 Answers8

Linked

Related