From: Kev Jackson <kevin.jackson@...>
Date: 2005-10-07T18:23:43+09:00
Subject: final sub problem

I've almost finished, but there's something I don't know how to do.

with
str = "INSERT INTO PEOPLE (USER_ID, NAME) VALUES (1, 'Fred')"

str.sub!(/PEOPLE/, 'KITTENS')

the replaced word is unquoted (ie)

irb(main):001:0> str = "INSERT INTO PEOPLE (USER_ID, NAME) VALUES (1, 
'Fred')"
=> "INSERT INTO PEOPLE (USER_ID, NAME) VALUES (1, 'Fred')"
irb(main):002:0> str.sub!(/PEOPLE/, 'KITTENS')
=> "INSERT INTO KITTENS (USER_ID, NAME) VALUES (1, 'Fred')"
irb(main):003:0>

But the same doesn't apply with:

str = "INSERT INTO TEST (DATE) VALUES (01/01/2000)
str.sub!(/(\d*\/\d*\/\d*)/,'TO_DATE(\'\1\', \'MM/DD/YYYY\')')

irb(main):003:0> str = "INSERT INTO TEST (DATE) VALUES (01/01/2000)
irb(main):004:0" "
=> "INSERT INTO TEST (DATE) VALUES (01/01/2000)\n"
irb(main):005:0> str.sub!(/(\d*\/\d*\/\d*)/,'TO_DATE(\'\1\', 
\'MM/DD/YYYY\')')
=> "INSERT INTO TEST (DATE) VALUES (TO_DATE('01/01/2000', 'MM/DD/YYYY'))\n"
irb(main):006:0>

In irb its fine (see above), but in my script, str is actually one 
element of an array

eg
data[23]=data[23].sub!(/(\d*\/\d*\/\d*)/,'TO_DATE(\'\1\', \'MM/DD/YYYY\')')

after joining the array back to get my sql statement, I find (to my 
outright disgust it must be said), that the damn value is quoted:

[snip]'DDS818/017/001',0,  'TO_DATE('01/01/2000', 
'MM/DD/YYYY')',3,4,16,4,1,5,3,25,1,15,[snip]

And I'm pretty certain Oracle is going to crap itself with that quoted 
string.

is there a way of ensuring that when joining, certain values are not quoted?

Kev