在 SQLDeveloper 中打印变量的值

我想打印一个匿名块中特定变量的值。我正在使用 Oracle SQL 开发人员。我试过用 dbms_output.put_line。但是没有用。我正在使用的代码如下所示。

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;

我得到的输出只有 anonymous block completed。

285461 次浏览
小开
最佳答案

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

  1. Show the DBMS Output window (View->DBMS Output).
  2. 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
小开

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.

小开

select View-->DBMS Output in menu and

小开

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

小开

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).

小开

Make server output on First of all

  1. SET SERVEROUTPUT on then

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

  3. then Press Ctrl+N for connecting server

小开 
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...

小开

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.

enter image description here enter image description here

小开

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.


提交答案