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
a) unlimited length b) all private members must have leading and trailing underscores c) underscore and ampersand are the only two special characters allowed d) none of the mentioned Answer: a Explanation: Variable names can be of any length.
CHAR is conceptually a fixed-length, blank-padded string. Trailing blanks (spaces) are removed on input, and are restored on output. The default length is 1, and the maximum length is 65000 octets (bytes).
While an individual quoted string cannot be longer than 2048 bytes, a string literal of roughly 65535 bytes can be constructed by concatenating strings.
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.
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With