Select Query Language

Because of my profession I know a bit about Linux (especially Red Hat/CentOS) and Oracle databases. When I script it usually looks like a twelve year old wrote it. I find it akin to an analogy I heard once. Life is like a box of crayons. Some people get 5 while others get 100. It's what you do with the crayons that's important. A person with 5 crayons can muster a complete piece of art that drops jaws while someone with 100 crayons struggles to color within the lines.

Well that's me when it comes to scripting. I only have 5 crayons but I'll use them in ways that most people didn't think possible. IF/THEN, you bet. That's my bread and butter. Redirecting to another file that get's a CAT and then GREPed because I can't figure out how to filter headers in my results. Hell yeah, it works!

Seriously though. I'm genuinely proud of myself. I wrote a script today that finds all partitions relative to dates within my database and then removes them. This sounds easy until you realize that our partition naming convention does not follow our adding/removing procedures. In other words we may have a partition named: Y2010_D264_S2 (Which stands for: Sept 22, 2010 for Site 2). Well the only way to drop said partitions is with a command like this:
exec PARTITION_UTILS.DROP_PARTITIONS(2,'2010264','2010264');

So when you start to script these types of things you first have to find them. (select partition_name, table_name from dba_tab_partitions order by 1;). The fucking partitions are named like this though:
Y2010_D264_S2 YOURTABLENAME

So it's kind of hard to automate the dropping of all data related partitions when you can't feed a wild card (think * or %) for anything you want. I'll be damned if this didn't just completely stump me... my solution? Funny you ask. My solution was to run that same select then redirect it to another file. Then I went ahead and did a CAT of that file while grepping for certain things and then redirected THOSE results to an SQL file that gets kicked off at the end.

I may not know formatting, I may not know programming, I may not even know how to configure TNS files. I can sure as fuck paint a pretty picture with 5 crayons though. Don't you think?

FILE 1:

> /home/mrnams/utils/partitioncat
$ORACLE_HOME/bin/sqlplus username/password<<EOF > /home/mrnams/utils/partitioncat.mmb
@test.sql
EOF

cat /home/mrnams/utils/partitioncat.mmb | grep "ALTER" > droppartitions.sql

$ORACLE_HOME/bin/sqlplus username/password @droppartitions.sql  

FILE 2:
$ORACLE_HOME/bin/sqlplus username/password<<EOF > /home/mrnams/utils/droppartitions.sql
set feedback off
set pagesize 0
SELECT 'ALTER TABLE ' || TABLE_NAME || ' DROP PARTITION ' || PARTITION_NAME || ';'
FROM DBA_TAB_PARTITIONS
WHERE TABLE_NAME IN (SELECT TABLE_NAME FROM NAMS_TABLES) AND
PARTITION_NAME != 'Y2000_D001_S2'
ORDER BY 1;