I have a file with a set of insert statements some of which have a single column value that crosses multiple lines causing the statement to fail in sql*plue. Can someone help me with a sed script to replace the new lines with chr(10)?
here is an example:
insert into mytable(id, field1, field2)
values ('1', 'myvarchar', 'my varchar
that has carriage returns, and is making
my script die');
insert into mytable(id, field1, field2)
values ('2', 'myvarchar2', 'my varchar
that has carriage returns, and is making
my script die. This one is even trickier because
it contains a quote that I didn't expect.
');
Note that the value for field2 (third column) crosses multiple lines. The first case I would need to replace each new line with:
' || chr(10)
and the start of the following line with:
||'
resulting in:
insert into mytable(id, field1, field2)
values ('1', 'myvarchar', 'my varchar '||chr(10)||'that has carriage '|| chr(10) ||'returns, and is making '|| chr(10) ||' my script die');
In the second case I would need to do the same as the first case with the following addtions.
- I need to handle the single quote by using '''
- because the last line doesn't have any text I can replace the last carriage return with
'||chr(10)
resulting in:
insert into mytable(id, field1, field2)
values ('2', 'myvarchar2', 'my varchar '||chr(10)|| 'that has carriage returns, and is making '||chr(10)||'my script die. This one is even trickier because '||chr(10)||'it contains a quote that I didn't expect.');
This has really stumped me and I am hoping there are some guru's out there that can help me out. So far I have come up with this script that prints the lines that I want to process:
/,.*'[^';]*$/p
The way I read it is search for a "," followed by 0 or more characters (I wanted just whitespace but \s doesn't work) followed by a single quote and 0 or more characters but not ' ; before the end of the line. Put simply, a single quote optional characters but not the end quote nor end statement before the end of the line.
So I expect that this finds the start of patterns like in the case #1 now I need to find the end of the pattern and process the chunk like I mentioned above.
Anyway, I'd really appreciate some help. T.I.A.
JJ