Korn Shell and Oracle

Hi Guys,

I haven't worked on oracle much but I have a situation where I have to do bdf in all the servers and insert that information into oracle table. I have already created table which have 7 columns, I can insert manually but I dont know how to insert that using Korn shell.

SERVER_ID NOT NULL NUMBER(6)
FS_LOCAL_NAME NOT NULL VARCHAR2(200)
LOCAL_MOUNT NOT NULL CHAR(1)
TOTAL_SPACE NOT NULL NUMBER(10)
USED_SPACE NUMBER(10)
SPACE_AVAILABLE NUMBER(10)
PERCENTAGE_USED NUMBER(3,2)

This is just a example I have done manually and its inserting data
#!/bin/ksh
sqlplus pareshan/ac6e94aac5b1b979902c0b0b1f42621761c@let01a<<EOF
insert into unix_servers values (1,'superior','Y',89885485,858757,4657575,2);
EXIT
EOF

But the thing I need is I have run df command which gives output like this

Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol4 1048576 298504 744256 29% /
/dev/vg00/lvol1 1014648 65224 847952 7% /stand
/dev/vg00/lvol8 4194304 1451112 2722456 35% /var
/dev/vg3psw/lvol12 3145728 1221208 1804256 40% /var/mqm
/dev/vg00/lvol10 4194304 3749008 441888 89% /var/adm/sw
/dev/vg00/lvol9 4194304 16744 4144936 0% /var/adm/crash
/dev/vg00/lvol7 5242880 1871528 3345048 36% /usr
/dev/vg3psw/lvol1 4194304 3867784 323984 92% /usr/local
/dev/vg3psw/lvol7 10338304 8815105 1428012 86% /usr/local/vertex
/dev/vg3psw/lvol2 12738560 8080720 4622144 64% /usr/local/oracle
/dev/vg3psw/lvol9 5120000 1286387 3594077 26% /usr/local/g1_3.2_SE
/dev/vghome_old/lvol1

and have to insert those data in the respective column in the oracle table.

Plz Help,

Use sqlldr: redirect bdf (not df) output to a file, parse the file and feed it to sqlldr.

Hi Jim, first of all thanks
I have done something like this

this is controlfile
control_file

LOAD DATA
INFILE '/home/gc1488/FST/input'
INTO TABLE unix_servers
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
(SERVER_ID NUMBER(6),
SERVER_NAME VARCHAR2(20),
MARKET CHAR(3),
SERVER_TYPE VARCHAR2(15),
PROD_IND CHAR(1),
OWNER VARCHAR2(20))

I already have input file in that location. One thing the table im going to use should already exist or doesnt matter because I have to insert into the table which is already there. but in this example im trying to use the table which doesnt exist because I think sqlldr will create table if doesnt exists right?

But I got one error, it says

SQL*Loader-350: Syntax error at line 5.
Expecting "," or ")", found "NUMBER".
(SERVER_ID NUMBER(6),
^

Also could you tell me how can i use the existing table here.

thank you very much

Your table would need to exist before attempting to load it. SQLLoader doesn't do this. You would create this in SQLPlus, etc.

Otherwise, using a pipe-delimited file for example:

Control File:

load data infile '/opt/axs/dbgora/ctron/data/useraudt/userupld' 
replace into table 
      CTRONRPT.TUSERFILE3 
fields terminated by "|" optionally enclosed by '"' 
     (USER_ID                    , 
      USER_NAME                  , 
      OPEN_FILENAME              , 
      OPEN_LIBRARY               , 
      OPEN_VOLUME                , 
      WO_INTERFACE               , 
      OPERATOR_LEVEL             , 
      SECURITY_LEVEL             , 
      PRINT_CLASS                , 
      PRINT_MODE                 , 
      PRINTER_NUM                , 
      AD_COMPANY_SW              , 
      AD_COMPANY_0               , 
      AD_COMPANY_1               , 
      AD_COMPANY_2               , 
      AD_COMPANY_3               , 
      AD_COMPANY_4               , 
      AD_COMPANY_5               , 
      AD_COMPANY_6               , 
      AD_COMPANY_7               , 
      AD_COMPANY_8               , 
      AD_COMPANY_9               , 
      CORPORATE_SW               , 
      PAGE_SIZE_ADDER            , 
      LOGOFF_TIME_INTERVAL_MIN   , 
      GROUP_CODE                 , 
      PASSWORD                   , 
      PRINTER_TOP_MARGIN         , 
      GUEST_ID                   , 
      GUEST_PASSWORD             , 
      REFRESH_CACHE_SW           , 
      USER_DEFINED_AREA          ) 

Script snippet:

#  Run SQL*Loader utility to replace ctronrpt.tuserfile3 table... 
   sqlldr                   \
      userid=${my_userparm} \
      control=${my_control} \
      data=${my_datfile}    \
      silent=${my_silence}  \
      log=${my_logfile}     \
      bad=${my_badfile}                                      >>${MY_LOG} 2>&1 

#  Confirm results via return code... 
   retcode=$(echo ${?} ) 

   case ${retcode} in 

      0) 
         print "\n\t\tSQL*Loader execution successful " 
         ;; 

      1) 
         print "\n\t\tSQL*Loader execution exited with EX_FAIL, see ${my_logfile} " 
         ;; 

      2) 
         print "\n\t\tSQL*Loader exectuion exited with EX_WARN, see ${my_logfile} " 
         ;; 

      3) 
         print "\n\t\tSQL*Loader execution encountered a fatal error " 
         ;; 

      *) 
         #  Huh...? 
         print "\n ===\n\n ${my_name}: $(date) "             |tee -a ${MY_LOG} 
         print "\n\t${retcode} not valid recognized..."      |tee -a ${MY_LOG} 
         print "\n\t                                  "      |tee -a ${MY_LOG} 
         print "\n\tReturned:  ${retcode} ${*} \n"           |tee -a ${MY_LOG} 
         logger_heads ; return 2 
         ;; 

   esac                                                      >>${MY_LOG} 2>&1 

Thank you very much, I really appreciate and you are right I can do using that and I have done also but here is the twist.

my bdf command gives me a bunch of output I think if I print it here it will be a long list but I am sure you are aware of that. It includes local file sytem + all the mounted file systems and of course I cant put that in one row of a table so I need to calcuate that using script and find out ( total, used, free and percentage used) and only that information I can enter in the table.
I tried to write korn shell script for that but Its not that easy but I am still trying.

Anybody have some script already or have some idea about it.

I hope I made it clear this time. if any queries please let me know. I will appreciate your help
thaks alot

Hi Pareshan,

Try this approach.
This will generate a sql script from df command's output and then insert the data.

# --------------------------------------
server=`hostname` # Get server name in a shell veriable
sqlfile=$server.sql

df | awk -v serv=$server '
{print "insert into table1 values (~" serv "~,~" $1 "~,~" $6 "~," $2 "," $3 "," $4 "," $5 " );" }
' | sed -e "s/~/'/g" -e 's/%//' > $sqlfile

# In above command awk will generate insert statement from df's output
# Then first sed will replace all ~ with single quote
# next sed will take out the percentage sigh from the insert
# Now insert the data

sqlplus -s /nolog <<EOSQL
connect user/pass@sid
whenever sqlerror exit 99 -- If there is error, this sqlplus will exit with non-zero status
@$sqlfile
commit;
exit
EOSQL
if [[ $? -ne 0 ]]
then
echo "Error while running file $sqlfile .....Run ... panic .... investigate"
fi
# ------------------------------------------------

I do not have a Unix session at hand, so I haven't run this script and tested, but this will work. I have done this before.

Enjoy...

Hi everyone, I really appreciate your replies and now I am able to do sqlloader part.

mynameisrahu, thats not working for me as i have to do that for bdf output,

Now my problem is just i want to format the bdf output with the values separated by space and put that into text file so that i can fed that into oracle table.

As I mentioned before my bdf will give some unformatted output like below which contains random spaces and you can see some of the values goes to second line also, i want the values spearated by space in one line plz help,

Filesystem kbytes used avail %used Mounted on
/dev/vg00/lvol4 1048576 314096 728792 30% /
/dev/vg00/lvol1 1014648 65224 847952 7% /stand
/dev/vg00/lvol8 4194304 1462872 2711512 35% /var
/dev/vg3psw/lvol12 3145728 1221224 1804241 40% /var/mqm
/dev/vg00/lvol10 4194304 3749008 441888 89% /var/adm/sw
/dev/vg00/lvol9 4194304 16744 4144936 0% /var/adm/crash
/dev/vg00/lvol7 5242880 1871472 3345104 36% /usr
/dev/vg3psw/lvol1 4194304 3883768 308128 93% /usr/local
/dev/vg3psw/lvol7 10338304 8815105 1428012 86% /usr/local/vertex
/dev/vg3psw/lvol2 12738560 8080712 4622152 64% /usr/local/oracle
/dev/vg3psw/lvol9 5120000 1286387 3594077 26% /usr/local/g1_3.2_SE
/dev/vghome_old/lvol1
23552000 10375127 12353732 46% /usr/local/g1
/dev/vg3psw/lvol3 2097152 137104 1944800 7% /usr/local/dazel
/dev/vg3psw/lvol8 1228800 302520 919104 25% /usr/local/ccmi
/dev/vg3psw/lvol10 4096000 3714067 358124 91% /usr/local/TWS
/dev/vgapp/lvol5 2097152 16192 2072336 1% /tuxhome
/dev/vgapp02/lvol1 153616384 100673926 49663667 67% /tuxappl
/dev/vg00/lvol6 1540096 43712 1489168 3% /tmp
/dev/vgapp-io/lvol1
199155712 153290696 45685856 77% /tlgvar
/dev/vgapp/lvol4 20971520 2912 20804800 0% /tlg
/dev/vgapp/lvol2 65536000 1138272 63894704 2% /reports
/dev/vgmafg/lvol1 52396032 6968 51982472 0% /pvdev
/dev/vgapp/lvol6 204800 10712 192632 5% /p/sbms
/dev/vgapp/lvol7 102498304 48411704 53675288 47% /p/sbms/mps
/dev/vgdbcommon/lvol2
167755776 139972872 27572816 84% /oraexp
/dev/vg00/lvol5 4194304 3641336 548688 87% /opt
/dev/vg3psw/lvol11 3145728 527068 2455009 18% /opt/mqm
/dev/vg3psw/lvol5 1048576 388952 654520 37% /opt/iona
/dev/vg3psw/lvol13 1048576 1357 981775 0% /opt/app/d1mqmm1
/dev/vg3psw/lvol6 2097152 654456 1433328 31% /opt/app/bmc
/dev/vgapp/lvol3 2097152 24344 2056680 1% /opcode
/dev/vgapp/lvol9 25624576 24107916 1494984 94% /mps
/dev/vghome_old/lvol5
5144576 185360 4649319 4% /lsms_tool
/dev/vgapp-io/lvol2
2097152 5200 2075808 0% /logs
/dev/vgdbcommon/lvol1
18432000 1510104 16794280 8% /logs/ORACLE
/dev/vghome/lvol1 62898176 58195552 4702624 93% /home
/dev/vgapp/lvol8 524288 290587 219125 57% /emc
/dev/vgd2bl12d_2/lvol3
20512768 6129 19224982 0% /csmtier1/logs
/dev/vgapp/lvol10 1048576 6334 977155 1% /csmscripts
/dev/vghome_old/lvol6
51707904 3864 51300112 0% /ora_D2BL12B_4
/dev/vgd2bl12b_2/lvol1
52396032 4155472 47863744 8% /ora_D2BL12B_3
/dev/vgd2bl12d_2/lvol2
34209792 29055568 5114024 85% /ora_D2BL42D_2
/dev/vghome_old/lvol7
62898176 61460312 1426640 98% /ora_D2BL42C_5
/dev/vghome_old/lvol4
30720000 3216 30476816 0% /ora_D2BL12A_3
/dev/vghome_old/lvol3
36126720 9960 33859469 0% /ora_D2BL12B_2
/dev/vghome_old/lvol2
20480000 4098928 16253104 20% /ora_D2BL42C_4
/dev/vgd2bl42a_2/lvol2
26214400 25687784 522512 98% /ora_D2BL42C_3
/dev/vgd2bl42e/lvol1
52412416 23481928 28704536 45% /ora_D2BL42E
/dev/vgd2bl42d/lvol1
20955136 10502664 10370880 50% /ora_D2BL42D
/dev/vgd2bl42c_2/lvol1
41926656 39955584 1955680 95% /ora_D2BL42C_2
/dev/vgd2bl42c/lvol1
20955136 18765072 2172960 90% /ora_D2BL42C
/dev/vgd2bl42b/lvol1
26198016 4115056 21910448 16% /ora_D2BL42B
/dev/vgd2bl42a_2/lvol1
15728640 2784 15603008 0% /ora_D2BL42A_2
/dev/vgd2bl42a/lvol1
20955136 19273216 1668784 92% /ora_D2BL42A
/dev/vgd2bl42e/lvol2
20955136 2920 20788536 0% /ora_D2BL42E_2
/dev/vgd2bl43c/lvol1
15712256 12684776 3003896 81% /ora_D2BL43C
/dev/vgd2bl43b/lvol2
14680064 14338784 338624 98% /ora_D2BL43B_2
/dev/vgd2bl43b/lvol1
47169536 46710616 455400 99% /ora_D2BL43B
/dev/vgd2bl43a/lvol1
12566528 11622368 936848 93% /ora_D2BL43A
/dev/vgd2bl12d_2/lvol1
30736384 17104 30479296 0% /ora_D2BL12D_2
/dev/vgd2bl12d/lvol1
20963328 16697736 4232272 80% /ora_D2BL12D
/dev/vgapp/lvol11 51298304 3864 50893712 0% /ora_D2BL12C_4
/dev/vgd2bl12c_2/lvol2
41910272 3560 41579328 0% /ora_D2BL12C_3
/dev/vgd2bl12c_2/lvol1
41910272 3560 41579328 0% /ora_D2BL12C_2
/dev/vgd2bl12c/lvol1
131047424 124934576 6065160 95% /ora_D2BL12C
/dev/vgd2bl12b/lvol1
146743296 106296952 40130408 73% /ora_D2BL12B
/dev/vgd2bl12a_2/lvol2
52396032 4201448 47818072 8% /ora_D2BL12A_4
/dev/vgd2bl12a_2/lvol1
52396032 3880 51982848 0% /ora_D2BL12A_2
/dev/vgd2bl12a/lvol1
78618624 76804888 1799640 98% /ora_D2BL12A
/dev/vg27/lvol3 204865536 197661672 7147648 97% /ora_D2BL42A_3
/dev/vg27/lvol2 307232768 26120 304806672 0% /ora_D2BL42B_2
/dev/vg27/lvol1 307232768 287320072 19757192 94% /ora_D2BL43A_2
dhtqa2:/nvqa_98 83886080 69989880 13789704 84% /nvqa_98
/dev/vg27/lvol4 20578304 9825080 10669224 48% /ora_fbf_1
dhdtlgcc:/nvdev5 51249152 43721568 7057120 86% /nvdev_98a
dhdtlgcc:/nvdev5 51249152 43721568 7057120 86% /nvdev_97a
dhdtlgcc:/scdev2 2097152 477720 1518240 24% /scdev_30
dhdtlgcc:/scdev5 2097152 478104 1517880 24% /scdev_30a
dhdtlgcc:/scdev5 2097152 478104 1517880 24% /scdev_29
dhdtlgcc:/fbfdev2 1048576 332856 671776 33% /fbfdev_98
dhdtlgcc:/fbfdev5 1048576 338832 665408 34% /fbfdev_98a
dhdtlgcc:/fbfdev5 1048576 338832 665408 34% /fbfdev_97a
dhdtlgcc:/fbfdev1 1048576 320664 683960 32% /fbfdev_97
dhdtlgcc:/scdev4 2097152 312832 1673720 16% /scdev_28
dhdtlgcc:/nvdev4 68157440 39546928 26825408 60% /nvdev_96
dhdtlgcc:/nvdev5 51249152 43721568 7057120 86% /nvdev_96a
dhdtlgcc:/nvdev3 77463552 44335616 31057448 59% /nvdev_95
dhdtlgcc:/qproj4 82706432 66679968 15915696 81% /qproj4
dhdtlgcc:/qproj3 81920000 58942336 21561512 73% /qproj3
dhdtlgcc:/qproj2 81920000 67528304 13497008 83% /qproj2
dhdtlgcc:/qproj1 82706432 50596336 30184632 63% /qproj1
dhtqa2:/qa_data 15728640 7164848 8044104 47% /qa_data
dhtqa2:/fbfqa_98 20971520 1416464 19403080 7% /fbfqa_98
dhtqa2:/scqa_30 3145728 1561032 1486552 51% /scqa_30
dhtqa2:/scqa_26 2097152 633680 1372048 32% /scqa_26
dhtqa2:/nvqa_97 82968576 67342968 14649040 82% /nvqa_97
dhtqa2:/nvqa_96 102760448 76883520 24263072 76% /nvqa_96
dhtqa2:/nvqa_93 50634752 39384216 10547440 79% /nvqa_93
dhdtlgcc:/fbfdev4 1048576 311528 691768 31% /fbfdev_96
dhdtlgcc:/fbfdev2 1048576 332856 671776 33% /fbfdev_94
dhdtlgcc:/fbfdev1 1048576 320664 683960 32% /fbfdev1
dhdtlgcc:/scdev5 2097152 478104 1517880 24% /scdev1
dhtqa2:/fbfqa_97 30932992 1358472 27726128 5% /fbfqa_97
dhtqa2:/scqa_29 26214400 972984 25045088 4% /scqa_29
wishbone:/opcode 18874368 1379552 17365704 7% /opcode_qa
dhttrn1:/ECA 2097152 265848 1826960 13% /ECA
dhdtlgcc:/home/cc 40960000 37415632 3322888 92% /home/cc
dhdtlgcc:/nvdev1 49807360 44079800 5369648 89% /nvdev_97
dhdtlgcc:/nvdev2 51249152 42715696 8000136 84% /nvdev_98
dhdtlgcc:/fbfdev5 1048576 338832 665408 34% /fbfdev_71a
dhdtlgcc:/fbfdev3 1048576 339872 664432 34% /fbfdev_71
dhdtlgcc:/scdev5 2097152 478104 1517880 24% /scdev_31a
dhdtlgcc:/scdev3 2097152 441920 1551800 22% /scdev_31
dhdtlgcc:/nvdev5 51249152 43721568 7057120 86% /nvdev_71a
dhdtlgcc:/nvdev3 77463552 44335616 31057448 59% /nvdev_71
dhdtlgcc:/fbfdev3 1048576 339872 664432 34% /fbfdev_95
dhdtlgcc:/scdev3 2097152 441920 1551800 22% /scdev_27
dhqtlgmm:/tlg 325058560 260120792 60892864 81% /ndev_988
dhtqa2:/nvqa_71 82968576 67342968 14649040 82% /nvqa_71
dhtqa2:/scqa_31 26214400 972984 25045088 4% /scqa_31
dhtqa2:/fbfqa_71 30932992 1358472 27726128 5% /fbfqa_71
dhtqa1:/qadmin 1048576 59888 981376 6% /qadmin

I use perl also to format but its not working for bdf properly and same script works for every other file which have random spaces or format.

plz help guys
thanks in advance

pareshan:

your bdf output is trying to squeeze itself down to fit your teminal width. try piping it through 'grep ^ ' such as:

bdf |grep ^ 

otherwise, if there's additional spaces causing you problems, you can also pipe the above output through tr to squeeze out spaces too:

bdf |grep ^ |tr -s [:space:] 

lastly, if you'd really want to get wild, you pipe all of the above into Rahul's earlier awk script too (although it might be somewhat slower, and less resilient for errors, than sqlldr if there's too many records). sqlldr is nice in that it ensures at least a basic transaction commit level...and since it creates a bad file, etc, it's helpful when the script might be supported by additional folks:

bdf |grep ^ |tr -s [:space:] |awk -v serv=$server '
{print "insert into table1 values (~" serv "~,~" $1 "~,~" $6 "~," $2 "," $3 "," $4 "," $5 " );" }
' | sed -e "s/~/'/g" -e 's/%//' > $sqlfile 

Thanks alot but nothign really working out here.

I think I can do rest if i get solution of just this one as I mentioned below

while im doing bdf if the output is in two lines then i want to shift that in one line rest removing spaces and all i can do it using perl. Just you can see some of them are in one line and some of then are in two lines just i need help with those whose result came in two lines. something like if the value is in two lines then shift it to one line and im not able to implement it thats my problem. can be in ksh or perl i dont care.

If I can change all the outputs which are like this
/dev/vghome_old/lvol1
23552000 10375127 12353732 46% /usr/local/g1/dev/vgapp02/lvol1

into this kind of outputs like the other ones
/dev/vgapp/lvol9 25624576 23454180 2138564 92% /mps
then rest that oracle and deleting extra spaces I can handle.. just I need help in this one.

bdf_output

/dev/vg3psw/lvol9 5120000 1286387 3594077 26% /usr/local/g1_3.2_SE
/dev/vghome_old/lvol1
23552000 10375127 12353732 46% /usr/local/g1/dev/vgapp02/lvol1 153616384 100802161 49543296 67% /tuxappl
/dev/vg00/lvol6 1540096 44424 1488456 3% /tmp
/dev/vgapp-io/lvol1
199155712 153617264 45361864 77% /tlgvar
/dev/vgapp/lvol3 2097152 24344 2056680 1% /opcode
/dev/vgapp/lvol9 25624576 23454180 2138564 92% /mps
/dev/vghome_old/lvol5
5144576 185360 4649319 4% /lsms_tool
/dev/vgapp-io/lvol2
2097152 5208 2075800 0% /logs
/dev/vgdbcommon/lvol1
18432000 1517936 16786512 8% /logs/ORACLE
/dev/vghome/lvol1 62898176 54860264 8037912 87% /home
/dev/vgapp/lvol8 524288 290587 219125 57% /emc
/dev/vgd2bl12d_2/lvol3
20512768 6129 19224982 0% /csmtier1/logs
/dev/vgapp/lvol10 1048576 6334 977155 1% /csmscripts
/dev/vghome_old/lvol6
51707904 3864 51300112 0% /ora_D2BL12B_4

/dev/vg27/lvol3 204865536 197661672 7147648 97% /ora_D2BL42A_3
/dev/vg27/lvol2 307232768 26120 304806672 0% /ora_D2BL42B_2
/dev/vg27/lvol1 307232768 287320072 19757192 94% /ora_D2BL43A_2
/dev/vg27/lvol4 20578304 9825080 10669224 48% /ora_fbf_1

Just you can see some of them are in one line and some of then are in two lines just i need help with those whose result came in two lines. something like if the value is in two lines then shift it to one line and im not able to implement it thats my problem. can be in ksh or perl i dont care

thanks alot
help plz

I found the solution now and I solved it and correct code is

#!//opt/perl/bin/perl
use strict;
use warnings;
my ($filesystem);
foreach (qx(bdf)) { ##executing bdf command here
next if/^Filesystem/; ##Skip the header line
if(!/%/) { ##remember on next line if no percentage on this line
chomp; ##clean up the new line
$filesystem = $; ## remember
next;
}
$ = $filesystem . $_ if /^\s/; ## add filesystem for wrapped lines
#next if /^\w+:/; #skip NFS mount with ":" in first word ##To skip the NFS mount
#chomp;
s/% / /; ## strip percentage sign
s/\s+/ /g; ## replace all tabs and spaces with just 1 space
my ($fs, $kbytes, $used, $avail, $pct_used, $mount) = split; ## now have each column
in a variable
## do anything you like with the columns (like preparing an oracle insert)
##print $_, "\n" ; # just to prove new glued line
##print qq(fs=$fs used=$pct_used \n);
print qq($fs $kbytes $used $avail $pct_used $mount\n);
}

this will help to format the bdf and even can remove NFS mount file systems if you comment that out.

Now I want to insert value into oracle table which has format

Name Null? Type
----------------------------------------- -------- ----------------------------
SERVER_ID NOT NULL NUMBER(6)
FS_LOCAL_NAME NOT NULL VARCHAR2(200)
LOCAL_MOUNT NOT NULL CHAR(1)
TOTAL_SPACE NOT NULL NUMBER(10)
USED_SPACE NUMBER(10)
SPACE_AVAILABLE NUMBER(10)
PERCENTAGE_USED NUMBER(3,2)

Here I have one problem because all of the required fields are in the above bdf output but SERVER_ID.

Could you guys help how can i insert into that table using the above code because the script is in perl but anything is ok whether perl or korn shell if it works

thanks