When Your Bind Variable is Invalid and Your Column is Weird

Did you know that you cannot use a bind variable if it has a specific name? And did you know that column names in your tables can be as cryptic as !@#$%^?

Invalid bind variable name

SQL reserved words cannot be used as bind variables. For example:

  SQL> var to number;
  SQL> var from number;
  SQL> var where number;
  SQL> var myto number;
  SQL> exec :to := 1; :from := 1; :where := 1; :myto := 1;

  PL/SQL procedure successfully completed.

  SQL> SELECT * FROM dual WHERE 1 = :to;
  SELECT * FROM dual WHERE 1 = :to
                                *
  ERROR at line 1:
  ORA-01745: invalid host/bind variable name


  SQL> SELECT * FROM dual WHERE 1 = :from;
  SELECT * FROM dual WHERE 1 = :from
                                *
  ERROR at line 1:
  ORA-01745: invalid host/bind variable name


  SQL> SELECT * FROM dual WHERE 1 = :where;
  SELECT * FROM dual WHERE 1 = :where
                                *
  ERROR at line 1:
  ORA-01745: invalid host/bind variable name


  SQL> SELECT * FROM dual WHERE 1 = :myto;

  D
  -
  X

Update Oct 14 2006: Refer to Ed’s post on how you can use the reserved words as bind variables as long as you quote them.

Weird, but valid column names

Someone at OraQA.com asked how to avoid special characters in column names. So, I did a little experiment and discovered that you could create tables with column names containing special characters like punctuation marks, dollar signs, percent signs… pretty much most of the keys on your keyboard. For example:

SQL> set define off
SQL> CREATE TABLE t
  2    ("this%is,so-,cool$,man()" VARCHAR2(10),
  3    "This@#is^~even+&cooler" NUMBER)
  4  /

Table created.

SQL> desc t;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------

 this%is,so-,cool$,man()                            VARCHAR2(10)
 This@#is^~even+&cooler                             NUMBER

SQL> INSERT INTO t
  2       VALUES ('A', 1)
  3  /

1 row created.

SQL> INSERT INTO t
  2       VALUES ('B', 2)
  3  /

1 row created.

SQL> SELECT *
  2    FROM t
  3  /

this%is,so This@#is^~even+&cooler
---------- ----------------------
A                               1
B                               2

Now, who would use such column names and why?!

Update Oct 14 2006: Refer to Sergio’s and Marco’s posts for more quotation magic.

Related articles:

• One more reason to upgrade your Oracle 8i
• CASE gotcha in Oracle 8i
• Rename column or constraint in Oracle

Filed in Oracle, Tips on 25 Sep 06 | Tags: Oracle

---