unix ksh retrieve oracle query result

Question

Hi guys.

I'm working on a small piece of ksh code for a simple task. I need to retrieve about 14 millions lines from a table and then generate a xml file using this informations. I don't have any treatement on the information, only some "IF". The problem is that for writing the file it takes about 30 minutes, and it is not acceptable for me.

This is a piece o code:

......
query="select field1||','||field2||' from table1"
ctl_data=`sqlplus -L -s $ORA_CONNECT @$REQUEST`

for variable in  ${ctl_data}
do

var1=echo ${variable} | awk -F, '{ print $1 }'

var2=echo ${variable} | awk -F, '{ print $2 }'

            ....... write into the file ......

done

For speed up the things I'm writing only 30 lines into the file, so more stuff on one line, so I have only 30 acces to the file. It is still long, so is not the writing but looping through the results.

Anyone have a ideea about how to improve it ?

Thanks.

C.C.

Answer 1

you can lessen the amount of calls to awk using just one instance. eg

query="select codtit||','||crsspt||' from table1"
.....
sqlplus -L -s $ORA_CONNECT @$REQUEST | awk -F"," 'BEGIN{
   print "xml headers here..."
}
{
   # generate xml here..
   print "<tag1>variable 1 is "$1"</tag1>"
   print "<tag2>variable 2 is "$2" and so on..</tag2>"
   if ( some condition here is true ){
      print "do something here"
   }
}'  

redirect the above to a new file as necessary using > or >>

Answer 2

I doubt, that this is the most efficient way of dumping data to an xml file. You could try to use groovy for such a task. Take a look at the groovy cookbook at -> http://groovy.codehaus.org/Convert+SQL+Result+To+XML

Answer 3

Rather than pass from oracle to ksh could you do it all in oracle? You can use the following to format your output as xml.

select xmlgen.getxml('select field1,field2 from table1') from dual;

Answer 4

You may be able to eliminate the calls to awk:

saveIFS="$IFS"
IFS=,
array=($variable)
IFS="$saveIFS"
var1=${array[0]} # or just use the array's elements in place of var1 and var2
var2=${array[1]}