dbms_output.put_line doesn't work inside a Cursor For loop of a stored procedure

Question

I am having a bizarre problem that seems very specific to CURSOR FOR Loops inside of a stored procedure. For clarity, I am using Oracle within DBeaver and am attempting to loop over all of the columns in a table and print out the results of a select statement.

I don't have access to the exact code but this is functionally approximate:

CREATE OR REPLACE PROCEDURE column_null(table_name_in IN VARCHAR2) 
AS
str_query VARCHAR2(1000);
temp_number NUMBER(10);
CURSOR col_cursor IS
SELECT * FROM user_tab_cols 
WHERE table_name = table_name_in;

BEGIN
FOR c_id IN col_cursor
LOOP
  str_query := 'select COUNT(*) FROM ' || table_name_in || 
               ' WHERE ' || c_id.column_name || ' IS NOT NULL';
  EXECUTE IMMEDIATE str_query INTO temp_number;
  DBMS_OUTPUT.PUT_LINE(temp_number);
END LOOP;
END;

Now, the bizarre part is that if I do this exact same code block outside of a stored function (minus an extra DECLARE keyword), it works as expected. Even if I try to just echo out 'Hello' within a loop it works as expected, but as soon as it becomes a stored procedure it stops working. I've been testing this for hours today, and am completely baffled; for reference, I have only recently become acquainted with PL/SQL so its mysteries escape me.

Furthermore, it seems specific to CURSOR FOR loops; if I replace the Cursor For loop with a generic numeric loop (i.e. FOR c_id IN 1 .. 10), a procedure will produce output just fine. And it isn't just DBMS_OUTPUT.PUT_LINE that's affected; pretty much everything that goes on inside the Cursor For loop is ignored in a stored procedure, including variable updates, even though they work fine otherwise in normal PL/SQL blocks.

To summarize: Works fine as a PL/SQL block, works fine in a numeric for loop, but for some reason the exact combination of stored procedure and cursor for loop causes no output to be produced; in fact from my testing it seems like nothing meaningful happens within the cursor for loop of a stored function.

Is this a DBeaver bug? A PL/SQL oddity? I'm posting here because I'm ignorant as to whether this is expected behavior due to how Procedures and/or Cursor For loops work, or if this is a bug of some kind.

Answer 1

What you have done is declaring a procedure. Now that you have declared it, you have to call it using a program like bellow. Most likely it will generate outputs.

Option 01

    set serveroutput on;
    Declare
      v_table_name_in IN VARCHAR2(499);

    Begin
      v_table_name_in := 'your table name';
      column_null(table_name_in => v_table_name_in);
    end;  

Option 02 Get a return parameter. ideally a table type as out parameter. and inside the above code, loop through it and print the value.

Option 03. Log the outputs into a log table.