Hi all,

Here is my question:

DECLARE
    l_something VARCHAR2( --<- WHAT NOW?

Okay, that's not a good question. Please let me explain.
I understand that in Oracle SQL, memory or rather storage, is allocated dynamically for VC2 columns. But that's not the topic.

In PL/SQL I can think of two goals:
A: allocate as much memory as possibly needed, add that much load to the PGA, grant maximum efficiency to data changes
B: allocate as little memory as needed (dynamically), limit load on PGA, be okay with waiting for memory reallocation

If my thoughts up to this point are mistaken, please explain (and stop reading here).

Otherwise and assuming I understand when to use A over B (and vice versa) in my application, how can I ensure I'm getting the behavior I want?

In https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/tuning.htm#LNPLS01203 (that's 11g), I found this:

You might need to allocate large VARCHAR2 variables when you are not sure how big an expression result will be. You can conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. Specify a size of more than 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed.

In the PL/SQL docs for 19c I couldn't find anything like this. I did not take the time to read the whole document, so if it's there and I overlooked it, please tell me where to look, maybe (and stop reading here).

Also I'd totally understand the editor if he'd silently deleted that paragraph.
And yes, it's 2019 where I am, because time zones.

Given the 11g implementation is still up, does that mean I should

DECLARE
    l_something VARCHAR2(4001);
    l_or_something VARCHAR2(4001 CHAR); --^^??

whenever I plan to load data from my VC2 column into my variable?

create or replace function get_stat (p_stat in varchar2) return number as
  l_return  number;
begin
  select ms.value
  into   l_return
  from   v$mystat ms
  join   v$statname sn
  on     ms.statistic# = sn.statistic#
  and    sn.name = p_stat;
  return l_return;
end get_stat;
/

conn chris
set serveroutput on
declare
  type arr is table of varchar2(1)
    index by pls_integer;
  a   arr;
  mem pls_integer;
begin
  mem := get_stat('session pga memory');
  
  for i in 1 .. 1000000 loop
    a(i) := 'a';
  end loop;
 
  DBMS_OUTPUT.put_line(
    'Length 1: ' || (get_stat('session pga memory') - mem)
  );
end;
/
conn chris
set serveroutput on
declare
  type arr is table of varchar2(4000)
    index by pls_integer;
  a   arr;
  mem pls_integer;
begin
  mem := get_stat('session pga memory');
  
  for i in 1 .. 1000000 loop
    a(i) := 'a';
  end loop;
 
  DBMS_OUTPUT.put_line(
    'Length 4000: ' || (get_stat('session pga memory') - mem)
  );
end;
/
conn chris
set serveroutput on
declare
  type arr is table of varchar2(32767)
    index by pls_integer;
  a   arr;
  mem pls_integer;
begin
  mem := get_stat('session pga memory');
  
  for i in 1 .. 1000000 loop
    a(i) := 'a';
  end loop;
 
  DBMS_OUTPUT.put_line(
    'Length 32767: ' || (get_stat('session pga memory') - mem)
  );
end;
/

Length 1: 68026368
Length 4000: 68026368
Length 32767: 68026368