Shell Programming and Scripting

Hi,

I am wanting to create a script that will construct a SQL statement based on a a space delimited string that it read from a config file.

Example of the SQL will be

For example, it will read a string like "AAA BBB CCC" and assign to a variable named IN_STRING.

I then concatenate this variable to DEFAULT_USERS so that IN_STRING="$DEFAULT_USERS $IN_STRING". DEFAULT_USERS contains "SYS SYSTEM DBSNMP RMAN"

The SQL statement will then be as below:

Code:

select username, account_status
from  dba_users
where username not in 
( $IN_STRING )

I am converting the IN_STRING variable so it will be 'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 'BBB', 'CCC' so that the resulting SQL will now be as below:

Code:

select username, account_status
from  dba_users
where username not in 
( 'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 'BBB', 'CCC' )

At the moment, I am doing as below:

Code:

IN_STRING=`echo $IN_STRING | awk '{ for(i=1; i < NF; i++) printf "\x27" $i "\x27, " ;  print "\x27"$NF"\x27" }'`

It is not the most elegant of awk but it does the trick. I am sure there will awk gurus around here who can make it look better. Feel free to recommend a more 'elegant' awk construct.

While this work, it does not look nice as the string gets longer . For example, the list might spill to 20+ space delimited word.

Can someone please advise if I can use awk to convert the string in such a way that the there will be 5 or 6 values per line? For example, instead of having the following in one line

Code:

'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 'GGG', 'HHH', 'III', 'JJJ', 'KKK', 'LLL'

I would want it to be like

Code:

'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 
'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 
'GGG', 'HHH', 'III', 'JJJ', 'KKK',
'LLL'

At the moment, I am thinking of looping thru the space delimited list, putting them into array and then going thru a loop and doing the construct. Kinda hoping I will be able to do the same using awk

Any advise will be much appreciated. Thanks in advance.

Hello newbie_01,

Following may help you in same.

Code:

echo "SYS SYSTEM DBSNMP RMAN AAA BBB CCC DDD EEE FFF GGG HHH III JJJ KKK LLL MMM NNN OOO PPP QQQ RRR SSS TTT AAA BBB CCC"  | awk -vs1="'" '{for(i=1;i<=NF;i++){printf("%s%s%s%s",s1,$i,s1,i%5==0?RS:(i==NF?RS:OFS))}}' OFS=", "

Output will be as follows.

Code:

'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA'
'BBB', 'CCC', 'DDD', 'EEE', 'FFF'
'GGG', 'HHH', 'III', 'JJJ', 'KKK'
'LLL', 'MMM', 'NNN', 'OOO', 'PPP'
'QQQ', 'RRR', 'SSS', 'TTT', 'AAA'
'BBB', 'CCC'

So this is giving till 5 fields, similarly you could do for 6 or as per your need too by doing little fine tuning to above.

Thanks,
R. Singh

Try also

Code:

echo "'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA', 'BBB', 'CCC', 'DDD', 'EEE', 'FFF', 'GGG', 'HHH', 'III', 'JJJ', 'KKK', 'LLL'" | tr "," "
" | paste -sd',,,,    
'
'SYS', 'SYSTEM', 'DBSNMP', 'RMAN', 'AAA'
 'BBB', 'CCC', 'DDD', 'EEE', 'FFF'
 'GGG', 'HHH', 'III', 'JJJ', 'KKK'
 'LLL'