Upgrading SQL*Plus Client

Sometimes the old ways are best, sometimes not. I have some rarely-used machines where I had never bothered to upgrade my client installation, and so were still running the 9i SQL*Plus. The older SQL*Plus could still compile all my PL/SQL source that used the latest and greatest 10g features, so what's the big deal?

As it turns out, there are a number of cases where having a downlevel SQL*Plus produces cryptic errors. I went looking in the docs, but I don't think there is any comprehensive listing of these cases. (It would be difficult to make an exhaustive list; it might not help much with troubleshooting because the errors are so cryptic; and I'm sure the workaround -- just upgrade! -- is stated many times in many places. Still, I would have liked to have found a list that showed all the errors that could occur, so I could find it by searching.)

For me, the feature that prompted me to finally upgrade was the alternative quoting syntax. It worked fine in a PL/SQL block:

begin
  insert into some_table (title) values (q'{Administrator's Guide}');

But not if I used the quoting feature directly in SQL, i.e. in a query from the SQL*Plus command line or in a SQL script with INSERT etc. not wrapped inside a PL/SQL block:

SQL> select title from some_table where title like q'{Administrator's}';
ERROR:
ORA-01756: quoted string not properly terminated

What's the Capital of Abyssinia?

Few subjects provoke as much passion as the question of how to capitalize programming code.

You've got C-like languages where case is significant, but the discussion continues whether to string_words_along or JamWordsTogether. My own convention for PL/SQL is a bit idiosyncratic. I decide whether a particular subprogram is more procedural or more object-oriented, and use underscores or Camel case as appropriate for each one.

Then you've got lots of other languages, like PL/SQL, where case is not significant. But you'd still like to format source code for readability and comprehensibility. I commonly see the convention of reserved words in all caps, and lowercase for names of tables, variables, etc. That allows for some degree of consistency in documentation -- you can talk about "the IF statement" and the reserved word is easy to pick out even if the text is stripped of all formatting in a man page or an e-mail. But if Oracle ships packages like DBMS_OUTPUT, does it make sense to capitalize those names as if they're reserved, or treat them more like user-defined objects? Rather than drive myself to distraction, I use lowercase for everything in source code (after all, nobody ever accused C or Perl code of being hard to read... er... :-). When referring to reserved words in text, I use all-caps.

Now, I am well aware that there is an uppercase stigma attached to some languages. FORTRAN 77 was case-sensitive and got endless grief for that. (Why, I left my small-town roots rather than accept a job working in FORTRAN.) Fortran 90 enabled the use of lowercase, which was such a big deal that it was reflected in the language name. At IBM, where I eventually worked my way up to working on XL Fortran :-), it was common to switch within the same sentence: "The IBM Toronto Lab took over the FORTRAN mission, and released several Fortran compilers, while the mainframe FORTRAN products continued to be developed by the Santa Teresa lab". This after years of working on Ada, which also had an uppercase stigma because everyone assumes it should be ADA. (It's Ada, because it's an eponym not an acronym.)

There is some uppercase lurking inside the database. Issue create table foo and it will dutifully create a table named FOO, as you can see by 'select table_name from user_tables'. You can make the table be named FooBar by enclosing the name in double quotes: create table "FooBar". But then you have to put the quotes everywhere you refer to the table, so it's not worth the trouble except in unusual circumstances where you want to obscure the name, or have names identical except for case.

The tilt towards uppercase means you more often see code like this for doing case-insensitive matches:

select last_name from my_table where upper(last_name) like 'A%';

You could just as well write:

...lower(last_name) like 'a%';

and people might regard that line with more respect. These days, out of principle, I always use a regular expression instead:

select last_name from my_table where regexp_like(last_name,'^a','i');

In real code, anyway. On the command line, I still slip back into LIKE out of habit. It puzzles me a bit that SQL can't handle the BOOL... bool... Boolean datatype of PL/SQL, yet regexp_like is testing a Boolean condition without any comparison operator.

Why Is It So Hard to Print?

A knock that seems to apply to any "enterprise"-class language is, why is it so hard to actually print anything? For purposes of this post, we'll consider PL/SQL and Java.

In Java, a hello world program must call System.out.println(). That 3-level call is kind of verbose, plus in the CamelCased world of Java you might forget whether the second part is "Out" or the third part is "PrintLn".

In PL/SQL, a hello world program must call dbms_output.put_line. Don't forget to issue "set serveroutput on size 1000000" in SQL*Plus first, or the output is held in a buffer instead of printed immediately. When you see this call in the docs, it will probably look like DBMS_OUTPUT.PUT_LINE, which will make you think it's combining the keywords of Pascal and the capitalization of FORTRAN 77. In the Oracle9i PL/SQL Guide, I had deep metaphysical anguish over whether to show such a hello world example in Chapter 1, or if it had to wait until much later after discussions of stored procedures and packages. Of course, I chose to break with linearity and show it early.

In contrast, scripting languages like Perl and Python let you just issue: print "hello world". Why make it hard to do simple I/O? How is that a selling point for a language?

You can think of it as an implicit nudge towards a particular style of programming. Both Java and PL/SQL would prefer that you write your own function named print(), or msg(), or debug(), or whatever that is essentially a 1-liner that calls System.out.println or dbms_output.put_line. That's because the program that today just prints something to the screen, tomorrow might store the output in a database, or send it in an e-mail, or transmit it in some other way, or maybe send it to multiple output sources. The idea being that it's easier to change your one print() function than to go back and change every system-defined print call to your own function name.

With Java, you might have several kinds of objects, and in each one print() might do something different.

With PL/SQL, you might define print() as a nested procedure inside a bunch of other procedures, or each package might have its own print() function. PL/SQL makes the "store in a database" option so easy, that you might find it's the most convenient way to go for any function like debug(), log(), etc.

Unix hackers might find all of this unnatural, because in the environment where you would normally be doing Perl or Python scripts, you could easily redefine where the output goes by way of pipes, redirects like > or 2>&1, or the tee command.

However, the zen of writing a bunch of seemingly trivial one-line functions is part and parcel of both object-oriented and functional programming, so just cast your mind back into LISP hacker mode if necessary and it won't seem so painful.