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