PL/SQL blocks that use Oracle's DBMS_OUTPUT.PUT_LINE() function are not returning the contents passed to that function #489

New issue

Open

opened 2026-03-04 10:13:43 -05:00 by deekerman · 0 comments

deekerman commented

2026-03-04 10:13:43 -05:00

Owner

Originally created by @DanHartman on GitHub (Apr 17, 2023).

Describe the bug
PL/SQL blocks that use Oracle's DBMS_OUTPUT.PUT_LINE() function are not returning the contents passed to that function.

Context
Issue 1432: https://github.com/dbeaver/cloudbeaver/issues/1432 was closed and rolled into version 23.0.0. That ticket encompassed the failure to compile PL/SQL blocks specifically connected to an Oracle database. This ticket is a result of testing PL/SQL on the new version. I can observe, that while the code compiles and makes transactions as necessary, I do not see it print any information back to cloudbeaver's GUI that had been passed to the DBMS_OUTPUT.PUT_LINE() function

To Reproduce
Here is an example that creates a user with minimal provisioning, creates a table, and inserts a row of data in that table.
Steps to reproduce the behavior:

Launch an SQL session in cloudbeaver and connect to your favorite oracle database
Create the user

-- ensure user BARRY exists
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM ALL_USERS WHERE USERNAME = 'BARRY';
  IF (BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('Creating user BARRY');
    EXECUTE IMMEDIATE 'CREATE USER BARRY IDENTIFIED BY "BARRY"';
    EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO BARRY';
  ELSE
    DBMS_OUTPUT.PUT_LINE('user BARRY exists');
  END IF;
END;

Highlight the above script and press CTRL+ENTER to execute
Observe the results window, it echos the script, but produces no further output
Create the table

-- ensure table BARRY.BAR exists
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM DBA_TABLES WHERE OWNER = 'BARRY' AND TABLE_NAME = 'BAR';
  IF(BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('Creating table BARRY.BAR');
    EXECUTE IMMEDIATE 'CREATE TABLE BARRY.BAR ("LAST_UPDATED" DATE)';
  ELSE
    DBMS_OUTPUT.PUT_LINE('table BARRY.BAR exists');
  END IF;
END;

Highlight the above script and press CTRL+ENTER to execute
Observe the results window, it echos the script, but produces no further output
Insert a row

-- ensure table BARRY.BAR has data in it
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM BARRY.BAR;
  IF(BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('inserting data');
    EXECUTE IMMEDIATE 'INSERT INTO BARRY.BAR (LAST_UPDATED) VALUES(sysdate)';
  ELSE
    DBMS_OUTPUT.PUT_LINE('table BARRY.BAR has ' || BARRY_COUNT || ' rows');
  END IF;
END;

Highlight the above script and press CTRL+ENTER to execute
Observe the results window, it echos the script, but produces no further output
Issue SELECT query to view that table

SELECT * FROM BARRY.BAR;

Observe that a single row exists
An attempt was made to make these blocks idempotent. If you run the insert block again, you will see that the PL/SQL blocks are correctly identifying that there is already a row, and not inserting more.

DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM BARRY.BAR;
  IF(BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('inserting data');
    EXECUTE IMMEDIATE 'INSERT INTO BARRY.BAR (LAST_UPDATED) VALUES(sysdate)';
  ELSE
    DBMS_OUTPUT.PUT_LINE('table BARRY.BAR has ' || BARRY_COUNT || ' rows');
  END IF;
END;

SELECT * FROM BARRY.BAR;

Observe that there is still only a single row

Now, here's something interesting

The behavior described above is similar to what would happen if you executed the blocks with sqlplus AND you did NOT issue the statement

SET SERVEROUTPUT ON;

If you execute these blocks in their entirety with sqlplus you will see that it acknowledges the statements, compiles, executes, but does not print anything.

cat <<'EOF' | sqlplus -s ${DB_CONNECTION_STRING} as SYSDBA
-- ensure user BARRY exists
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM ALL_USERS WHERE USERNAME = 'BARRY';
  IF (BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('Creating user BARRY');
    EXECUTE IMMEDIATE 'CREATE USER BARRY IDENTIFIED BY "BARRY"';
    EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO BARRY';
  ELSE
    DBMS_OUTPUT.PUT_LINE('user BARRY exists');
  END IF;
END;
/

-- ensure table BARRY.BAR exists
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM DBA_TABLES WHERE OWNER = 'BARRY' AND TABLE_NAME = 'BAR';
  IF(BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('Creating table BARRY.BAR');
    EXECUTE IMMEDIATE 'CREATE TABLE BARRY.BAR ("LAST_UPDATED" DATE)';
  ELSE
    DBMS_OUTPUT.PUT_LINE('table BARRY.BAR exists');
  END IF;
END;
/

-- ensure table BARRY.BAR has data in it
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM BARRY.BAR;
  IF(BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('inserting data');
    EXECUTE IMMEDIATE 'INSERT INTO BARRY.BAR (LAST_UPDATED) VALUES(sysdate)';
  ELSE
    DBMS_OUTPUT.PUT_LINE('table BARRY.BAR has ' || BARRY_COUNT || ' rows');
  END IF;
END;
/

-- verify PL/SQL block made changes
SELECT * FROM BARRY.BAR;
EOF

Returns:


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


PL/SQL procedure successfully completed.


LAST_UPDA
---------
17-APR-23

So let's re-run it with the SET statement

cat <<'EOF' | sqlplus -s ${DB_CONNECTION_STRING} as SYSDBA
-- enable DBMS_OUTPUT.PUT_LINE() to print data
SET SERVEROUTPUT ON;

-- ensure user BARRY exists
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM ALL_USERS WHERE USERNAME = 'BARRY';
  IF (BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('Creating user BARRY');
    EXECUTE IMMEDIATE 'CREATE USER BARRY IDENTIFIED BY "BARRY"';
    EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO BARRY';
  ELSE
    DBMS_OUTPUT.PUT_LINE('user BARRY exists');
  END IF;
END;
/

-- ensure table BARRY.BAR exists
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM DBA_TABLES WHERE OWNER = 'BARRY' AND TABLE_NAME = 'BAR';
  IF(BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('Creating table BARRY.BAR');
    EXECUTE IMMEDIATE 'CREATE TABLE BARRY.BAR ("LAST_UPDATED" DATE)';
  ELSE
    DBMS_OUTPUT.PUT_LINE('table BARRY.BAR exists');
  END IF;
END;
/

-- ensure table BARRY.BAR has data in it
DECLARE BARRY_COUNT INTEGER;
BEGIN
  SELECT COUNT(*) INTO BARRY_COUNT FROM BARRY.BAR;
  IF(BARRY_COUNT < 1) THEN
    DBMS_OUTPUT.PUT_LINE('inserting data');
    EXECUTE IMMEDIATE 'INSERT INTO BARRY.BAR (LAST_UPDATED) VALUES(sysdate)';
  ELSE
    DBMS_OUTPUT.PUT_LINE('table BARRY.BAR has ' || BARRY_COUNT || ' rows');
  END IF;
END;
/

-- verify PL/SQL block made changes
SELECT * FROM BARRY.BAR;
EOF

You can see the output of each DBMS_OUTPUT.PUT_LINE() function intermingled with the "success" responses

user BARRY exists

PL/SQL procedure successfully completed.

table BARRY.BAR exists

PL/SQL procedure successfully completed.

table BARRY.BAR has 1 rows

PL/SQL procedure successfully completed.


LAST_UPDA
---------
17-APR-23

I would expect that information passed to this function would display in cloudbeaver for analysis and/or debugging purposes

I tried executing the SET statement via cloudbeaver, but it returns the following error

Desktop (please complete the following information):

LInux 6.2.10-arch1-1
Chrome Version 112.0.5615.49 (Official Build) (64-bit)
Version 23.0.0 && 23.0.2

Originally created by @DanHartman on GitHub (Apr 17, 2023). **Describe the bug** `PL/SQL` blocks that use Oracle's `DBMS_OUTPUT.PUT_LINE()` function are not returning the contents passed to that function. **Context** Issue 1432: https://github.com/dbeaver/cloudbeaver/issues/1432 was closed and rolled into version `23.0.0`. That ticket encompassed the failure to compile `PL/SQL` blocks specifically connected to an Oracle database. This ticket is a result of testing PL/SQL on the new version. I can observe, that while the code compiles and makes transactions as necessary, I do not see it print any information back to cloudbeaver's GUI that had been passed to the `DBMS_OUTPUT.PUT_LINE()` function **To Reproduce** Here is an example that creates a user with minimal provisioning, creates a table, and inserts a row of data in that table. Steps to reproduce the behavior: 1. Launch an SQL session in cloudbeaver and connect to your favorite oracle database 2. Create the user ```sql -- ensure user BARRY exists DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM ALL_USERS WHERE USERNAME = 'BARRY'; IF (BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('Creating user BARRY'); EXECUTE IMMEDIATE 'CREATE USER BARRY IDENTIFIED BY "BARRY"'; EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO BARRY'; ELSE DBMS_OUTPUT.PUT_LINE('user BARRY exists'); END IF; END; ``` 3. Highlight the above script and press `CTRL+ENTER` to execute 4. Observe the results window, it echos the script, but produces no further output ![image](https://user-images.githubusercontent.com/1824639/232608496-6d2338ea-0199-4322-a944-93b5a786be90.png) 5. Create the table ```sql -- ensure table BARRY.BAR exists DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM DBA_TABLES WHERE OWNER = 'BARRY' AND TABLE_NAME = 'BAR'; IF(BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('Creating table BARRY.BAR'); EXECUTE IMMEDIATE 'CREATE TABLE BARRY.BAR ("LAST_UPDATED" DATE)'; ELSE DBMS_OUTPUT.PUT_LINE('table BARRY.BAR exists'); END IF; END; ``` 7. Highlight the above script and press `CTRL+ENTER` to execute 8. Observe the results window, it echos the script, but produces no further output ![image](https://user-images.githubusercontent.com/1824639/232608752-d85f5a6c-2cff-4096-995e-f749a0dab0a4.png) 9. Insert a row ```sql -- ensure table BARRY.BAR has data in it DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM BARRY.BAR; IF(BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('inserting data'); EXECUTE IMMEDIATE 'INSERT INTO BARRY.BAR (LAST_UPDATED) VALUES(sysdate)'; ELSE DBMS_OUTPUT.PUT_LINE('table BARRY.BAR has ' || BARRY_COUNT || ' rows'); END IF; END; ``` 10. Highlight the above script and press `CTRL+ENTER` to execute 11. Observe the results window, it echos the script, but produces no further output ![image](https://user-images.githubusercontent.com/1824639/232609217-d24d8124-1f6a-4d56-9252-5d5ec8ebb702.png) 12. Issue `SELECT` query to view that table ```sql SELECT * FROM BARRY.BAR; ``` 13. Observe that a single row exists ![image](https://user-images.githubusercontent.com/1824639/232609867-019da629-bb59-4641-b343-42db9b5e091e.png) 14. An attempt was made to make these blocks idempotent. If you run the insert block again, you will see that the PL/SQL blocks are correctly identifying that there is already a row, and not inserting more. ```sql DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM BARRY.BAR; IF(BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('inserting data'); EXECUTE IMMEDIATE 'INSERT INTO BARRY.BAR (LAST_UPDATED) VALUES(sysdate)'; ELSE DBMS_OUTPUT.PUT_LINE('table BARRY.BAR has ' || BARRY_COUNT || ' rows'); END IF; END; SELECT * FROM BARRY.BAR; ``` 15. Observe that there is still only a single row ![image](https://user-images.githubusercontent.com/1824639/232610542-e621d3cc-d1a0-45bd-ad3a-00667d3b0350.png) ## Now, here's something interesting The behavior described above is similar to what would happen if you executed the blocks with `sqlplus` **AND** you did *NOT* issue the statement ```sql SET SERVEROUTPUT ON; ``` If you execute these blocks in their entirety with `sqlplus` you will see that it acknowledges the statements, compiles, executes, but does not print anything. ```sh cat <<'EOF' | sqlplus -s ${DB_CONNECTION_STRING} as SYSDBA -- ensure user BARRY exists DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM ALL_USERS WHERE USERNAME = 'BARRY'; IF (BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('Creating user BARRY'); EXECUTE IMMEDIATE 'CREATE USER BARRY IDENTIFIED BY "BARRY"'; EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO BARRY'; ELSE DBMS_OUTPUT.PUT_LINE('user BARRY exists'); END IF; END; / -- ensure table BARRY.BAR exists DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM DBA_TABLES WHERE OWNER = 'BARRY' AND TABLE_NAME = 'BAR'; IF(BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('Creating table BARRY.BAR'); EXECUTE IMMEDIATE 'CREATE TABLE BARRY.BAR ("LAST_UPDATED" DATE)'; ELSE DBMS_OUTPUT.PUT_LINE('table BARRY.BAR exists'); END IF; END; / -- ensure table BARRY.BAR has data in it DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM BARRY.BAR; IF(BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('inserting data'); EXECUTE IMMEDIATE 'INSERT INTO BARRY.BAR (LAST_UPDATED) VALUES(sysdate)'; ELSE DBMS_OUTPUT.PUT_LINE('table BARRY.BAR has ' || BARRY_COUNT || ' rows'); END IF; END; / -- verify PL/SQL block made changes SELECT * FROM BARRY.BAR; EOF ``` Returns: ```sh PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. LAST_UPDA --------- 17-APR-23 ``` So let's re-run it with the SET statement ```sh cat <<'EOF' | sqlplus -s ${DB_CONNECTION_STRING} as SYSDBA -- enable DBMS_OUTPUT.PUT_LINE() to print data SET SERVEROUTPUT ON; -- ensure user BARRY exists DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM ALL_USERS WHERE USERNAME = 'BARRY'; IF (BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('Creating user BARRY'); EXECUTE IMMEDIATE 'CREATE USER BARRY IDENTIFIED BY "BARRY"'; EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO BARRY'; ELSE DBMS_OUTPUT.PUT_LINE('user BARRY exists'); END IF; END; / -- ensure table BARRY.BAR exists DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM DBA_TABLES WHERE OWNER = 'BARRY' AND TABLE_NAME = 'BAR'; IF(BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('Creating table BARRY.BAR'); EXECUTE IMMEDIATE 'CREATE TABLE BARRY.BAR ("LAST_UPDATED" DATE)'; ELSE DBMS_OUTPUT.PUT_LINE('table BARRY.BAR exists'); END IF; END; / -- ensure table BARRY.BAR has data in it DECLARE BARRY_COUNT INTEGER; BEGIN SELECT COUNT(*) INTO BARRY_COUNT FROM BARRY.BAR; IF(BARRY_COUNT < 1) THEN DBMS_OUTPUT.PUT_LINE('inserting data'); EXECUTE IMMEDIATE 'INSERT INTO BARRY.BAR (LAST_UPDATED) VALUES(sysdate)'; ELSE DBMS_OUTPUT.PUT_LINE('table BARRY.BAR has ' || BARRY_COUNT || ' rows'); END IF; END; / -- verify PL/SQL block made changes SELECT * FROM BARRY.BAR; EOF ``` You can see the output of each `DBMS_OUTPUT.PUT_LINE()` function intermingled with the "success" responses ```sh user BARRY exists PL/SQL procedure successfully completed. table BARRY.BAR exists PL/SQL procedure successfully completed. table BARRY.BAR has 1 rows PL/SQL procedure successfully completed. LAST_UPDA --------- 17-APR-23 ``` ### I would expect that information passed to this function would display in cloudbeaver for analysis and/or debugging purposes I tried executing the `SET` statement via cloudbeaver, but it returns the following error ![image](https://user-images.githubusercontent.com/1824639/232612014-6ea6a5c0-dacc-4b79-a6a1-ca75516a9daf.png) **Desktop (please complete the following information):** - LInux `6.2.10-arch1-1` - Chrome Version 112.0.5615.49 (Official Build) (64-bit) - Version `23.0.0 && 23.0.2`