Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations IamaSherpa on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Line brakes from an SQL

Status
Not open for further replies.

lhg1

IS-IT--Management
Mar 29, 2005
134
DK
Hi

An SQL that returns line brakes gives me problems.

I have this script (set by the id condition to only return 1 row). But it appears as 2 lines in the output.

Code:
#!/bin/ksh

#############################################
# Oracle setup - sets up ORACLE PATCH and echoon cridentials
. $HOME/CFG/.db_profile_JBOSS

sql="SELECT '<ROWID>'||a.rowid||'<ROWID><AID>'||a.id||'<AID>
<ACTIME>'||a.ctime||'<ACTIME><RID>'||r.id||'<RID><NAME>'||r.NAME||'<NAME>
<PRIORITY>'||ad.priority||'<PRIORITY>
<DESCRIPTION>'||ad.description||'<DESCRIPTION><VALUE>'||cl.VALUE||'<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 rt.NAME = 'JBossAS 
Server'
AND r.NAME LIKE 'balor%' 
AND ad.NAME NOT IN ('Datasources - Available connections', 'RHQ Agent - Clock difference') 
and a.id = '1063524'
ORDER BY a.ctime DESC;"

echo "set linesize 32767
set pagesize 999
set head off
set feed off
$sql"|nawk 'NF' | sqlplus -s ${JBOSSuser}/${JBOSSpass}@${JBOSSinst} | nawk 'NF' |while read line; do
    echo "---------------Whole line-----------------"
    echo "$line"
done

This is the result.
---------------Whole line-----------------
<ROWID>AAATAbAAGAAACqfAAU<ROWID><AID>1063524<AID>
<ACTIME>1235662809990<ACTIME><RID>504429<RID><NAME>xxxxxxxxxxxx JBossSOA
4.3.0.GA_SOA balor02 (172.17.61.103:1099)<NAME><PRIORITY>HIGH<PRIORITY>
<DESCRIPTION>An alert is raised if any log entries of severity ERROR are
encountered.<DESCRIPTION><VALUE>ERROR, extraInfo=
[[dservices.ResourceOrder] (http-172.33.61.3333-8080-12:) A message could
not be delivered to the ResourceOrderWSGateway service
---------------Whole line-----------------

org.jboss.soa.esb.listeners.message.MessageDeliverException: Failed<VALUE>

The thing is that it reads 2 lines. I do need to process every line as an individual element.

Any ideers on how to solve it?

Thanks
Lhg
 
Hi

You mean you have no idea inside the [tt]while[/tt] loop about which line you are currently processing ? If you are sure there are always two lines, [tt]read[/tt] them separately :
Code:
$sql"|nawk 'NF' | \
sqlplus -s ${JBOSSuser}/${JBOSSpass}@${JBOSSinst} | \
nawk 'NF' |\
(
read line
echo "processing line 1 ( $line ) in one way"

read line
echo "processing line 2 ( $line ) in another way"
)
Or I misunderstood you...

Feherke.
 
I would look to see if there's actually a line break character in the column [tt]rhq.rhq_alert_condition_log.VALUE[/tt]. That would cause it to look like two lines returned even though the query only really returns one row. If that's the case, you just need to clean up the data, or possibly how it's being loaded.

If you just want to make what was returned into one line, maybe change your last little while loop to this...
Code:
while read OUTPUT
do
    LINE="${LINE}${OUTPUT}"
done

echo "---------------Whole line-----------------"
echo ${LINE}
echo "---------------Whole line-----------------"


 
There isn't always 2 lines.

SamBones describtion of the problem is correct.
But not sure what he is dooing. and I can't get it to work either.

Any ideers on how to clean up the data og load it differently?

Regards
LHG
 
Well, the whole thing would be...
Code:
#!/bin/ksh

#############################################
# Oracle setup - sets up ORACLE PATCH and echoon cridentials
. $HOME/CFG/.db_profile_JBOSS

sql="SELECT '<ROWID>'||a.rowid||'<ROWID><AID>'||a.id||'<AID>
<ACTIME>'||a.ctime||'<ACTIME><RID>'||r.id||'<RID><NAME>'||r.NAME||'<NAME>
<PRIORITY>'||ad.priority||'<PRIORITY>
<DESCRIPTION>'||ad.description||'<DESCRIPTION><VALUE>'||cl.VALUE||'<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 rt.NAME = 'JBossAS
Server'
AND r.NAME LIKE 'balor%'
AND ad.NAME NOT IN ('Datasources - Available connections', 'RHQ Agent - Clock difference')
and a.id = '1063524'
ORDER BY a.ctime DESC;"

echo "set linesize 32767
set pagesize 999
set head off
set feed off
$sql"|nawk 'NF' | sqlplus -s ${JBOSSuser}/${JBOSSpass}@${JBOSSinst} | nawk 'NF' | while read OUTPUT
do
    LINE="${LINE}${OUTPUT}"
done

echo "---------------Whole line-----------------"
echo ${LINE}
echo "---------------Whole line-----------------"
I was just showing the changes to the last couple lines.

I don't know how your data's being loaded, so I don't know what to suggest on cleaning it up there. You could also run a query to update that column and remove the line feed character. So, one approach is to clean it up as it's being loaded, the other is to clean it up after it's been loaded. You might want to work on both. It's best to clean it up before it goes in, but it would be nice to be able to clean it up after it's in.

 
As a matter of interest, are you just copy/pasting this script from Windows? If so, try ftping it instead. Windows copy/paste sometimes doesn't play nicely with unix*.

I want to be good, is that not enough?
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top