Shell Programming and Scripting

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.

1. I need to handle the single quote by using '''
2. 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

Hi,
Hope can help me. Actually, i do not know how to use single quote<'> in awk. So i use <"> insteand of <'> and the output is ok.
If anyone know how to use single quote<'> in awk, pls kindly inform me. Thanks in advanced.
Output:

Code:

insert into mytable(id, field1, field2)
values ('1', 'myvarchar', 'my varchar"||chr(10)
||"that has carriage returns, and is making "||chr(10)
||"my script die');

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."||chr(10)
||"');

code:

Code:

awk '
{
if (index($0,"values")!=0)
	n=1;
if (index($0,");")!=0)
	n=3
if(n==1)
	{
		print $0"\"||chr(10)"
		n=2
	}
else if(n==2)
	print "||\""$0"\"||chr(10)"
else if (n==3)
	{
		print "||\""$0
		n=0
	}
else
	print $0
}' filename

Code:

awk -v q="'" -v qq='"' '{print q $0 q}' myFile

Or...

Code:

awk '{print "\042" $0 "\047"}' myFile