0

The below is a snippet that updates a customers name using SQL*Plus. It works ok for all customers except for customers whose name contains a '&' symbol even though the name is 'enclosed'. 

sqlplus -s $user/$pass@$db << EOF
UPDATE order_address SET name = '$customer_name' WHERE address_type = '2' AND order_number = '$order_number';
COMMIT;
EXIT;
EOF

What should be done so that it works even on customers whose name contains a '&'?

Update: It seems it does actually work but wherever there is a '&' it puts 'COMMIT;' so the name shows as 'A COMMIT; B' instead of 'A & B'.

user2656114
  • 949
  • 4
  • 18
  • 35
  • 1
    Many answers here: http://stackoverflow.com/questions/118190/how-do-i-ignore-ampersands-in-a-sql-script-running-from-sql-plus?rq=1 – Miika L. Nov 07 '13 at 14:52

1 Answers1

2

Can you try with "SET DEFINE OFF"?

SQL> SET SERVEROUTPUT ON
SQL> SET DEFINE OFF
SQL> BEGIN
  2  DBMS_OUTPUT.PUT_LINE('A&B');
  3  END;
  4  /
A&B

PL/SQL procedure successfully completed.
the_slk
  • 2,172
  • 1
  • 11
  • 10