SqlPlus was expecting substitution variable.
Little demo case:
msutic@TESTDB> create table test (text varchar2(100)); Table created.
msutic@TESTDB> insert into test values ('Pips,Chips & Videoclips'); Enter value for videoclips: msutic@TESTDB> insert into test values ('Pips,Chips & Videoclips'); Enter value for videoclips: 1 row created. msutic@TESTDB> select * from test; TEXT ------------------------------------------- Pips,Chips 1 row selected.
So how to insert "&" into table. Here are some solutions:
msutic@TESTDB> set define off msutic@TESTDB> msutic@TESTDB> insert into test values ('Pips,Chips & Videoclips'); 1 row created. msutic@TESTDB> select * from test; TEXT ---------------------------------------------------- Pips,Chips & Videoclips 1 row selected. msutic@TESTDB> set define on
msutic@TESTDB> set escape \ msutic@TESTDB> insert into test values ('Pips,Chips \& Videoclips'); 1 row created. msutic@TESTDB> select * from test; TEXT -------------------------------------------------- Pips,Chips & Videoclips 1 row selected. msutic@TESTDB> set escape off
msutic@TESTDB> insert into test values ('Pips,Chips '|| '&' ||' Videoclips'); 1 row created. msutic@TESTDB> select * from test; TEXT -------------------------------------------------- Pips,Chips & Videoclips 1 row selected.
msutic@TESTDB> insert into test values ('Pips,Chips '|| chr(38) ||' Videoclips'); 1 row created. msutic@TESTDB> select * from test; TEXT ------------------------------------------------ Pips,Chips & Videoclips
It can be inserted by applying following code :
ReplyDeleteinsert into demo values('r'||chr(38)||'d');.but this is now working for generation of insert statements through procedure.
also you can use
ReplyDeleteSQL> set define off
SQL> insert into test values ('Pips,Chips & Videoclips');
SQL> COMMIT;
to solve this issue
Ahmed Abd El Latif
airtifa23@gmail.com
Hi Ahmed,
ReplyDeletecheck my second example.
I've noted this solution already but thanks for commenting ;)
Regards,
Marko