Re: Insert failure

Norman Dunbar wrote:
> 'set scan off' in sqlplus does the job. Not sure about SQLLoader - as I
> don't use it myself (yet).

Hmm, you can always tell the old-timers in the Oracle world. The 'set scan' command is obsolete (though I don't know what that means exactly, since it still works). It has been replaced by the 'set define' command. I suppose they replaced it because you can now do the job of two set commands in one.

The old command: *set scan on | off*
The new command: *set define on | off | prefix_char*

Both 'set scan off' and 'set define off' perform the identical function of disabling variable substitution.

When you start sqlplus, variable substitution is on by default, with a default prefix character of ampersand. If you don't use any of these set commands, you can use the default escape of backslash (modifiable by the 'set escape' command):

insert into test values ('Hello \& Goodbye');

One additional gotcha that the above example doesn't cover is what do you do to terminate the substitution variable name (above example will prompt for a value of Goodbye)? Suppose I want to retrieve the column EMP_ID from the table EMP with one substitution variable:

SQL> define table="EMP"
SQL> select &&table._id from &&table;

Notice the dot after '&&table' - it's the default concatenation operator and serves to terminate the substitution variable so I don't get asked to supply the value of TABLE_ID. Received on Wed Oct 23 2002 - 10:49:46 CDT