how can I insert unix-data into an sql script?

[gertvangaever]

I have a sql-script.
I would like to put 'df-k' information into tables in the database, so one way or another I have to copy the unix-output of a command to sql-variables!

anyone that can help me?

Tnx!
Gert

 

[sem]

You may generate entire sql script from shell script and run it by sql*plus. Another way is to pass the values you need to sql script via lexical variables.

 

[gertvangaever]

Could you give some more information on how to do this?

Tnx!

 

[neilsie]

Why not tabluate the output of the df command to a text file and then use an SP to load into your appropriate table (effectivly SQL loader). Even better, kick this off from withn Oracle by using External procedures.

N.

 

[evatype2]

I use a simple, but reliable method to do this sort of thing. In your script, do what you need to format the df output ("cut" fields, etc) into a file. I then call sqlldr (sql loader doc. is in utility manual with import/export) to load the formatted file. You will need a .ctl file. This method is good for a process that is repeated on a regular schedule.

 

[DBAwhosaysNIE]

from sqlplus I'd
!df -k > /tmp/df-k2sql.lst (or whatever switches)
then use utl_file.get_line to read it & insert/update

from shell you could
df -k | tail +2 | while read FILESYSTEM
do
filesystem=`echo $FILESYSTEM | awk '{print $1}'`
KBblocks=`echo $FILESYSTEM | awk '{print $2}'`
used=`echo $FILESYSTEM | awk '{print $3}'`
available=`echo $FILESYSTEM | awk '{print $4}'`
pctused=`echo $FILESYSTEM | awk '{print $5}' | sed s/%//`
mountpoint=`echo $FILESYSTEM | awk '{print $6}'`
echo "insert into sometable values(\"$filesystem\",$KBblocks,$used,$available,$pctused,\"$mountpoint\""
done

but that's kind of ugly

simplest & most elegant way would be to use perl & DBI