Database Application Developer and DBA
On Characters, Pivots,
and Endings
I want to insert a value into a table as follows:
create table test (name varchar2( 35));
insert into test values ('&Vivek');
But the system asks for a value for the
substitution variable. How can I insert an
ampersand (&)?
This is probably one of the most frequently asked questions out there, not only
on Ask Tom ( asktom.oracle.com) but on any
Oracle Database forum.
Before I give the answers, I’ll explain the
problem fully. By default, SQL*Plus will scan
each line of input and look for an & character. Upon finding it, SQL*Plus will scan
the characters after the ampersand and use
those as a variable name (the variable name
in the above example is Vivek). SQL*Plus
will then prompt the user for a value for
Vivek—like this:
SQL> insert into test (name)
values ('&Vivek');
Enter value for vivek: Hello World
old 1: insert into test (name)
values ('&Vivek')
new 1: insert into test (name)
values ('Hello World')
1 row created.
Here you can see how SQL*Plus turned
&Vivek into Hello World. Now the question
is how to stop it from doing that. The easiest
method is simply to issue the SQL*Plus set
define off command:
SQL> set define off
SQL> insert into test (name)
values ('&Vivek');
NAME
—————————————————————————————————————————
Hello World
&Vivek
That prevents SQL*Plus from scanning
the input to try to find the substitution character. Another approach is to use a different
substitution character:
SQL> set define @
SQL> insert into test (name)
values ( '&Vivek @X' );
Enter value for x: this was x
old 1: insert into test (name)
values ( '&Vivek @X' )
new 1: insert into test (name)
values ( '&Vivek this was x' )
1 row created.
In this case, the character is doing what
the & used to do.
There are other approaches, such as
avoiding the & character in your SQL:
SQL> insert into test
values (chr( 38)||'Vivek xxx');
1 row created.
SQL> select from test
where name like '% xxx';
NAME
—————————————————————————————————————————
&Vivek xxx
Although that approach works, I’m not
a fan of it, because you have to change your
SQL statement.
Yet another approach is to use a zero-length substitution variable name, which
will make SQL*Plus just leave that & character alone:
SQL> insert into test
values ('&'||'Vivek yyy');
1 row created.
SQL> select from test
where name like '% yyy';
NAME
—————————————————————————————————————————
&Vivek yyy
This is perhaps better than using chr( 38)
to avoid the & character in your SQL, but it
still is not something I recommend. By the
way, even though I’ve been using SQL*Plus
for almost 25 years, I had no idea that the
last solution—with ‘&’ ||’Vivek’—would work
that way. That was something new I learned
from a reader of Ask Tom!
DYNAMIC PIVOT
I have a table like this:
create table fish (
fish_id number,
fish_type varchar2( 3),
fish_weight number);
insert into fish values ( 1,'COD', 20);
insert into fish values( 1,'HAD', 30);
insert into fish values( 2,'COD', 45);
insert into fish values( 2,'HKE', 10);
insert into fish values( 2,'LIN', 55);
