Passing OS Shell Parameters to Oracle SQL Scripts

Ever had the need to pass an OS level parameter to an Oracle SQL script? I have and after some work I was able to make it to happen.

The scenario here is to gather the total amount of diskspace in the ASM diskgroups and then load it into a database. The biggest challenge is that ASM is not easy to connect to remotely…even with the (UR=A) added to the tnsnames.ora string. Especially when you have 15 environments that you’re trying to report on in one place.

This is how I was able to facilitate this need. This is Oracle 11gR2 hosted on AIX in a two node RAC.

The first challenge is to obtain the information from ASM on the database server. This is only needed on one node of the cluster.

. /home/oracle/.profile
export ORACLE_HOME=$GRID_HOME
export ORACLE_SID=+ASM1

sqlplus -s "/ as sysdba" < /tmp/asm_total_space.log
SET HEADING   off
SELECT to_char(sum(total_mb))
FROM v\$asm_diskgroup
ORDER BY name
/
exit;
EOF

Now that I’ve gathered the information I need from ASM I will need to clean it up some, as the process creates an extra row at the top of the output file for some reason.

cat /tmp/asm_total_space.log | head -2 | tail -1 > /tmp/asm_total_space_clean.log

Now that the OS file is clean I’ll go ahead and read the value from within it and pass the value into a SQL script to load into Oracle via the $ASM_SIZE parameter.

. /home/oracle/.profile
export ASM_SIZE=`cat /exports/ASM/asm_total_space_clean.log`

sqlplus username/password@asm_total_space.sql $ASM_SIZE

Here are the details of the sql script being called. Note two items here.

  1. The substitution variable &1 is being used to pass the OS parameter $ASM_SIZE into.
  2. The datatype of size_mb is number and not a char.
set verify off
delete from schema.table where environment = '11g Production';
commit;
insert into schema.table (size_mb, environment) values ('&1','11g Production');
commit;
exit;

**Note that some of the sensitive information has been replaced with simple things such as table, schema, username, and password. This blog post is written with the assumption that you can adapt this to your needs.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s