Oracle PL/SQL: modify a variable using a loop and return the result

Question

Suppose I have this dummy table.

create table dummy_table as
select 'A' letter from dual
union all
select 'B' letter from dual;

... and need to concatenate all values from the letter column into one clob variable, each value beginning on a new line.

I've been trying something along these lines, but it doesn't work as my knowledge of PL/SQL is unfortunately limited. The code below isn't working, but conveys the idea.

var my_text clob; -- def? define? declare?

begin
 for c in ( select letter from dummy_table ) 
  loop
    my_text := my_text  || c.letter || chr(10);
  end loop;
end;

In the end, need to be able to output the result

print(my_text); -- dbms_output.put_line(my_text)? dbms_sql.return_result(my_text)?

While it says PL/SQL procedure successfully completed., printing my_value returns an empty variable.

Using SQL Developer and Oracle 19c.

Answer 1

VAR is short for VARIABLE and is an SQL*Plus command that defines a bind variable for use in the client application (i.e. SQL*Plus, SQL Developer or SQLcl). It is not a PL/SQL command. Similarly, PRINT is an SQL*Plus command and is not part of the SQL or PL/SQL languages.

If you want to use a bind variable then you need to prefix it with : when you are using it in SQL or PL/SQL.

VARIABLE my_text clob;

BEGIN
  FOR c IN (SELECT letter FROM dummy_table) 
  LOOP
    :my_text := :my_text  || c.letter || chr(10);
  END LOOP;
END;
/

PRINT my_text;

-- or

BEGIN
  DBMS_OUTPUT.PUT_LINE(:my_text);
END;
/

However, if you want to do it in a PL/SQL block (and not using client-base bind variables) then DECLARE a local variable and use that:

DECLARE
  my_text CLOB;
BEGIN
  DBMS_OUTPUT.ENABLE();

  FOR c IN (SELECT letter FROM dummy_table) 
  LOOP
    my_text := my_text  || c.letter || chr(10);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE(my_text);
END;
/