CodeSOD: Putting the File Out

There's a lot of room for disagreement in technology, but there's one universal, unchangeable truth: Oracle is the worst. But a second truth is that there's nothing so bad a programmer can't make it worse.

Someone at Ben's company needed to take data from a database and write it to a file. That file needed to have some specific formatting. So they used the best possible tool for the job: a PL/SQL stored procedure.

Now, PL/SQL is a... special language. The procedural elements it adds to SQL have a distinctly "we want to sell this to mainframe programmers" vibe, which makes the syntax verbose and clumsy. It frequently creates situations where things which should be easy are incredibly hard, and things which should be hard are impossible. But it's technically a feature-rich language, and you can even write web servers in it, if you want. And if you want to do that, you either work for Oracle or you should go work for Oracle, but certainly shouldn't be allowed out to mix with the general public.

In any case, Ben's predecessor decided to generate a carefully formatted text file in PL/SQL, and had... their own way of accomplishing things.

v_line := FIELD1 || ' ' || FIELD2 || ' ' || FIELD3 ' ' || FIELD4;utl_file.put_line(write_file, v_line);v_line := ' ';utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);utl_file.put_line(write_file, v_line);v_line := ...

Who needs a loop? Not this person. There's a hint, in this sample, though, that the entire thing is designed to be easily copy/pasteable. Every time they do output, they just dump utl_file.put_line(write_file, v_line) and just update v_line in between, pretty much guaranteeing that this'll be extra hard to debug when it eventually fails.

It's also worth noting that Ben supplied a small snippet, as a screenshot, which included line numbers. This block starts at line 163 of the procedure, and I suspect is followed by many, many more lines.

[Advertisement] Otter - Provision your servers automatically without ever needing to log-in to a command prompt. Get started today!