Print text in Oracle SQL Developer SQL Worksheet window

Question

I am using Oracle SQL (in SQLDeveloper, using the SQL Worksheet). I would like to print a statement before my select, such as

PRINT 'Querying Table1';
SELECT * from Table1;

What do I use to Print / show text output? It's not Print, because that gives me the error: Bind Variable Table1 is NOT DECLARED. DBMS_OUTPUT.PUT_LINE is an unknown command. (Obviously, I'm an inexperienced SQLDeveloper and Oracle user. There must be some synonym for Print, but I'm having trouble finding help on it without knowing what it is.)

Community · Accepted Answer · 2012-10-26 08:54:51Z

165

enter image description here

for simple comments:

set serveroutput on format wrapped;
begin
    DBMS_OUTPUT.put_line('simple comment');
end;
/

-- do something

begin
    DBMS_OUTPUT.put_line('second simple comment');
end;
/

you should get:

anonymous block completed
simple comment

anonymous block completed
second simple comment

if you want to print out the results of variables, here's another example:

set serveroutput on format wrapped;
declare
a_comment VARCHAR2(200) :='first comment';
begin
    DBMS_OUTPUT.put_line(a_comment);
end;

/

-- do something


declare
a_comment VARCHAR2(200) :='comment';
begin
    DBMS_OUTPUT.put_line(a_comment || 2);
end;

your output should be:

anonymous block completed
first comment

anonymous block completed
comment2

edited Oct 26, 2012 at 8:54

CommunityBot

11 silver badge

answered Dec 11, 2008 at 16:45

Perry Tribolet

2,7441 gold badge21 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

geographika Over a year ago

First line set serveroutput on format wraped; should be set serveroutput on format WRAPPED;

dwjohnston Over a year ago

I can't get this to work. set serveroutput on format word_wrapped; begin dbms_output.put_line('hello world'); end; select * from dual Gives me:

select * from dual; Error report: ORA-06550: line 7, column 1: PLS-00103: Encountered the symbol "SELECT" 06550. 00000 - "line %s, column %s:\n%s" *Cause: Usually a PL/SQL compilation error. *Action

Bruno Ranschaert Over a year ago

In newer versions of SQL Developer you first have to open the DBMS Output window using the topleven menu bar: View > Dbms Output.

David Mann Over a year ago

could've but the screen shot and no text, would have been a +1 haha

H77 · Accepted Answer · 2017-10-24 01:57:10Z

56

PROMPT text to print

Note: must use Run as Script (F5) not Run Statement (Ctl + Enter)

edited Oct 24, 2017 at 1:57

answered Nov 3, 2010 at 7:09

H77

5,9772 gold badges29 silver badges40 bronze badges

1 Comment

Pascal R. Over a year ago

Works for me with Run Statement (Ctl + Enter) too.

ΩmegaMan · Accepted Answer · 2019-02-15 17:40:34Z

26

The main answer left out a step for new installs where one has to open up the dbms output window.

Then the script I used:

dbms_output.put_line('Start');

Another script:

set serveroutput on format wrapped;
begin
    DBMS_OUTPUT.put_line('jabberwocky');
end;

answered Feb 15, 2019 at 17:40

ΩmegaMan

32.2k13 gold badges110 silver badges138 bronze badges

Comments

Eddie Awad · Accepted Answer · 2008-10-10 21:50:10Z

23

You could set echo to on:

set echo on
REM Querying table
select * from dual;

In SQLDeveloper, hit F5 to run as a script.

answered Oct 10, 2008 at 21:50

Eddie Awad

3,7651 gold badge22 silver badges18 bronze badges

1 Comment

thursdaysgeek Over a year ago

I had accepted this as an answer, but I see another answer has a lot more points, so I changed my accept to that. However, this did work for me, and it's a good answer.

Leigh Riffel · Accepted Answer · 2008-10-12 02:25:30Z

16

You could put your text in a select statement such as...

SELECT 'Querying Table1' FROM dual;

answered Oct 12, 2008 at 2:25

Leigh Riffel

6,6813 gold badges38 silver badges48 bronze badges

Comments

Michael Erickson · Accepted Answer · 2011-03-02 16:19:11Z

8

For me, I could only get it to work with

set serveroutput on format word_wrapped;

The wraped and WRAPPED just threw errors: SQLPLUS command failed - not enough arguments

answered Mar 2, 2011 at 16:19

Michael Erickson

4,1112 gold badges22 silver badges17 bronze badges

Comments

FrenkyB · Accepted Answer · 2020-05-20 04:56:27Z

4

If I ommit begin - end it is error. So for me this is working (nothing else needed):

set serveroutput on;
begin
DBMS_OUTPUT.PUT_LINE('testing');
end;

answered May 20, 2020 at 4:56

FrenkyB

7,30719 gold badges75 silver badges125 bronze badges

Comments

Community · Accepted Answer · 2020-06-20 09:12:55Z

1

If you don't want all of your SQL statements to be echoed, but you only want to see the easily identifiable results of your script, do it this way:

set echo on

REM MyFirstTable

set echo off

delete from MyFirstTable;

set echo on

REM MySecondTable

set echo off

delete from MySecondTable;

The output from the above example will look something like this:

-REM MyFirstTable

13 rows deleted.

-REM MySecondTable

27 rows deleted.

edited Jun 20, 2020 at 9:12

CommunityBot

11 silver badge

answered Dec 8, 2014 at 23:24

Frank Staheli

292 bronze badges

Collectives™ on Stack Overflow

Print text in Oracle SQL Developer SQL Worksheet window

8 Answers 8

4 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

8 Answers 8

4 Comments

1 Comment

Comments

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related