Amped on Amper

This “problem” rates in the top 10 all time viewed questions on AskTom, and it demonstrates the importance of reading the Concepts guide with Oracle

SQL> create table CARTOONS ( name varchar2(30));

Table created.

SQL> insert into CARTOONS values ('Tom & Jerry');
Enter value for jerry:

And the question comes in: “How can I insert an ampersand into my table?”

And the correct response is – the database treats ampersand exactly the same as any other character.  Which of course, just confuses the heck out of people, because they are staring at their screen, being asked for a value for “Jerry” and it’s obvious that ampersands are not the same as any character.

So what’s going on here ?  There is an excellent section in the Concepts Guide which talks about the way we actually communicate to the database, and that is via a client program.

The client program passes our requests to the database, and awaits it’s response.  The client program is not the database, it is a means of communicating to the database.  And whatever client program you are using, it may have features designed to assist you (which in fact may just be confusing you).

Whether it be SQL Plus, or SQL Developer, or TOAD, or (insert any tool here), there’s a good chance it has a facility to allow the end user to provide input during the execution of a SQL statement.  (More accurately, its asking you for input before it sends the statement for execution).  And for many tools, the ampersand is the special character that indicates “pause for input”.

So in the SQL Plus example above, it’s just a case of telling the client tool that the ampersand is not to be used for this purpose:

SQL> set define off
SQL> insert into CARTOONS values ('Tom & Jerry');

1 row created.

SQL> set define &

Just remember. This has nothing to do with the database.