How to get declared varchar2 size in oracle database

Question

Trying to get the size of a specific variable in Oracle. I may need to use a number when declaring a varchar2 size, but rather I will not need to track an additional variable or number.

pseudo code example:

declare 
  myvar varchar(42) := 'a';
begin

  /* I know the length is length(myvar) = 1. */
  /* but how do I get 42? */
  /* What is the max defined size of this variable */
  declared_size_of(myvar);  
end

The reason I need this is to combine the length of the string into the declared size so that it does not throw an exception.

Answer 1

As @Justin explained in his comments, you do not need to explicitly fill in the string if you use the CHAR data type . Oracle will be empty - enter the value in the maximum size.

From the documentation ,

CHAR, PL/SQL . .

,

SQL> SET serveroutput ON
SQL> DECLARE
  2    myvar CHAR(42);
  3  BEGIN
  4    myvar:='a';
  5    dbms_output.put_line(LENGTH(myvar));
  6  END;
  7  /
42

PL/SQL procedure successfully completed.

SQL>

Answer 2

Brute Force , , , :

 DECLARE
   myvar varchar2(42) := 'a'; /* using varchar */
   v_size number := null;
   x varchar(4000) := '';
   v_length number := 0;
   BEGIN
     begin
        v_length := length(myvar);
        x := myvar;
        FOR i in v_length..8001 LOOP
           myvar := myvar || ' '; /* add one space at a time until it causes an exception */
        End Loop;   
        EXCEPTION
           -- WHEN NO_DATA_FOUND THEN
              WHEN OTHERS THEN
                v_length := length(myvar);
     end;
     dbms_output.put_line('Declared size is varchar('||v_length
                          ||') and length(myvar) is '||length(trim(myvar)));
   END;

Answer 3

To get max. input column, you just can do:

SELECT MAX(LENGTH(Column)) 
FROM TableA;