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
.
PL/SQL allows you to set a default value for a variable at the declaration time. To assign a default value to a variable, you use the assignment operator ( := ) or the DEFAULT keyword. In this example, instead of using the assignment operator := , we used the DEFAULT keyword to initialize a variable.
To do this we use a procedure called dbms_output. put_line to place the results in a buffer that SQL*Plus will retrieve and display. SQL*Plus must be told to retrieve data from this buffer in order to display the results. The SQL*Plus command 'set serveroutput on' causes SQL*Plus to retrieve and display the buffer.
How do you see the result of DBMS_OUTPUT in SQL Developer? First, go to the View menu and select DBMS Output (shortcut is Alt+V, then D). This will display the DBMS Output panel.
You need to turn on dbms_output. In Oracle SQL Developer:
In SQL*Plus:
SET SERVEROUTPUT ON
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With