Mutating String Concatenation in SQL | Details

Who needs an ORM when we can build it all ourselves but not take advantage of our work!

Posted on 2020-03-20

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
;