sqlplus devider

[lhg1]

hi

I have a sqlstatement that returns a lot of rows from sqlplus, and I need to go throu them row by row.

I can do this by spooling them to a file and then reading the file row by row, but I would rather not put them into a file. And do it by a variable.

This is the sql that I and working from.

a=`sqlplus -silent sndappsco/barney@p01sc1 << EOF
set linesize 132
set pagesize 999
set head off
set feed off
SELECT phd_id,(SELECT COUNT(*) FROM q3 WHERE dvc_trx_sts_cd='PE' AND phd_id=A.phd_id) Count
FROM phd_port_info A
GROUP BY phd_id;
EOF`

for i in $a
do
echo $i
done

this puts the result into the variable a. And i can go throu this one , but not row by row (as far as I know.)

One of the ideers i have if I could get the SQL to put a devider between the results, putting every row into one.

Does anybody know how i in sqlplus puts a devider between the colums.

Or perhaps even better, knows a smarter way to handle sqlplus output in from rows.


Regards
LHG

 

[feherke]

Hi

I would try it this way :

echo "set linesize 132
set pagesize 999
set head off
set feed off
SELECT phd_id,(SELECT COUNT(*) FROM q3 WHERE dvc_trx_sts_cd='PE' AND phd_id=A.phd_id) Count
FROM phd_port_info A
GROUP BY phd_id;" | \
sqlplus -silent sndappsco/barney@p01sc1 | \
while read i; do
  echo "$i"
done

Feherke.

http://rootshell.be/~feherke/

 

[lhg1]

Works great.

Could you give a few words of what it acturly does?

/Lhg

 

[lhg1]

The first line of the SQL is always empty, I've tryed to add awk 'NF' - but that does not do anything. Can I remove that line?

GROUP BY phd_id;"|awk 'NF' | \

In the line, i can do it like this, but perhaps not the smartest way.

echo "set linesize 132
set pagesize 999
set head off
set feed off
SELECT phd_id,(SELECT COUNT(*) FROM q3 WHERE dvc_trx_sts_cd='PE' AND phd_id=A.phd_id) Count
FROM phd_port_info A
GROUP BY phd_id;"|awk 'NF' | \
sqlplus -silent sndappsco/barney@p01sc1 | \
while read i; do
     
  a=`echo $i|wc|awk '{print $2}'`

  if [ $a -gt 1 ]
  then
  phd_id=`echo $i|awk '{print $1}'`
  antal=`echo $i|awk '{print $2}'`
  
  echo "The phd_id is $phd_id and the que is $antal"
  fi
done

Any ideers?

 

[p5wizard]

put the [tt]awk 'NF'[/tt] after the sqlplus maybe?

[tt]...
GROUP BY phd_id;"|\
sqlplus -silent user/pass@sid | \
[red]awk 'NF' |\[/red]
while read i; do
...[/tt]

oh and I wouldn't put userids/passwords and stuff in your posts. You might want to red-flag it and ask for that info to be deleted.


HTH,

p5wizard

 

[PHV]

You may try this:

echo "set linesize 132
set pagesize 999
set head off
set feed off
SELECT phd_id,(SELECT COUNT(*) FROM q3 WHERE dvc_trx_sts_cd='PE' AND phd_id=A.phd_id) Count
FROM phd_port_info A
GROUP BY phd_id;" | \
sqlplus -silent sndappsco/barney@p01sc1 | awk 'NF>1{
  print "The phd_id is "$1" and the que is "$2
}'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[feherke]

Hi

Do it as PHV suggested. This is only to answer your question about the empty line. Using external tools is usually slower than letting the shell to solve it.

echo "set linesize 132
set pagesize 999
set head off
set feed off
SELECT phd_id,(SELECT COUNT(*) FROM q3 WHERE dvc_trx_sts_cd='PE' AND phd_id=A.phd_id) Count
FROM phd_port_info A
GROUP BY phd_id;"| \
sqlplus -silent sndappsco/barney@p01sc1 | \
while read i; do
  [red][ "$i" ] || continue[/red]

  a=`echo $i|wc|awk '{print $2}'`

  if [ $a -gt 1 ]
  then
    phd_id=`echo $i|awk '{print $1}'`
    antal=`echo $i|awk '{print $2}'`
  
    echo "The phd_id is $phd_id and the que is $antal"
  fi
done

Feherke.

http://rootshell.be/~feherke/