Just execute as a script
G, March 15, 2012 - 10:51 am UTC
set define off
create table test (name varchar2(35));
insert into test values ('&Vivek');
press 'F5' to execute the above commands.
the q' operator
asktom fan, March 15, 2012 - 10:53 am UTC
Hi Tom,
After reading the question above, I thought the q' operator might be able to escape character & in sqlplus without using "set define off". Obviously, it does not:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
CORE 11.2.0.3.0 Production
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production
SQL> set null null
SQL> col lets_see for a10
SQL> select q'<&test>' lets_see from dual;
Enter value for test:
old 1: select q'<&test>' lets_see from dual
new 1: select q'<>' lets_see from dual
LETS_SEE
----------
null
So q' is not magical enough to escape everything? :)
March 15, 2012 - 1:51 pm UTC
It isn't a "sql" problem.
SQLPlus, a client program, by default looks through all text for & and does the substitution way before the SQL layer gets it. q' is a SQL think, & is a sqlplus - the client tool - thing.
Inserting values with '&'
Vivek popat, March 16, 2012 - 1:12 am UTC
set define off;
create table test (name varchar2(35));
insert into test values ('&Vivek');
I pressed the F5 key to execute the above statements as a script and it worked.
Thank You all for help.
optionally you could try this
Barry Chase, March 18, 2012 - 10:08 pm UTC
Optionally you could replace your ampersands with CHR(38) which is the equivalent. There are loss of ways to handle in the end.
March 19, 2012 - 10:11 am UTC
it is not that simple, you have to not only replace them but add concatenation
insert into test values (chr(38)||'Vivek');
Concatenation works
AndyP, March 20, 2012 - 3:26 am UTC
And, if you are going the concatenation route, you can just
insert into table1 values('&'||'hello world');
1 row created.
select * from table1;
COL1
--------------------------------------------------
&hello world
March 20, 2012 - 9:39 am UTC
nifty, i learned something new again today!
Another way
Valério, March 21, 2012 - 7:12 am UTC
Hi,
Another way that worked for me, incluindg in SQL Developer, was the use of '"' around the '&'. Example:
insert into test values ('"&"Vivek');
Thanks
March 21, 2012 - 10:21 am UTC
but that adds the double quotes to it
user1%ORA10GR2> select '"&"Hello world' from dual;
'"&"HELLOWORLD
--------------
"&"Hello world
user1%ORA10GR2>
This may be cleaner
Tony Fernandez, March 21, 2012 - 10:45 am UTC
as this may not imply changing, but only 1 line, if in large imports:
just adding SET SCAN OFF; before the operation.
SQL> create table test2(x varchar2(100));
Table created.
SQL> insert into test2(x) values('&hello world');
Enter value for hello:
old 1: insert into test2(x) values('&hello world')
new 1: insert into test2(x) values(' world')
1 row created.
SQL> rollback;
Rollback complete.
SQL> SET SCAN OFF;
SQL> insert into test2(x) values('&hello world');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test2;
X
---------------------------------------
&hello world
March 21, 2012 - 10:56 am UTC
This may be cleaner
Tony Fernandez, March 21, 2012 - 11:03 am UTC
Thanks for the update Tom. I have been using SCAN for too long. Will now change into SET DEFINE OFF;
hi tom, can u help me in this
Viknesh, May 27, 2012 - 8:56 am UTC
how come i insert record using returning into clause with bulk collect.
SQL> declare
2 type t1 is table of employees.employee_id%type;
3 type t2 is table of employees.last_name%type;
4 empno t1;
5 empname t2;
6 begin
7 insert into tab select * from employees where employee_id between 100 and 200
8 returning employee_id,first_name bulk collect into empno,empname;
9 for i in 1..empno.count loop
10 dbms_output.put_line('Deleted items are '||empno(i)||' '||empname(i));
11 end loop;
12 end;
13 /
returning employee_id,first_name bulk collect into empno,empname;
*
ERROR at line 8:
ORA-06550: line 7, column 79:
PL/SQL: ORA-00933: SQL command not properly ended
ORA-06550: line 7, column 1:
PL/SQL: SQL Statement ignored
Thanks
Viknesh, May 27, 2012 - 1:37 pm UTC
Hi tom am a new user and my name is viknesh and i keep refer your blog for solutions.
Thanks anyway i tried using VALUES in INSERT statement and got inserted successfully. and now my question is can use sub query to insert more than one row using returning into clause?
If possible give some example..
Thanks Viknesh
May 28, 2012 - 2:17 am UTC
you can only do this with values - not with the subquery.
You can do something like:
select a, b, c, d, ... bulk collect into some_array
from employees where employee_id between 100 and 200;
forall i in 1 .. some_array.count
insert into t values some_array(i);
Thanks
Viknesh, May 28, 2012 - 3:52 pm UTC
Thank for your help tom :)
Please help me in this
Vikky, May 31, 2012 - 4:46 am UTC
hi tom,
This is viknesh from pondichery India. can u give me the query to find the employee whose salary is less than the average salary of the specific department.
and i want to display all the departments and employees getting less than avg salary of the their department.
May 31, 2012 - 10:17 am UTC
I smell homework :)
How about this, I'll lead you along....
can you write a query to get the average salary in a given department? It is pretty easy right?
select avg(sal) from emp where deptno = ?
Now, if you wanted to get employees whose salary was less than something you would write:
select * from emp where salary < "something"
So, can you see how to plug in that first query into the second query?
Although, if your question is truly:
"all the departments"
then we have to do a bit more work, is that really a requirement (i doubt it, since this is homework and the correlate subquery you are supposed to be learning about is complex enough without throwing in an outer join)
The mutating thread!!
Greg, May 31, 2012 - 1:26 pm UTC
Hey Tom,
I'm reading this thread, it's about one topic.
It then suddenly changes to a completely unrelated topic!
How do I fix it??
;)
Viknesh
Viknesh, May 31, 2012 - 11:54 pm UTC
Hi Tom,
I found the solution for my previous question. and i framed a query like this.
select e1.department_id,e1.salary from employees e1,(select department_id,trunc(avg(salary))as salary from employees
group by department_id)e2
where
e1.salary<e2.salary and
e1.department_id=e2.department_id
order by 1,2
So my question is is there any better idea to write query more efficient than this??
June 01, 2012 - 6:38 am UTC
that is definitely one way. here are others:
ops$tkyte%ORA11GR2> select e1.deptno,e1.ename, e1.sal
2 from emp e1,(select deptno,trunc(avg(sal))as sal
3 from emp
4 group by deptno) e2
5 where e1.sal<e2.sal
6 and e1.deptno=e2.deptno
7 order by 1,2
8 /
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
10 MILLER 1300
20 ADAMS 1100
20 SMITH 800
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30 WARD 1250
8 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select deptno, ename, sal
2 from (select deptno, ename, sal, trunc(avg(sal) over (partition by deptno)) avg_sal
3 from emp)
4 where sal <avg_sal
5 order by 1, 2
6 /
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
10 MILLER 1300
20 ADAMS 1100
20 SMITH 800
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30 WARD 1250
8 rows selected.
ops$tkyte%ORA11GR2>
ops$tkyte%ORA11GR2> select deptno, ename, sal
2 from emp e1
3 where sal < (select trunc(avg(sal))
4 from emp e2
5 where e2.deptno = e1.deptno)
6 order by 1, 2
7 /
DEPTNO ENAME SAL
---------- ---------- ----------
10 CLARK 2450
10 MILLER 1300
20 ADAMS 1100
20 SMITH 800
30 JAMES 950
30 MARTIN 1250
30 TURNER 1500
30 WARD 1250
8 rows selected.
Thank you very much tom! much useful! :)
Viknesh, June 01, 2012 - 10:06 am UTC
Thanks alot tom! much useful! :)
To Greg from Toronto
Autonomous, June 01, 2012 - 8:23 pm UTC
You can fix a mutating thread using an autonomous poster but the result may not be what you expect
June 02, 2012 - 2:49 am UTC
;) it took me a minute to get this - very funny....
offtopic
Sokrates, June 04, 2012 - 7:26 am UTC
here another way to insert values into a table with '&':
sql > create table "table_with_&" ( i int);
Table created.
sql >
sql > insert into "table_with_&" values(0);
1 row created.
June 04, 2012 - 9:17 am UTC
that doesn't show how to insert an ampersand into a table though?
that shows that & is ignored when sql plus realizes it is in a quoted identifier
it is close to this:
ops$tkyte%ORA11GR2> select 'hello world &' from dual;
'HELLOWORLD&'
-------------
hello world &
ops$tkyte%ORA11GR2> select 'hello world &x' from dual;
Enter value for x: xxx
old 1: select 'hello world &x' from dual
new 1: select 'hello world xxx' from dual
'HELLOWORLDXXX'
---------------
hello world xxx
which was demonstrated (when the & is followed by ', sqlplus doesn't do the substitution)
&' and &" apparently are the same, if you used &something" it wouldn't work:
ops$tkyte%ORA11GR2> select * from "DUAL&X"
2 /
Enter value for x:
old 1: select * from "DUAL&X"
new 1: select * from "DUAL"
D
-
X
ops$tkyte%ORA11GR2>
A reader, September 11, 2012 - 6:34 am UTC
Barry's solution works
Jack, November 10, 2015 - 3:52 pm UTC
UPDATE <tablename>
SET ACAD_GROUP = 'A' || CHR(38) || 'S'
WHERE ...
Sets the column to 'A&S'
Use the UNISTR() function
Thomas Bender, September 20, 2016 - 3:24 pm UTC
You can use the UNISTR() function:
INSERT INTO test (name) VALUES (UNISTR ('\0026Vivek'));... and if you can stop lazy programmers from writing "&" instead of "and" in comments ("-- close crsr & ret.") your scripts will never have issues with client-side substitution again.
print prime numbers
Monika soni, August 05, 2019 - 7:39 am UTC
hi i want to print all prime numbers less than or equal to . Print your result on a single line, and use the ampersand () character as your separator (instead of a space).
August 05, 2019 - 9:28 am UTC
This sounds a *lot* like homework to me :-)
So I'll help with the primes....your task is search this site for many of the examples of LISTAGG
SQL> with digits as ( select level n from dual connect by level <= 100 )
2 select n
3 from digits d1
4 where d1.n = 1
5 or not exists
6 ( select 1
7 from digits d2
8 where mod(d1.n,d2.n)=0
9 and d2.n > 1
10 and d2.n < d1.n
11 )
12 order by 1;
N
----------
1
2
3
5
7
11
13
17
19
23
29
31
37
41
43
47
53
59
61
67
71
73
79
83
89
97
26 rows selected.
Pranav, October 07, 2019 - 11:37 am UTC
Hello Tom,
How can we achieve the same DML through Execute Immediate?
When below block is executed , the input is requested from user.
begin
execute immediate 'insert into test values (''&Vivek'')';
end;
set define off doesn't work in this case.
Can you please provide solution for this?
October 07, 2019 - 12:38 pm UTC
What are the exact commands you're using and in which client (SQL*Plus, SQL Dev, ...)?
Because it all looks fine to me:
SQL> create table test ( c1 varchar2(10) );
Table created.
SQL> set define off
SQL> begin
2 execute immediate 'insert into test values (''&Vivek'')';
3 end;
4 /
PL/SQL procedure successfully completed.
SQL> select * from test;
C1
----------
&VivekPS - you don't need execute immediate!
SQL> begin
2 insert into test values ('&Vivek');
3 end;
4 /
PL/SQL procedure successfully completed.
Pranav, October 09, 2019 - 4:45 am UTC
Oh.. Thanks a ton.
I was doing mistake of using set define off inside the begin block
as below:
begin
set define off;
execute immediate 'insert into test values (''&Vivek'')';
end;
I am using TOAD. I was just doing RND on how '&' is to be handled in dynamic query so was trying to insert using execute immediate.
Thank you once again for the solution.
October 09, 2019 - 10:33 am UTC
No problem!
Insertion failure
NATRAJ V, April 05, 2023 - 5:13 pm UTC
EXEC SQL INSERT INTO TABLE(column1,column2) VALUES(value1,value2) fails(sqlca.sqlcode != 0) in C code.
TABLE description :
Name Null? Type
==================
column1 - NULL - NUMBER(9)
column2- NULL - NUMBER(1)
column1=>value=>value1->eg. 150771326
column2=>value=>value2->eg. 1
What are all the possible reasons for failure? Your prompt reply is highly appreciated. Thanks!
April 06, 2023 - 6:46 am UTC
what error code did you get ?
Utter nonsense
Fred, April 26, 2023 - 9:26 pm UTC
It also doesn't work in SQL Developer. Oh, and what was the wonderful tool that Oracle originally gave us to use for dozens of years? SQLplus?? But, yeah, talk trash about TOAD. ha
April 27, 2023 - 1:13 pm UTC
It looks like it works in SQL Dev to me - you have to run in script mode (F5 by default)
create table test (name varchar2(35));
set define off
insert into test values ('&Vivek');
select * from test;
NAME
-----------------------------------
&Vivek