How to have semicolon at the end of every line?

Hi,

I wanted to create an automated script that will output a create table statement in unix. Below is the input and the desired output:

INPUT:

desc ZZ_APL_TIDDATELIST
(
TID           NUMBER
AEX_DATE      TIMESTAMP(6)
)

desc ZZ_APL_TIDLIST
(
TID       NUMBER
)

desc ZZ_APL_TIDTYPELIST
(
TID          NUMBER
INSTYPE      VARCHAR2(3)
)

desc ZZ_CAM_AEORDER
(
ORDER_ID      NUMBER
)

desc ZZ_CAM_AETID
(
TID                          NUMBER
INSTRUMENT_GROUP_ABBREV      CHAR(3)
INSTRUMENT_TYPE_ID           NUMBER
)

desc ZZ_OM_WRK_TABLE
(
TIME                DATE
PLACED              NUMBER
RUNNING_PLACED      NUMBER
FILLED              NUMBER
RUNNING_FILLED      NUMBER
)

OUTPUT:

desc ZZ_APL_TIDDATELIST
(
TID           NUMBER;
AEX_DATE      TIMESTAMP(6)
);

desc ZZ_APL_TIDLIST
(
TID       NUMBER
);

desc ZZ_APL_TIDTYPELIST
(
TID          NUMBER;
INSTYPE      VARCHAR2(3)
);

desc ZZ_CAM_AEORDER
(
ORDER_ID      NUMBER
);

desc ZZ_CAM_AETID
(
TID                          NUMBER;
INSTRUMENT_GROUP_ABBREV      CHAR(3);
INSTRUMENT_TYPE_ID           NUMBER
);

desc ZZ_OM_WRK_TABLE
(
TIME                DATE;
PLACED              NUMBER;
RUNNING_PLACED      NUMBER;
FILLED              NUMBER;
RUNNING_FILLED      NUMBER
);

Many thanks

Your file is not consistent
; behind every line staring with ) OK

INSTYPE      VARCHAR2(3)
INSTRUMENT_GROUP_ABBREV      CHAR(3);

Why do only one of this has ; behind

Same here, why?

TID                          NUMBER;
INSTRUMENT_TYPE_ID           NUMBER

EDIT:
I do see now that where you have ) in a new line, line above do not have ;

this is so because i wanted to create a script that will output a create table statement that will be run using isql.

for the semicolon (';'), this should be comma(,). Please see updated output:

desc AEXEO.ZZ_APL_TIDTYPELIST
(
TID          NUMBER,
INSTYPE      VARCHAR2(3)
);

desc AEXEO.ZZ_CAM_AEORDER
(
ORDER_ID      NUMBER
);

desc AEXEO.ZZ_CAM_AETID
(
TID                          NUMBER,
INSTRUMENT_GROUP_ABBREV      CHAR(3),
INSTRUMENT_TYPE_ID           NUMBER
);

Not a clean small code, but his should give what you want:

awk '{sub(/^\)/,"&;");if ($0~/^[A-Z]/&& a~/^[A-Z]/ && !/^desc/) {printf ",\n%s",$0} else printf "\n%s",$0 ;a=$0} END {print ""}' file

desc ZZ_APL_TIDDATELIST
(
TID           NUMBER,
AEX_DATE      TIMESTAMP(6)
);

desc ZZ_APL_TIDLIST
(
TID       NUMBER
);

desc ZZ_APL_TIDTYPELIST
(
TID          NUMBER,
INSTYPE      VARCHAR2(3)
);

desc ZZ_CAM_AEORDER
(
ORDER_ID      NUMBER
);

desc ZZ_CAM_AETID
(
TID                          NUMBER,
INSTRUMENT_GROUP_ABBREV      CHAR(3),
INSTRUMENT_TYPE_ID           NUMBER
);

desc ZZ_OM_WRK_TABLE
(
TIME                DATE,
PLACED              NUMBER,
RUNNING_PLACED      NUMBER,
FILLED              NUMBER,
RUNNING_FILLED      NUMBER
);

EDIT: some shorter version

awk '{sub(/^\)/,"&;");s=($0~/^[A-Z]/&& a~/^[A-Z]/ && !/^desc/)?",":"";printf s"\n%s",$0;a=$0} END {print ""}' file

hi Jotne,

can you please explain what the following regex do.

Thanks

awk '{
	sub(/^\)/,"&;")					# Replace ")" with ");"
	s=($0~/^[A-Z]/&& a~/^[A-Z]/ && !/^desc/)?",":""	# IF this line and previous line start with "A-Z" and not "desc" set "s" to "," else set it to ""
	printf s"\n%s",$0				# Print this line "$0" using "s" as formating pluss new line
	a=$0} 						# set a=this line
END {
	print ""}' file					# print a new line
1 Like