Is it possible to get the maximum possible length of a variable

Question

I was wondering if a function giving the maximum length of a variable exists in plsql.

For example, if I declare

DECLARE

varia VARCHAR2(7)
BEGIN
   call of a function that would return 7
END

even though varia is null, I could get the length 7 of the varchar.

--- exemple

create or replace 
TYPE ENREG_320_03 UNDER ENREG_320_BASE(
date_creation VARCHAR2(8),
raison_sociale_emetteur VARCHAR2(35),
adresse_emetteur_1 VARCHAR2(35),
adresse_emetteur_2 VARCHAR2(35),
adresse_emetteur_3 VARCHAR2(35),
num_siret VARCHAR2(14),
ref_remise VARCHAR2(16),
code_bic_emetteur VARCHAR2(11),
type_ident_compte_debit VARCHAR2(1),
ident_compte_debit VARCHAR2(34),
code_devise_compte_debit VARCHAR2(3),
ident_client VARCHAR2(16),
type_ident_compte_frais VARCHAR2(1),
ident_compte_frais VARCHAR2(34),
code_devise_compte_frais VARCHAR2(3),
zone_reserve VARCHAR2(16),
indice_type_debit_remise VARCHAR2(1),
indice_type_remise  VARCHAR2(1),
date_execution_souhait VARCHAR2(8),
devise_transfert VARCHAR2(3),

MEMBER FUNCTION get_date_creation RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.date_creation IS NULL THEN lpad(' ', 8, ' ') ELSE   rpad(SELF.date_creation, 8, ' ') END;
END get_date_creation;

MEMBER FUNCTION get_raison_sociale_emetteur RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.raison_sociale_emetteur IS NULL THEN lpad(' ', 35, ' ') ELSE rpad(SELF.raison_sociale_emetteur, 35, ' ') END;
END get_raison_sociale_emetteur;

MEMBER FUNCTION get_adresse_emetteur_1 RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.adresse_emetteur_1 IS NULL THEN lpad(' ', 35, ' ') ELSE rpad(SELF.adresse_emetteur_1, 35, ' ') END;
END get_adresse_emetteur_1;

MEMBER FUNCTION get_adresse_emetteur_2 RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.adresse_emetteur_2 IS NULL THEN lpad(' ', 35, ' ') ELSE rpad(SELF.adresse_emetteur_2, 35, ' ') END;
END get_adresse_emetteur_2;

MEMBER FUNCTION get_adresse_emetteur_3 RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.adresse_emetteur_3 IS NULL THEN lpad(' ', 35, ' ') ELSE rpad(SELF.adresse_emetteur_3, 35, ' ') END;
END get_adresse_emetteur_3;

MEMBER FUNCTION get_num_siret RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.num_siret IS NULL THEN lpad(' ', 14, ' ') ELSE rpad(SELF.num_siret, 14, ' ') END;
END get_num_siret;

MEMBER FUNCTION get_ref_remise RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.ref_remise IS NULL THEN lpad(' ', 16, ' ') ELSE rpad(SELF.ref_remise, 16, ' ') END;
END get_ref_remise;

MEMBER FUNCTION get_code_bic_emetteur RETURN VARCHAR2 AS
BEGIN
   RETURN CASE WHEN SELF.code_bic_emetteur IS NULL THEN lpad(' ', 11, ' ') ELSE rpad(SELF.code_bic_emetteur, 11, ' ') END;
END get_code_bic_emetteur;

MEMBER FUNCTION get_type_ident_compte_debit RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.type_ident_compte_debit IS NULL THEN lpad(' ', 1, ' ') ELSE rpad(SELF.type_ident_compte_debit, 1, ' ') END;
END get_type_ident_compte_debit;

MEMBER FUNCTION get_ident_compte_debit RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.ident_compte_debit IS NULL THEN lpad(' ', 34, ' ') ELSE rpad(SELF.ident_compte_debit, 34, ' ') END;
END get_ident_compte_debit;

MEMBER FUNCTION get_code_devise_compte_debit RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.code_devise_compte_debit IS NULL THEN lpad(' ', 3, ' ') ELSE rpad(SELF.code_devise_compte_debit, 3, ' ') END;
END get_code_devise_compte_debit;

MEMBER FUNCTION get_ident_client RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.ident_client IS NULL THEN lpad(' ', 16, ' ') ELSE rpad(SELF.ident_client, 16, ' ') END;
END get_ident_client;

MEMBER FUNCTION get_type_ident_compte_frais RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.type_ident_compte_frais IS NULL THEN lpad(' ', 1, ' ') ELSE rpad(SELF.type_ident_compte_frais, 1, ' ') END;
END get_type_ident_compte_frais;

MEMBER FUNCTION get_ident_compte_frais RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.ident_compte_frais IS NULL THEN lpad(' ', 34, ' ') ELSE rpad(SELF.ident_compte_frais, 34, ' ') END;
END get_ident_compte_frais;

MEMBER FUNCTION get_code_devise_compte_frais RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.code_devise_compte_frais IS NULL THEN lpad(' ', 3, ' ') ELSE rpad(SELF.code_devise_compte_frais, 3, ' ') END;
END get_code_devise_compte_frais;

MEMBER FUNCTION get_zone_reserve RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.zone_reserve IS NULL THEN lpad(' ', 16, ' ') ELSE rpad(SELF.zone_reserve, 16, ' ') END;
END get_zone_reserve;

MEMBER FUNCTION get_indice_type_debit_remise RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.indice_type_debit_remise IS NULL THEN lpad(' ', 1, ' ') ELSE rpad(SELF.indice_type_debit_remise, 1, ' ') END;
END get_indice_type_debit_remise;

MEMBER FUNCTION get_indice_type_remise RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.indice_type_remise IS NULL THEN lpad(' ', 1, ' ') ELSE rpad(SELF.indice_type_remise, 1, ' ') END;
END get_indice_type_remise;

MEMBER FUNCTION get_date_execution_souhait RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.date_execution_souhait IS NULL THEN lpad(' ', 8, ' ') ELSE rpad(SELF.date_execution_souhait, 8, ' ') END;
END get_date_execution_souhait;

MEMBER FUNCTION get_devise_transfert RETURN VARCHAR2 AS
BEGIN
  RETURN CASE WHEN SELF.devise_transfert IS NULL THEN lpad(' ', 3, ' ') ELSE rpad(SELF.devise_transfert, 3, ' ') END;
END get_devise_transfert;

So I was wondering if a simple function existed to get the size of the field so I dont have to use numbers in the getters : if the size of the fields is changed, there would be no need to change the getters, it would work out whatever the size of the varchar

But I understand it is not possible

Thank you to everyone

Answer 1

The approach with the %TYPE attribute Alex Poole suggested is one from the best practices... but just FYI I provide an example for VARCHAR2 variable:

create or replace function f_declared_length (
     p in out varchar2
   )
     return integer
   is
     r integer;
     a varchar2(32767) := p;
     function f_c ( p in out char ) return integer is
     begin
       return length(p);
     end;
   begin
     p := 1;
     r := f_c(p);
     p := a;
     return r;
   end;
/

Now let's test this function:

set serveroutput on
declare
   v1 varchar2(23331);
   v2 varchar2(500);
   v3 varchar2(10);

begin
  dbms_output.put_line ( f_declared_length (v1));
  dbms_output.put_line ( f_declared_length (v2));
  dbms_output.put_line ( f_declared_length (v3));
end;
/

anonymous block completed
23331
500
10

Here is an original code and an idea. It's based on the conclusion that Oracle internally knows length of the variable when converts it's value from VARCHAR2 to CHAR explicitly while going through call stack. Such information is used to decide how much spaces need to be appended to an actual VARCHAR2 parameter value.

Answer 2

In your specific use case, since you're doing this within a type and not in an anonymous block or stored procedure, you could get the information from the user_type_attrs view:

create or replace type t42 as object (
  id number
) not final;
/

create or replace type t42_sub under t42 (
  value varchar2(8),
  constructor function t42_sub(p_value in varchar2) return self as result,
  member function get_value return varchar2
);
/

create or replace type body t42_sub as 
  constructor function t42_sub(p_value in varchar2) return self as result is
  begin
    value := p_value;
    return;
  end t42_sub;

  member function get_value return varchar2 is
    l_attr_len number;
  begin
    select length into l_attr_len
    from user_type_attrs
    where type_name = 'T42_SUB'
    and attr_name = 'VALUE';

    return case when self.value is null then lpad(' ', l_attr_len, ' ')
      else rpad(self.value, l_attr_len, ' ') end;
  end get_value;
end;
/

And then using that type gives:

with t as (
  select t42_sub('AA').get_value() as val from dual
  union all select t42_sub(null).get_value() as val from dual
)
select val, '<'|| val ||'>', length(val)
from t;

VAL             '<'||VAL||'>'   LENGTH(VAL)
--------------- --------------- -----------
AA              <AA      >                8 
                <        >                8 

Clearly you could write a function to get the length for the type/attr_name, rather than repeating the select in each member function.

I suspect it will be quite expensive though, unless you can come up with a caching mechanism. If the objects are long-lived you could do the look-up in the constructor I suppose:

create or replace type t42_sub under t42 (
  value varchar2(8),
  max_value_len number,
  constructor function t42_sub(p_value in varchar2) return self as result,
  member function get_value return varchar2
);
/

create or replace type body t42_sub as
  constructor function t42_sub(p_value in varchar2) return self as result is
  begin
    value := p_value;

    select length into max_value_len
    from user_type_attrs
    where type_name = 'T42_SUB'
    and attr_name = 'VALUE';
    return;
  end t42_sub;

  member function get_value return varchar2 is
  begin
    return case when self.value is null then lpad(' ', max_value_len, ' ')
      else rpad(self.value, max_value_len, ' ') end;
  end get_value;
end;
/

But it still seems like something you should be handling in source control rather than at runtime, either explicitly setting max_value_len := 8 in the type declaration (next to the value so you'll hopefully notice they both need to be changed), or with a creation script that uses substitution variables.

Answer 3

suPPLer (clap) really cool idea
just for fun look at my way )))

declare
    l_var varchar2(10);
begin
    for i in 1 .. 32767 loop
        begin
            l_var := l_var || '*';
        exception
            when others then
                dbms_output.put_line('max length for l_var is ' ||  to_char(i - 1));
                exit;
        end;
    end loop;
end;