Print variable value in SQL Developer

Question

I wanted to print the value of a specific variable inside an anonymous block. I am using Oracle SQL Developer. I tried using dbms_output.put_line . But that does not work. The code I'm 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 get the output only as anonymous block completed .

Answer 1

You need to enable dbms_output. In Oracle SQL Developer:

• Show DBMS output window (View-> DBMS Output).
• Click the “+” button at the top of the Dbms output window, and then select an open database connection in the dialog box that opens.

In SQL * Plus:

  SET SERVEROUTPUT ON 

Answer 2

SQL Developer seems to only output DBMS_OUTPUT text when you explicitly enable the DBMS_OUTPUT window pane.

Go to (Menu) VIEW → Dbms_output to bring up the panel.

Click the green plus sign to enable output for your connection, and then run the code.

EDIT: Remember to set the buffer size according to the amount of expected result.

Answer 3

Make server output first

• SET SERVEROUTPUT on then

• Go to the DBMS output window (View-> DBMS Output)

• then press Ctrl + N to connect the server

Answer 4

There are 2 options:

 set serveroutput on format wrapped; 

or

Open the View menu and click on Output to DBMS. You should get a dbms output window at the bottom of the sheet. Then you need to add a connection (for some reason this is not done automatically).

Answer 5

 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 added Close to the second cursor. He works and gets a way out as well ...

Answer 6

1) Go to the browse menu.
2) Select the DBMS_OUTPUT menu item.
3) Press Ctrl + N and select the connection editor.
4) Run the SET SERVEROUTPUT ON command.
5) Then execute the PL / SQL script.

Answer 7

select "View" → "DBMS Output" in the menu and

Answer 8

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