Mutating String Concatenation in SQL

I don't remember who told me, "You aren't building an operating system." But they meant keep it simple, sucker!

I saw something funny at work today. I will recreate it from memory. The previous developers had written a query, but split up each section into different variables, and then used string concatenation to reassemble the query. And then later in the file, another developer had reassigned the same variables and did the same thing for a different query on a different table.

DECLARE
          -- DECLARE around 50 variables...
          foo    varchar2(50);
          bar    varchar2(50);
          glux   varchar2(50);
          query  varchar2(500);
      BEGIN
          foo := 'select * from ';
          bar := 'myfavtable where cheese is not ';
          glux := 'stinky ';
          query := '(' || foo || bar || glux || ')';
          --do whatever with query
      
          foo := 'select * from ';
          bar := 'myothertable where col1 = 12345 ';
          glux := 'or col1 = 12346 ';
          query := '(' || foo || bar || glux || ')';
          --do whatever with query, again
      END
      ;

And then it just keeps going.

I don't want to go into why the script uses variables for the queries, but for the rest…. folks, do yerselves a solid and only:

DECLARE
          -- Assign the correct size after writing the query.
          query  varchar2(24);
          query  varchar2(26);
      BEGIN
          query1 := 'SELECT * FROM MYFAVTABLE';
          --do whatever with query
      
          query2 := 'SELECT * FROM MYOTHERTABLE';
          --do whatever with query, again
      END
      ;