Concatenating text in PL/SQL in a more concise way

Question

I'm trying to find the most efficient way to concatenate text in PL/SQL. My system scans five variables, and does something along these lines:

    return_value := ''
    IF variable_a is null THEN
        return_value := 'Error: Variable A null';
    END IF;

    IF variable_b is null THEN
        IF return_value = '' THEN
            return_value := 'Error: Variable B null';
        ELSE
            return_value := return_value || ', Variable B null';
    END IF;

    IF variable_c is null THEN
        IF return_value = '' THEN
            return_value := 'Error: Variable C null';
        ELSE
            return_value := return_value || ', Variable c null';
    END IF;

    etc.

Hopefully the logic of what I'm trying to do is clear - determine if any of the five variables are null and concatenate the text of each error message to the return value, so by the end of the bunch of if/else statements the return value is either null (good) or contains an error call (bad).

Is there a better way to do this though? It seems rather lengthy and I feel, as a PL/SQL newbie, I'm missing some obvious way of doing this in a much more efficient, shorter manner.

In some cases PL/SQL is a verbose language. Usually it's not a worth of reducing the verbosity as the clarity of the code will degrade too much in many cases. This is especially true for beginners. From the 3 answers below I'd prefer Bacs answer for beginners for it's simplicity though FrancescoGenta's helper function solution is also great. Dmitry's answer is clever one too, but not exactly the solution I'd like to see in my code base for such a simple problem (after one week it will take a lot of developers' brain cycles to decode what is going on there). — user272735
– user272735, Commented Nov 10, 2014 at 6:53

Bacs · Accepted Answer · 2014-11-06 17:57:19Z

There are plenty of ways to do this I'm sure. One possibility is to concatenate all the errors in the same way, as 'Variable ? null, ', then if necessary prefix with 'Error: ' and remove the redundant comma and space at the end.

return_value := '';

IF variable_a is null THEN
    return_value := 'Variable A null, ';
END IF;

IF variable_b is null THEN
    return_value := return_value||'Variable B null, ';
END IF;

IF variable_c is null THEN
    return_value := return_value||'Variable C null, ';
END IF;

IF variable_d is null THEN
    return_value := return_value||'Variable D null, ';
END IF; 

IF variable_e is null THEN
    return_value := return_value||'Variable E null, ';
END IF;

IF return_value <> '' THEN
    return_value := 'Error: '||return_value;         
    return_value := RTRIM(return_value, ', ');
END IF;

Francesco Genta · Accepted Answer · 2014-11-06 12:22:50Z

You can define a function that simulate ternary operator, this makes more readible your code in my opinion. The final presentation of error obviously can be improved.

declare

  return_value varchar2(4001);

  variable_a varchar2(1) := null;
  variable_b varchar2(1) := 'B';
  variable_c varchar2(1) := 'C';

  function iif(predicate boolean, if_true varchar2, if_false varchar2 := '') return varchar2 as
  begin
    if(predicate) then
      return if_true;
    else
      return if_false;
    end if;
  end;

begin

  return_value := iif(variable_a is null, ', Variable A null')
    || iif(variable_b is null, ', Variable B null')
    || iif(variable_c is null, ', Variable C null');  

  return_value := iif(return_value is not null, 'Error: ' || substr(return_value, 2, length(return_value)));

  dbms_output.put_line('[' || return_value || ']');

end;

Dmitriy · Accepted Answer · 2014-11-06 13:27:05Z

My decision is quite bizarre, but I like it:

declare
  a varchar2(10) := '';
  b varchar2(10) := 'brt';
  c varchar2(10) := '';
  d varchar2(10) := 'sdf';
  e varchar2(10) := '';
  return_value varchar2(1000);
begin
  select listagg(dcd, ',') within group (order by dcd)
    into return_value
    from (select decode(col, null, 'variable ' || n || ' is null', null) dcd
            from (select a col, 'a' n from dual union all
                  select b, 'b' from dual union all
                  select c, 'c' from dual union all
                  select d, 'd' from dual union all
                  select e, 'e' from dual));

  dbms_output.put_line('return_value: ' || return_value);
end;
/

return:

return_value: variable a is null,variable c is null,variable e is null

Collectives™ on Stack Overflow

Concatenating text in PL/SQL in a more concise way

3 Answers 3

Comments

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Related