Subject: bug#11481: 24.0.94; Oracle interactive SQL mode not allowing & in substitution text.

From: Shannon Severance <[email-masked]>
Date: Tue, 15 May 2012 11:44:29 -0400

Steps to reproduce:
1. Start emacs from icon installed by addpm.exe
2. M-x sql-oracle
3. Enter proper username, password & database at prompts
4. At Oracle's sql*plus prompt enter:
   select '&x' from dual;
=> Emacs prompts in *Minibuf-1*, "Enter value for x: "
5. Enter &y in the mini buffer.
=> Emacs prompts in *Minibuf-1*, "Enter value for y: "
6. Enter z in the mini buffer.
=> In the *SQL* buffer:
   SQL> select '&x' from dual;

   '
   -
   z

   Elapsed: 00:00:00.29
   SQL>

Expected result after step 5:
   SQL> select '&x' from dual;

   '&
   --
   &y

--- Contents of my sql*plus login.sql file:
set serveroutput on size unlimited format wrapped
set trimspool on
set long 5000
set linesize 170
set pagesize 50000
set tab off
set arraysize 100
set define off
alter session set nls_date_format = 'SYYYY-MM-DD HH24:Mi:SS';
alter session set nls_timestamp_format = 'SYYYY-MM-DD HH24:Mi:SS.FF9';
alter session set nls_timestamp_tz_format = 'SYYYY-MM-DD HH24:Mi:SS.FF9 TZH:TZM';
set timing on
--- End of login.sql

Compare to:
1. M-x shell
-- Results:
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.

r:\>sqlplus
sqlplus

SQL*Plus: Release 11.1.0.6.0 - Production on Tue May 15 08:27:07 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Enter user-name: sseverance
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

Session altered.

Session altered.

Session altered.

SQL> set define on
SQL> select '&x' from dual;
Enter value for x: &y
old   1: select '&x' from dual
new   1: select '&y' from dual

'&
--
&y

Elapsed: 00:00:00.32
SQL>
-- end results

I found I could get the behavior I wanted by customizing Sql Oracle Scan
On to off (nil), which puts the following in my init.el file:
(custom-set-variables
 ;; custom-set-variables was added by Custom.
 ;; If you edit it by hand, you could mess it up, so be careful.
 ;; Your init file should contain only one such instance.
 ;; If there is more than one, they won't work right.
 '(column-number-mode t)
 '(org-src-fontify-natively t)
 '(org-src-preserve-indentation nil)
 '(org-src-window-setup (quote current-window))
 '(scheme-program-name "petite")
 '(sql-oracle-scan-on t)
 '(tool-bar-mode nil)
 '(transient-mark-mode t))
-- end init.el snipit.

In GNU Emacs 24.0.94.1 (i386-mingw-nt6.1.7601)
 of 2012-03-19 on MARVIN
Windowing system distributor `Microsoft Corp.', version 6.1.7601
Configured using:
 `configure --with-gcc (4.6) --no-opt --enable-checking --cflags
 -ID:/devel/emacs/libs/libXpm-3.5.8/include
 -ID:/devel/emacs/libs/libXpm-3.5.8/src
 -ID:/devel/emacs/libs/libpng-dev_1.4.3-1/include
 -ID:/devel/emacs/libs/zlib-dev_1.2.5-2/include
 -ID:/devel/emacs/libs/giflib-4.1.4-1/include
 -ID:/devel/emacs/libs/jpeg-6b-4/include
 -ID:/devel/emacs/libs/tiff-3.8.2-1/include
 -ID:/devel/emacs/libs/gnutls-3.0.9/include'

Important settings:
  value of $LC_ALL: nil
  value of $LC_COLLATE: nil
  value of $LC_CTYPE: nil
  value of $LC_MESSAGES: nil
  value of $LC_MONETARY: nil
  value of $LC_NUMERIC: nil
  value of $LC_TIME: nil
  value of $LANG: ENU
  value of $XMODIFIERS: nil
  locale-coding-system: cp1252
  default enable-multibyte-characters: t

Major mode: SQLi[Oracle]

Minor modes in effect:
  shell-dirtrack-mode: t
  tooltip-mode: t
  mouse-wheel-mode: t
  menu-bar-mode: t
  file-name-shadow-mode: t
  global-font-lock-mode: t
  font-lock-mode: t
  blink-cursor-mode: t
  auto-composition-mode: t
  auto-encryption-mode: t
  auto-compression-mode: t
  column-number-mode: t
  line-number-mode: t
  transient-mark-mode: t

Recent input:
<return> s e l e c t SPC ' Q <backspace> <backspace>
<backspace> <backspace> <backspace> <backspace> <backspace>
<backspace> <backspace> s e t SPC d e f i n e SPC o
f f <backspace> <backspace> <backspace> o n <return>
s e l e c t SPC ' & x ' SPC f r o m SPC d u a l ; <return>
& x <backspace> y <return> C-x o <escape> x e m a c
s <tab> r <tab> <backspace> <backspace> <backspace>
<backspace> <backspace> <backspace> <backspace> r e
p o r t <tab> <return>

Recent messages:
For information about GNU Emacs and the GNU system, type C-h C-a.
Login...done

Load-path shadows:
None found.

Features:
(shadow sort gnus-util mail-extr emacsbug message format-spec rfc822 mml
mml-sec mm-decode mm-bodies mm-encode mail-parse rfc2231 mailabbrev
gmm-utils mailheader sendmail rfc2047 rfc2045 ietf-drums mm-util
mail-prsvr mail-utils shell sql thingatpt org-info org byte-opt warnings
bytecomp byte-compile cconv macroexp advice help-fns advice-preload
ob-emacs-lisp ob-tangle ob-ref ob-lob ob-table org-footnote org-src
ob-comint ob-keys ob ob-eval org-pcomplete pcomplete comint ansi-color
ring org-list org-faces org-compat org-entities org-macs noutline
outline easy-mmode regexp-opt cal-menu easymenu calendar cal-loaddefs
org-install time-date tooltip ediff-hook vc-hooks lisp-float-type mwheel
dos-w32 disp-table ls-lisp w32-win w32-vars tool-bar dnd fontset image
fringe lisp-mode register page menu-bar rfn-eshadow timer select
scroll-bar mouse jit-lock font-lock syntax facemenu font-core frame cham
georgian utf-8-lang misc-lang vietnamese tibetan thai tai-viet lao
korean japanese hebrew greek romanian slovak czech european ethiopic
indian cyrillic chinese case-table epa-hook jka-cmpr-hook help simple
abbrev minibuffer button faces cus-face files text-properties overlay
sha1 md5 base64 format env code-pages mule custom widget
hashtable-print-readable backquote make-network-process multi-tty emacs)