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 onmsutic@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 offmsutic@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