Programming

Example 1=>
I have unix ksh file and I am calling sql file from it.



(table 1 contains 2 rows -
ID Name
1 test't
2 Pet's
)

below sql statement is part of sql file:

update table1 set name = replace(name,'''','''''') where instr(name,'''') > 0 ;

--I see below statement in my log file
2 rows updated.


2 rows updated.

--after this update I see name is updated to test''''t and pet''''s

(Problem here is I see 2 rows updated two times.even if there is one row its getting updated two times)

--------------
Example 2)
SO I have created other ksh and sql and in that sql I have written same update and its working fine.


update table1 set name = replace(name,'''','''''') where instr(name,'''') > 0 ;

--I see below statement in my log file

2 rows updated.

--after this update I see name is updated to test''t and pet''s

--
Why there are 2 updates in first example?

A possible reason is that you have a slash / at the end of your SQL, which would cause the previous SQL to be re-executed.

Code:

$ cat sql1
sqlplus -s scott/tiger << !
  update table1 set name = replace(name,'''','''''') where instr(name,'''') > 0 ;
/
!

echo Returned $?

Code:

2 rows updated.


2 rows updated.


Returned 0

If it's not that, please post the script and the SQL (using code tags).

Hi scottn,

Thanks...

That is correct reason that \ is causing update twice. But why \ is causing twice update?

-thanks

A slash (/ (not a backslash (\) as you wrote)) will cause SQL*Plus to run whatever SQL is in the SQL buffer. Since you already ran that command (update .... ;), using / will cause it to be re-executed.