Printing the value of a variable in SQL Developer

Question

I wanted to print the value of a particular variable which is inside an anonymous block. I am using Oracle SQL Developer. I tried using dbms_output.put_line. But it is not working. The code which I am using is shown below.

SET SERVEROUTPUT ON

DECLARE

  CTABLE USER_OBJECTS.OBJECT_NAME%TYPE;
  CCOLUMN ALL_TAB_COLS.COLUMN_NAME%TYPE;
  V_ALL_COLS VARCHAR2(500);

  CURSOR CURSOR_TABLE
    IS
    SELECT OBJECT_NAME 
    FROM USER_OBJECTS 
    WHERE OBJECT_TYPE='TABLE'
    AND OBJECT_NAME LIKE 'tb_prm_%';

  CURSOR CURSOR_COLUMNS (V_TABLE_NAME IN VARCHAR2)
    IS
    SELECT COLUMN_NAME
    FROM ALL_TAB_COLS
    WHERE TABLE_NAME = V_TABLE_NAME;

BEGIN

  OPEN CURSOR_TABLE;

  LOOP
    FETCH CURSOR_TABLE INTO CTABLE;
    EXIT WHEN CURSOR_TABLE%NOTFOUND;

    OPEN CURSOR_COLUMNS (CTABLE);

    V_ALL_COLS := NULL;

    LOOP
      FETCH CURSOR_COLUMNS INTO CCOLUMN;
      V_ALL_COLS := V_ALL_COLS || CCOLUMN;
      IF CURSOR_COLUMNS%FOUND THEN
        V_ALL_COLS := V_ALL_COLS || ', ';
      ELSE
        EXIT;
      END IF;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE(V_ALL_COLS);

  END LOOP;
  CLOSE CURSOR_TABLE;

END;

And I am getting the output only as anonymous block completed.

output is flushed at end/termination of program, thats the way it works. You can't flush to display while running. Look into log tables (autonomous transaction) to see whats happening while a program is running — tbone, Oct 25 '11 at 10:51
Why not use the built in debugger and step through your block. It shows the state of all variables. — WoMo, Oct 25 '11 at 15:09
SET SERVEROUTPUT ON; --add semi colon, select ALL and run in developer, for me its working fine, — Praveenkumar_V, Jun 19 '14 at 07:29

score 224 · Accepted Answer · answered Oct 25 '11 at 12:38

224

You need to turn on dbms_output. In Oracle SQL Developer:

Show the DBMS Output window (View->DBMS Output).
Press the "+" button at the top of the Dbms Output window and then select an open database connection in the dialog that opens.

In SQL*Plus:

 SET SERVEROUTPUT ON

answered Oct 25 '11 at 12:38

Klas Lindbäck

33,105
5
57
82

In version 1.5.5, I don't seem to have that option under View. There's Connections, Files, Reports... Status Bar, Toolbars, Refresh, but no DBMS Output. Lots of stuff under preferences, but I still don't see an option for output. – ruffin Sep 11 '12 at 22:15
1

@ruffin: v1.5.5 is 3 years old. I'm using v3.0, and the current version seems to be v3.1. – Klas Lindbäck Sep 12 '12 at 09:37
/nods We're stuck on 1.5.5 -- if you had the answer handy, I'd take it, but nps otherwise. Thanks! – ruffin Sep 12 '12 at 15:52
I set the option "SET SERVEROUTPUT ON" on the sql file first line. – Samih A Feb 24 '14 at 09:45

score 22 · Answer 2 · answered Oct 25 '11 at 12:38

SQL Developer seems to only output the DBMS_OUTPUT text when you have explicitly turned on the DBMS_OUTPUT window pane.

Go to (Menu) VIEW -> Dbms_output to invoke the pane.

Click on the Green Plus sign to enable output for your connection and then run the code.

EDIT: Don't forget to set the buffer size according to the amount of output you are expecting.

score 9 · Answer 3 · edited Nov 29 '17 at 13:36

9

Make server output on First of all

SET SERVEROUTPUT on then
Go to the DBMS Output window (View->DBMS Output)
then Press Ctrl+N for connecting server

edited Nov 29 '17 at 13:36

mx0

6,445
12
49
54

answered Jun 18 '13 at 13:06

user2497268

91
1
1

score 7 · Answer 4 · answered Jun 20 '19 at 02:16

7

1 ) Go to view menu.
2 ) Select the DBMS_OUTPUT menu item.
3 ) Press Ctrl + N and select connection editor.
4 ) Execute the SET SERVEROUTPUT ON Command.
5 ) Then execute your PL/SQL Script.

answered Jun 20 '19 at 02:16

Lova Chittumuri

2,994
1
30
33

score 4 · Answer 5 · edited Sep 28 '18 at 11:35

4

There are 2 options:

set serveroutput on format wrapped;

or

Open the 'view' menu and click on 'dbms output'. You should get a dbms output window at the bottom of the worksheet. You then need to add the connection (for some reason this is not done automatically).

edited Sep 28 '18 at 11:35

Stefnotch

511
2
13
26

answered May 15 '13 at 14:57

devosJava

263
1
3
12

score 0 · Answer 6 · edited Jan 09 '14 at 12:44

DECLARE

  CTABLE USER_OBJECTS.OBJECT_NAME%TYPE;
  CCOLUMN ALL_TAB_COLS.COLUMN_NAME%TYPE;
  V_ALL_COLS VARCHAR2(5000);

  CURSOR CURSOR_TABLE
    IS
    SELECT OBJECT_NAME 
    FROM USER_OBJECTS 
    WHERE OBJECT_TYPE='TABLE'
    AND OBJECT_NAME LIKE 'STG%';

  CURSOR CURSOR_COLUMNS (V_TABLE_NAME IN VARCHAR2)
    IS
    SELECT COLUMN_NAME
    FROM ALL_TAB_COLS
    WHERE TABLE_NAME = V_TABLE_NAME;

BEGIN

  OPEN CURSOR_TABLE;
  LOOP
    FETCH CURSOR_TABLE INTO CTABLE;

    OPEN CURSOR_COLUMNS (CTABLE);
    V_ALL_COLS := NULL;
    LOOP

      FETCH CURSOR_COLUMNS INTO CCOLUMN;
      V_ALL_COLS := V_ALL_COLS || CCOLUMN;
      IF CURSOR_COLUMNS%FOUND THEN
        V_ALL_COLS := V_ALL_COLS || ', ';
      ELSE
        EXIT;
      END IF;
    END LOOP;
   close CURSOR_COLUMNS ;
    DBMS_OUTPUT.PUT_LINE(V_ALL_COLS);
    EXIT WHEN CURSOR_TABLE%NOTFOUND;
  END LOOP;`enter code here`
  CLOSE CURSOR_TABLE;

END;

I have added Close of second cursor. It working and getting output as well...

score 0 · Answer 7 · answered Sep 03 '21 at 13:41

In newer versions of SQL developer, there is no more separate output view that you need to show, instead it's always there, as a tab. Below your list of tabs (which SQL developer calls "windows", so for example two SQL input areas), there is another list of tabs, called "SQL", "Output" and "Statistics". The output will not open automatically, you need to click on the tab.

score -1 · Answer 8 · answered Feb 07 '13 at 09:00

-1

select View-->DBMS Output in menu and

answered Feb 07 '13 at 09:00

Bhadra

21
6

score -1 · Answer 9 · answered Feb 07 '13 at 09:04

-1

Go to the DBMS Output window (View->DBMS Output).

answered Feb 07 '13 at 09:04

user2001117

3,727
1
18
18

Printing the value of a variable in SQL Developer

9 Answers9

Linked

Related