Thursday, June 28, 2007

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:

  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}';
ORA-01756: quoted string not properly terminated

No comments: