Thursday, February 7, 2013

Exclusion List Script

This may not be of interest to most of my blog readers, and I apoligize for that. However, after struggling with a good way to accomplish what I needed to for days, and now having come up with a somewhat good solution, I wanted to publish it out here so I don't lose it. No sense reinventing the wheel, right?

My problem was I needed a good way to track an exclusion list. Normally I'd dump it into a table, but this particular list needed to be used on dozens of databases. I didn't want to maintain multiple copies of the same data - that would be a nightmare. And I didn't want to create dozens of database links - I don't really like them anyway. I thought about hard coding the list in my script, but that seemed kind of ugly. And the list needs to be sql ready, which means each element needs to be enclosed in single quotes and be comma delimited. Let me tell you, bash was not crazy about the quotes. At one point, I had the list hard coded in my script, and had escaped the quotes. Not pretty. It looked something like this:
EXCEPTIONLIST="\(\'one\'\,\'two\'\,\'three\'\)"

Bare in mind the end result for the sql query just needs to be:
('one','two','three')

Option one. I created a table in my central database. I then queried it one time, put it in a variable, then used that for the queries to all the other databases. The bash code looks like this:
EXCLUDED_USERS=`sqlplus -s " / as sysdba"  <
set pages 9999
set lines 132
set trim on
set trims on
set echo off
set recsep off
set head off
set feedback off
set newpage none
select username from owner.table_name;
exit;
EOF`

echo $EXCLUDED_USERS
# Init the variable, put on the left paren.
DELIMITED_LIST=`echo -ne '('`
for USER in $EXCLUDED_USERS
do
# reconstruct the list, but add in the commas and single quotes.
DELIMITED_LIST=`echo $DELIMITED_LIST"'"$USER"',"`
done
# Remove the last comma we really did not need.
DELIMITED_LIST=$(echo ${DELIMITED_LIST%\,})
# Add in the right paren.
DELIMITED_LIST=`echo -ne $DELIMITED_LIST")"`

Option two. Just for grins, I wanted to see if it would work with a space delimited variable list in the script. It looks like this. I ripped out the actual sensitive data.....

# Exclude listing. Put into multiple vars for readability.
# If you add a line, make sure you add it to the line that puts them all together.
# One space between entries please.
EXCLUDED_USERS1="blah blah2"
EXCLUDED_USERS2="blah3 blah4"
EXCLUDED_USERS=$EXCLUDED_USERS1$EXCLUDED_USERS2$EXCLUDED_USERS3$EXCLUDED_USERS4$EXCLUDED_USERS5

# Make sure we are set to a space delimiter
IFS=" "
# Init the variable, put on the left paren.
DELIMITED_LIST=`echo -ne '('`
for USER in $EXCLUDED_USERS
do
# reconstruct the list, but add in the commas and single quotes.
DELIMITED_LIST=`echo $DELIMITED_LIST"'"$USER"',"`
done
# Remove the last comma we really did not need.
DELIMITED_LIST=$(echo ${DELIMITED_LIST%\,})
# Add in the right paren.
DELIMITED_LIST=`echo -ne $DELIMITED_LIST")"`
EXCLUDED_USERS=$DELIMITED_LIST

The basic principle is the same either way. Capture the list into an array, then walk through the array, putting the quotes, commas and parentheses back in. Then it's good to go for the actual query:

USERLIST=`sqlplus -s "/as sysdba"   <
set pages 9999
set lines 132
set trim on
set trims on
set echo off
set recsep off
set head off
set feedback off
set newpage none
select username
from dba_users
where account_status = 'OPEN'
and username not in
(select distinct owner
from dba_objects)
and username not in $EXCLUDED_USERS
order by username
/
exit
EOF`

And that is more than you ever wanted to know about working in IT.......

No comments:

Post a Comment