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.

SQL Plus does not appear to be bothered by the direction of slashes, ie “/” or “\” in its scripts

Hence as long as you have a “C:\tmp” folder on your Windows drive (or wherever your SQL Pus binaary is installed/running from), you can do:

spool /tmp/blah
@/tmp/my_file.sql

and it will work on Unix and Windows without alteration.

(This also works for SQLcl as well by the way)

I just posted a video on how I get the most out of my SQL*Plus usage.  You can check it out here

I reference various elements of my login.sql file throughout the video – so here’s login.sql file – feel free to utilise any parts of it for your benefit.

col column_name     format a30
col sql_text        format a64
col segment_name    format a30 trunc
col tablespace_name format a20 trunc
col program         format a30 trunc
col what            format a50 word_wrapped
col plan_plus_exp   format a100

set lines      130
set pages      99
set trimspool  on
set exitcommit OFF
set arraysize  100
REM set serverout on


set termout off

define gname = 'SQL'
column global_name new_value gname

define IS12 = ""
define NOT12 = ""
col x new_value IS12
col y new_value NOT12

with rel as 
( select substr(regexp_replace(banner,'^.*Release '),1,2) rel
  from v$version
  where rownum = 1 )
select 
  case when rel = '12' then '--' end y,
  case when rel != '12' then '--' end x
from rel;

set verify off

select lower(user) || '@'||
&&IS12 case when sys_context('USERENV','CON_NAME') != 'CDB$ROOT' then lower(sys_context('USERENV','CON_NAME'))
&&IS12 else
lower(instance_name)
&&IS12 end
||
chr(10)||'SQL' global_name
from v$instance;

set sqlprompt '&&gname> '
host title &&gname

set verify on
set termout on

col profile     format a30
col object_name format a30
col table_name  format a30
col view_name   format a30
col username    format a30
col user_name   format a30