topic Re: Shell Script to database in Operating System - HP-UX

Shell Script to database

Ricardo Bassoi — Thu, 03 Jul 2003 16:54:22 GMT

Hi All,

When I do a select in my data base I got the result below, but I??d to use the fields in order to compare.
The problem is that I have a lot of blank spaces, I??m using set colsep ";" in order to have the columns separated.

1 - How can I use the fieds, extract the data ?
2- How to organize the query result ? Remove the blanks and well formated ?

SQL> select ascii(GRP_BEP),SNPUPDT,DYNUPDT,A_OD_REF,A_PRV_ID,
A_SUB_ID,CMMAD,CMM,CMMNB,CMMNM,CMMST,CMMZIP,
CURCRED,CUS_NBR,CUSTID,DATEBLO,DATEFIN,ascii(F_TARP),ascii(FRST_ACC),LAST_CCA,MAX_CRED,MN15,NB_CCA,RI,
ASCII(STA),TASERQU,USR_PIN from custlc where cus_nbr is not null and ascii(state)=0;
0; 180545248;01-JUL-03; 0;0 ;
00047234580344 ;

CE ; ; 3124800;852697013 ;47382 ;17/09/2003
19/07/2003; 0; 0;25/06/2003; 3127600;852697013
1; 47382; ##########; 1;

0; 180699629;03-JUL-03; 0;0 ;
00043461980315 ;

SILVIA HELENA CORREA RODRIGUES
CE ; ; 691600;854693827 ;12976 ;05/10/2003
06/08/2003; 0; 0;01/07/2003; 2500000;854693827
3; 12976; ##########; 1;

0; 179276618;09-JUN-03; 0;0 ;
BRUNO LEONARDO - CGR TESTE ;

Re: Shell Script to database

Tim D Fulford — Thu, 03 Jul 2003 17:29:19 GMT

Hi, I'm not 100% sure I understand your question. The best way to do this is to get the perl DB add on for your database.

BUT

if you want to seperate out the fields

awk -F";" '{print $1}'

The above will print the first column using ; as the delimeter.

If you want to further format the answers (from within a script) use printf, either within or outside awk.

e.g.

unload to "output.txt" select cust_name, age, height, bank_balance from table delieter ";";

and you can use a script (one-liner) to print out thses results like so:

awk -F ";" '{printf"%12s %4d %4d %12.2f\n", $1, $2, $3, $4}' output.txt

Regards

Tim

Re: Shell Script to database

A. Clay Stephenson — Thu, 03 Jul 2003 17:33:36 GMT

I suspect tht the reason you have received no help (or from your Perl question either) is that you seem unwilling to at least make an attempt at a script.

Awk is probably a little easier to grasp/use so here is a START:

Create a little awk script, my.awk
{
if (length($0) > 0)
{
n = split($0,array,";")
printf("%s %5s %-8.8s\n",array[1],array[2],array[3])
}
}

This will print the first three fields (separataed by semicolons and format them as strings)

Now feed the output of your SQL to awk,

sqlplus .... | awk -f my.awk

OR

awk -f my.awk < spool.out