Sql pr line with sqlplus and AWK

[lhg1]

Hi

I need to process an sql pr. line.

This is the code that I cant get to work.

echo "set linesize 132
set pagesize 999
set head off
set feed off
SELECT a.id, a.ctime, r.id, r.NAME, ad.priority, ad.description, cl.VALUE 
FROM rhq.rhq_alert a, rhq.rhq_alert_definition ad, rhq.rhq_alert_condition_log cl, rhq.rhq_resource r, rhq.rhq_resource_type  rt 
WHERE a.alert_definition_id = ad.id AND cl.alert_id = a.id AND r.id = ad.resource_id AND r.resource_type_id = rt.id AND ad.name = 'Datasources - Available connections' ORDER BY ctime DESC;"|awk 'NF' | \
/oracle/app/oracle/sqlplus -silent xxxxxxxx/xxxxxx@JB | \
awk 'NF' |\
while read i; do

echo $i 

done

This is the error message

awk: syntax error near line 1
awk: bailing out near line 1

This however dows work (regular sql)

/oracle/app/oracle/sqlplus -silent xxxxxxx/xxxxxx@JB <<!
SELECT a.id, a.ctime, r.id, r.NAME, ad.priority, ad.description, cl.VALUE 
FROM rhq.rhq_alert a, rhq.rhq_alert_definition ad, rhq.rhq_alert_condition_log cl, rhq.rhq_resource r, rhq.rhq_resource_type  rt 
WHERE a.alert_definition_id = ad.id AND cl.alert_id = a.id AND r.id = ad.resource_id AND r.resource_type_id = rt.id AND ad.name = 'Datasources - Available connections' ORDER BY ctime DESC;
!

Can anyone se what the problem is?

Thanks
LHG

 

[Annihilannic]

I think the first awk 'NF' you have in there is unnecessary... but apart from that it should work fine. Perhaps try nawk if you're on Solaris? Or maybe awk 'NF>0'?

Annihilannic.

 

[lhg1]

Thanks - it was on a sun server and nawk worked.

/LHG