Please let me know how to insert the text "mehra & co" into the varchar2 datatype in SQL in 9i. Can it be done...
without allowing Oracle to allow the user to enter the text at run time? Is there any method to insert along with the query itself? Can we insert the special characters in a table? If so please let me know with an example.
In order for values to be entered into a field without having the user enter the value, the table must be created with a default value. If you are using SQL*Plus and part of the default value is a special character, you must set the DEFINE parameter to off. Below, I connected to scott, then set define to off. Next I dropped my table then re-created it with the default value with the special character "&".
SQL> connect scott/tiger Connected. SQL> set define off SQL> drop table special_chars; Table dropped. SQL> create table special_chars(id number, spec_char varchar2(100) default 'mehra & co'); Table created.
In the next set of statements, I inserted a value only in the ID field. Notice when I perform a select, the value for the field spec_char shows "mehra & co".
SQL> insert into special_chars(id) values (1); 1 row created. SQL> select id, substr(spec_char,1,20) from special_chars; ID SUBSTR(SPEC_CHAR,1,2 ---------- -------------------- 1 mehra & co
Next, I performed an insert using a subquery. Then when I performed a query, the query showed two records.
SQL> insert into special_chars(id, spec_char) 2 select id, spec_char 3 from special_chars 4 where id = 1; 1 row created. SQL> select id, substr(spec_char,1,20) from special_chars; ID SUBSTR(SPEC_CHAR,1,2 ---------- -------------------- 1 mehra & co 1 mehra & co