Oracle Flashback

So, I overheard a conversation this week and discovered a really interesting and potentially useful feature called Oracle Flashback. Depending on how your DBA's have setup the database you may or may not be able to hit the ground running with this feature. Let's say we were updating or deleting records in our table and did something we really didn't want to do, such as update or delete the wrong record. Depending on the complexity of your database, you may be able to replace or manually undo the mistake but in an OLTP system more than likely you are pretty much hosed. I've done it. Many have done it. It's often viewed as a badge of courage. You learn to pay closer attention. 

But, let's say you've done something by accident and committed before you noticed your error and you really need to recreate the data the way it was prior to your little whoopsie. Well, in comes Oracle like a messiah - no, a superhero. It saves the day with Oracle Flashback. There is a system package called DBMS_FLASHBACK that you would need execute on from your DBA and they would have to have setup the UNDO_RETENTION and UNDO_MANAGEMENT properties accordingly ahead of time. If not, you are going to have a bad time.

Anyway, here's the scenario: you get tasked with deleting a record from a table called RECIPE. You tried grandma's recipe for potato salad and it tastes like ass and cat food. You want to get rid of that motherfucker before someone ends up dead. In your haste, you forget to qualify your delete statement and up deleting the whole table and you committed. Oh noes!?!?! What do?!?!?! Well, you would write something like:

CREATE TABLE ORACLE_SAVE_MY_ASS_PLS AS 

    SELECT * FROM (

        SELECT * FROM RECIPE

     ) 

     AS OF TIMESTAMP SYSDATE - 0.125;

This will create a table that is a copy of recipe as of 3 hours ago. If your UNDO_RETENTION is set for a day or more, you might be able to fix something you fucked up yesterday. But if you  need to fix something you just did a few moments ago and have made other changes previous to that that were legit, you'd have to alter the number you subtract from SYSDATE to zero in on your change. As a reminder, SYSDATE is the function that returns the time stamp from the database server for when it is run. You can subtract or add a number to SYSDATE to go back or forward in time. Now, obviously for our current purposes we cannot go forward in time, that doesn't make sense. But, often systems will add 30, 60 or 90 days to SYSDATE to set a reminder in the database or to control some future functionality, like password expiration.

So far, I have yet to use this little feature to save my bacon (mmm, bacon!) but I can really see it's usefulness.  A caveat here would still be TRUNCATE. This will not work if you TRUNCATE the table. TRUNCATE does not record the action in the UNDO logs and you will get dry anal raped by TRUNCATE. Use this wisely. Be cautious and use sparingly. You best know what you are about son.

I will leave you hear. I've slacked off a bit lately on posting and I'm trying to get back on the proverbial whore, err, horse or horse-faced whore. Whatever. I need to post more frequently is what I'm trying to say. And I have the sudden urge to watch Sex and the City with the sound off and my pants down. Ah, the Chris Noth is a handsome motherfucker.

Finally approaching warp speed with Unit Tests or More about this DBDeveloper's past than you probably care to know

I haven't posted for a bit. I've been busy at home and busy at work. The last few sprints at work have been grueling and ball-busting. Sprints are the Agile process's coding increments. Ours are currently broken into two weeks. Tasks are divided into small enough granules that can be completed in that two weeks or (the one that seems to be happening more and more) broken up in the the smallest pieces that are logically or easily done. My latest task is changing four reports and logically, any one of those could be completed and released independent of the others, but that would mean creating four separate issues to track and overhead for people so it is lumped together. I may or may not get these done in two weeks. I have recently had an issue drag out for several sprints. Let me tell you it fucking sucks. In no uncertain terms, it is a terrible feeling to go into a meeting and have people looking at you like what the fuck have you been doing? Why isn't this done? Well, my excuses have been, scope creep, under pointing stories ( issues are also sometimes called stories and they are pointed to give a weight of difficulty or time involved), stories still at an epic level, and the length of time and the monumental amount of work to arrive at a functional unit test at the database level. Some of these need to be addressed more in our planning meetings or retrospectives where we plan out what to work on and talk about what worked well and what didn't work well looking back. I will bust my hump on this current task and potentially get it done on time, but I need to bring up the self-defeating behavior to the group and discuss methods to minimize this.

The one thing that has seemed to be improving is the unit test portion. A year or more ago, I had not a fucking clue what a unit test was. I've been out of school for about 15 years now, and I must say that I haven't kept up with everything or much of anything really going on in the industry at large. I've had a lot of my personal time consumed by mitigating poor life choices and the like. I've gone through bankruptcy, divorce, the birth of my children, the betrayal of trust by once close friends, and the death of family and friends, just to name a few. Boo fucking hoo, amiright? Really, I was never very industrious and usually gravitated towards spending time doing things that weren't healthy for me. I'm not very career-centric, at least, historically speaking. I've just meandered through: should probably go to college (went to college), should probably graduate (graduated), should probably get a job in my field (got job in my field), should probably get a better job in my field (got better job in my field), etc.

So, now (and I realize I am digressing like a motherfucking boss - wait for it...) I find myself surrounded by people that are very cognizant of their skill sets growing stale and the industry moving forward in leaps and bounds. Since I am a very impressionable guy and easily bow to peer pressure, I have started branching out and learning more, etc. Of course, it helped that I finally started to get my personal shit together. I haven't had my ass kicked in 16 years or so and I haven't had a death threat in 7 or 8. I might be a real boy now, Geppetto! Anyway, since my personal life is doing good, my only distractions at home are hobbies, the family and the usual stuff that every "normal" person deals with, I have some time that I can safely devote to self-improvement (and not just on the tech side of things).

Anyway, back to not knowing what the fuck a unit test was. Given my penchant for doing only things that were at a minimum unproductive and usually detrimental to my health, sanity, employment, etc., I was completely clueless to the advancement of testing and quality control and what not. If my schooling had touched on any of this, I had quickly forgotten it in a booze and stripper fueled haze. I logically understand the need for good testing and find that unit tests and test based development in theory are wonderful things, but in real life, can be quite the undertaking for a database guy. In application code unit tests, programmers can stub out or easily simulate the tangential and/or dependent pieces that your current code needs to interact with to work correctly. This simulation is much more difficult at the database level. For one thing, how do you simulate a record existing in a table? If the procedure you are testing interacts with a record in a table, you pretty much have to have a record in the goddamn table. This, coupled with the advice of a non-database programmer that "you cannot depend on any record existing prior to your test" that I religiously followed because, like most religious zealots, I didn't think about it. Zing! I swallowed it hook line and sinker. My first unit test was a fucking mess. It was a Gary Busey in drag kind of ugly conglomeration of things that shouldn't be where they were and are just repulsive to think about. I quickly learned the uselessness of assuming that nothing exists prior and adding everything. Many of our supporting tables have a strictly controlled list of values, lacking even sequences to populate the primary keys. And, if my goal is to test how a certain construct affects data in a real world scenario, creating some of these type records from scratch simply do not make sense.

About, halfway through my second test, I figured out that I needed a hybrid approach. I needed to be able to assume that some things were in fact already present in the database to appropriately test certain constructs. I pursued the idea of this hybrid like a queer dog pursues a Prius, lightly skipping along with chapped lips and sore knees and humming a Justin Bieber tune. I think I failed to mention that my first test took 99% of the development time. It vastly dwarfed the real actual coding. The part that added benefit to the application and eventually eased the suffering of the end user, at least to a degree, was over quite quickly and I struggled with the "this test isn't going to help things" mindset for a while. Without the mandate that all code needed to have unit tests, I probably would have dropped it. After the hybrid approach was considered, that test still took quite a long time to develop but I felt pride that I improved the process a bit.

My next test resulted in even more improvement on the process. I wrapped the procedures with stripped down procedures and adding error handling and surfaced issues with more actionable information. I really felt it starting to gel. I have finally reached the point where the amount of work to write the unit test didn't dwarf the value adding portion of the work. It was still a 75/25 split or so but it seemed more logical this time around and I felt more capable of actually completing my work on time. I actually met a self imposed deadline today. Its been a while since I've hit one. It feels good man. Awwwww yeahhhhhhhh!

I am hopeful that I can shift the ratio more in my favor in the next iteration and gain even more speed in developing tests and confirming that shit works before it hits production. It is only a matter of time before the infrastructure side of the house has their ducks in some kind of arrangement that looks sort of row-ish and the focus of the issues we are having as a company becomes more application development centric. They probably will not give a shit about why it takes me so long to code these tests, they probably will just ignore any reasons whether valid or bullshit. I'd better get to where I can knock out a unit test in a much faster time. Maybe not premature ejaculation type times. Definitely not Miculek target shooting times. But, something better than 3 times the duration of the real development. I'll get there. With every one, I gain a further nuanced understanding of the inner workings of this thugged out crazy ass database. I don't know if you know this, but one of the database we have is completely fucked up. It is like Lindsay Lohan on Spring Break. It makes no sense, looks like shit, and probably forgot its Plan B. Someone is making a stop at the clinic.

In closing, Unit Tests are a good thing. You should make sure your shit works. Some of us are less perfect than others but none of us are infallible to typos and faulty logic. We cannot know our shit is bulletproof. It isn't. Even if it is, it is better to prove it by shooting at it than just making magnanimous claims like, "My Shit Doesn't Stink!" I say, "NO." and "FUCK YOU!" and "YOUR SHIT STINKS! ALONG WITH YOUR BREATH, YOUR FEET, MOST OF YOUR OPINIONS, AND YOUR MOTHER"S COOKING. SHE CAN SURE SUCK A DICK THOUGH..." But don't get mad, so does mine. I miss you mom.

Perhaps I've said too much. But, no matter. I came here to write a post and fuck bitches and my post is finished.

Unit Tests and Oracle

Our organization has relatively recently jumped on the bandwagon and started testing our code. Fucking weird, I know. Why test your code, you say? Aren't we all super brilliant and everything we right comes out the write way? Well, as you can tell by me switching the right and write there, sometimes shit happens. Even the best authors have typos now and then and even the best coders misplace a semi-colon from time to time. Yeah, the compiler will probably catch the bogus semi-colon but sometimes we have errors in logic as well. I know, I know. It sucks to say. Coders are human and humans are great and terrible and everything in between. And sometimes those errors in logic are so subtle that you might not find it until it is live, and you might not find it until some customer points it out and then you have angry managers and egg on your face and have to endure the snickers and giggles of your co-workers and not just when they catch a glimpse at the urinal. So, some genius somewhere invented the idea of testing your code. This happens in java and dot net by faking a picture of the application and supplying everything that is needed to run a scenario through and compare the output against your expected results. In the database, it is a little bit different. I can not always substitute something fake for something real. Often times, I have to actually enter data into the tables and then delete it afterwards.

So, anyway, all of the other fucktard coders here are implementing unit tests or at least faking it. I didn't want to be left out and didn't really have a choice in the matter. There are at least three ways to do this. One is to write everything yourself. Code setup procedures and tear down procedures and test procedures and run the tests. Two is to find some product to assist you. Three is to do a little bit of both. I have settled on the third way at this point. I wrote my own tests for the first couple of objects before I found the UTPLSQL package written by Steven Feuerstein. It was freeware so I grabbed it and took a look. It is a pretty interesting set of code but since most of the objects I am testing are stored procedures for reporting, I found the asserts in the UTPLSQL a bit limiting. So, naturally, I figured I would use as much as I could and make up the rest on my own. It has worked okay so far.

Unit tests are self-contained objects that mimic everything but the subject of the test and then compare results received from the test subject against expected results. Let's imagine our program is a calculator. It would be relatively easy to test that 1 + 2 = 3 and such. In reality, I work with a transaction processing system and the reports can be quite complicated to test. I insert a new record for every table involved in the report query and compare the results of the stored procedure. For our calculator program we might issue something like: assertTrue(calc(1,2,'+'), 3); however, my report procedures return the data set in a cursor. It looked like the UTPLSQL had an assert that would handle cursors but I had a looming deadline and a few minutes with it wasn't enough for me to figure it out so I panicked and wrote my own cursor assert. I write a package for each report I need to test that contains a setup procedure ut_setup, a tear down procedure ut_teardown, a test procedure ut_rpt_to_test where the name has ut_prepended to the report stored procedure name, and a shit ton of constants. The constants are the string literals that I insert during setup and delete during tear down and compare against the results during the test.

It is still a little bit foreign to me and I'm still learning new and better ways to test. Each one gets a little easier. If you have to work with Oracle and are looking to bulletproof your code, check out UTPLSQL. There may be others out there as well. There are probably suites for other popular databases as well, but you may prefer to home-cook your own. So, go ahead you fucking rebel! Be a Tom Petty! You won't back down from unit testing! But, if HB 499 passes you might get another dance with Mary Jane. Am I right? Nudge nudge wink wink, say no more Squire!

I may get into the nuts and bolts of the tests in a future post and some of you fucking rock star coders might show this humble database halfwit a thing or two with your fancy hipster unit testing. You probably did it before it was cool. Goddamn hipsters! Get off my lawn!

Until next time.

Accepting multiple values in a parameter

I had an interesting case recently where I had to modify a report to accept a parameter that could be a comma separated list of values and I needed to filter on the value(s). Someone on the system before me created a function called stringsplit that helps this occur. Her is the form of stringsplit:

CREATE OR REPLACE FUNCTION STRINGSPLIT (expression varchar2, delimiter varchar2 := ',')

return SPLIT_TABLE pipelined

IS

    idx    pls_integer;

    listTmp    varchar2(32767) := expression;

BEGIN

    loop

        idx := instr(listTmp,delimiter);

        

        if idx > 0 then

            pipe row(substr(listTmp, 1, idx-1));

            listTmp := substr(listTmp, idx+length(delimiter));

        else

            pipe row(listTmp);

            exit;

        end if;

    end loop;

    return;

END STRINGSPLIT;

As you can see, this function returns a SPLIT_TABLE type. The form of the Split_Table:

CREATE OR REPLACE TYPE SPLIT_TABLE AS TABLE OF VARCHAR2(32767);

So, our type is a table of large varchar2 values and our function takes a comma separated list and returns a table of those values. Now, we need to reference and conditionally filter on this in our report query. The condition is that if it is null, do not filter, otherwise filter. It would look something like:

CREATE OR REPLACE PROCEDURE myRecipeReport (

   recipeTypeList   IN     VARCHAR2 DEFAULT NULL,

   result_cursor       OUT SYS_REFCURSOR)

IS

BEGIN

   OPEN result_cursor FOR

      SELECT r.name

        FROM recipes r

       WHERE (   recipeTypeList IS NULL

              OR (EXISTS

                     (SELECT TO_NUMBER (COLUMN_VALUE)

                        FROM TABLE (STRINGSPLIT (recipeTypeList, ','))

                       WHERE TO_NUMBER (COLUMN_VALUE) = r.type)))

      ORDER BY 1;

END myRecipeReport;

This allows us to run the report for everything (list parameter is null) or filter on a subset of available types.

We are getting into several new things here: Types, Casting, etc. Google anything that you are interested in that I do not cover explicitly. 

Types:

A Type is a user defined method to model the structure and behavior of data in an application. You can create object types and static array types and as we've seen, table types. These are usually collections of data that are used somewhere in your application. You will need the Create Type privilege to create a type in your schema, or Create Any Type to create a type in a different schema. 

Casting:

As you may be aware from other coding ventures, Casting converts one datatype to another. 

Pipelined:

You specify Pipelined on a function when you want the function to be the source of data instead of a table. In our purpose here, we are calling the stringsplit function on a single variable and creating a table out of that.

Pipe row:

Pipe row is an Oracle function that is used in the pipelined function to return the results in a table format.

Select from Table:

This is how you treat the results of the pipelined function.

Pls_integer:

This is a pl/sql datatype in the NUMBER family that ranges from -2,147,483,647 to 2,147,483,647. It uses machine arithmetic and is usually faster in operations than the sql datatype NUMBER.

Column_value:

This is how you refer to the column returned from the pipelined function in the table format.

Exists:

This is a native oracle function that executes a subquery and returns true or false depending on whether or not rows were found. If the subquery refers to a column in the parent query it is referred to as a correlated subquery.

I feel I must apologize for the dearth of fucks and shits and goddamnits in this post. I must be off my game today. I am still rather disgruntled and somewhat riled up at the absurdities and idiocy that constantly surround me. I think I must have just focused on getting the information out this time. I will redouble my efforts to include references to coat-hanger abortions and liken my fucktard predecessors to Corky from Life Goes On, Client #9 and/or Justin Beiber's presumably tiny hairless nutsack. Until next time, keep fighting the good fight, stockpiling weaponry, practicing your tomahawk moves, preparing for the zombie apocalypse or civilization collapse and just whatever general badassery you are into. Thanks Obama! ;)

Oracle's NEXT_DAY function and How and Why to use it

I recently received an interesting request. How many transactions did we receive last year on a Friday, Saturday or Sunday? It is an interesting question depending on what kind of transactions you have and what reporting requirements may exist. So, I get this request and I'm all like...WTF? Bitches be trippin'. I have never received a question like this before. It is new ground for me. Ooh, I'm excited! Sorry Ma'am. You too sir.

I do a bit of googling and I don't really find much. Then I discovered NEXT_DAY in an Oracle Doc. So, NEXT_DAY takes a DATE argument and a CHAR argument. The CHAR argument we'll call DayOfWeek.   It is 'SUN'...'SATURDAY', meaning you use either the full name of the day of the week or the standard abbreviation and pass a date in and your results are the date of the next DayOfWeek that you supplied. For example:

select next_day('01-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/6/2013

So, the first sunday after the first was the 6th. Ok. Now for the tricky part:

select next_day('06-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/13/2013

At first, this threw me for a loop, but then it started to make more sense. It is called NEXT_DAY after all, so it would not return the same date. It would return the date of the next DayOfWeek. So, the way to short circuit that if you want to check the closest day is date - 1. So,

select next_day('05-JAN-2013', 'SUNDAY') mydate from dual;

Gets:

MYDATE
1/6/2013

So, now we come to the part where I slay the dragon, save the day and get the girl. Or keep struggling to restore the Thieve's Guild to its former glory. Anyway, I created a table to store a years worth of 3 day weekends, so to speak.


CREATE TABLE WEEKEND_DATES (
 
    WEEKEND_DATE DATE
   
);

I think looped through a year's worth of days (one for each week) and inserted the weekend dates for each week. Like so:

DECLARE

   l_i      NUMBER;

   mydate   DATE;

BEGIN

   l_i := 0;

   LOOP

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'FRIDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'SATURDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      SELECT NEXT_DAY (TRUNC(SYSDATE) - L_I, 'SUNDAY') INTO MYDATE FROM DUAL;

      INSERT INTO WEEKEND_DATES

           VALUES (MYDATE);

      L_I := L_I + 7;

      EXIT WHEN L_I >= 373;

   END LOOP;

   COMMIT;

END;

So, now we have the table WEEKEND_DATES filled with the dates for all of the Fridays, Saturdays and Sundays over the last year. Now we can join that to the transaction table or use an In Clause and a sub-select or an exists with a sub-select to get our desired results. Here is a final query:

SELECT

       CASE timestamp

          WHEN NEXT_DAY (timestamp - 1, 'FRIDAY') THEN 'FRIDAY'

          WHEN NEXT_DAY (timestamp - 1, 'SATURDAY') THEN 'SATURDAY'

          WHEN NEXT_DAY (timestamp - 1, 'SUNDAY') THEN 'SUNDAY'

          ELSE 'NOT A WEEKEND AND SHOULD BE IMPOSSIBRU!!!'

       END

          DAY_OF_WEEK,

       T.* 

FROM TRANSACTIONS T

WHERE TRUNC(timestamp) IN (

SELECT WEEKEND_DATE FROM WEEKEND_DATES);

So, you may be looking at this and going why are you TRUNCing everything? Well, the NEXT_DAY function retains the time portion of the date supplied. So, the first time though this process, the only results received were the ones that matched on the full time as well as date. When you TRUNC a date, it sets the time portion to midnight. This allows you to compare based on any time during the day. TRUNC does have other options so google that if you are interested and I have touched on it in past posts. Also, know that the IN clause might not be the best performing of the options available, but because this was a one-time ad hoc query and results returned quickly enough, I did not dwell on performance. Now if you sick fucks are wondering about the CASE statement above, I simply had the common decency to provide the knowledge of which DayOfWeek the given record actually fell on. I'm cool like that. Now, since we are limiting to days that are Friday, Saturday or Sunday, no other days of the week should be possible. If the results had any IMPOSSIBRU in it, then I rawdogged the pooch like a motherfucker somewhere along the line. I'll need to circle the wagons, go back to the beginning and figure out the failure point, after I kick the pooch down the stairs (just in case).

Alright, I think that is it for today. Class dismissed.

Fuckers.

Function-Based indexes

Indexes in Oracle are like indexes in books and documents. It is simply a way of finding things quicker than looking through the whole thing. But, there can be some tricky little gotchas when it comes to Oracle data and real world scenarios.

I was recently tasked with adding an index to a column in a table. At first glance this is a stupid simple task. Think of the dumbest, most clueless fucktard you know. They could create an index. It is that simple. The confusion and trickiness and why I have a job is knowing when to use an index and how to use an index.

CREATE INDEX index_name ON table_name (column_name [, another_column_name...]);

Not heavy lifting is it? Bro, do you even index?

So, as any good DB dude should do, I ran some queries to get to know the data I was tasked with indexing. So, it turns out that the column I was supposed to index was largely null. This is a problem. Nulls are not included in indexes. How do you index the absence of something? Now, I could go ahead and create an index on the column and all of the non-null values would be indexed appropriately. But, would that be enough? I needed to find out how the column is referenced in the system. I ran a query to look through all of the database objects for references to the column and/or table. I might cover this in another post. I was able to tell that the column was not used in any WHERE clauses in the DB code. I went to an App guy to get perspective on the application and how it interacted with the column. So, it turns out that most of the calls from the App were checking on the NULL status of the column. Well, that's no good. All of those calls would not use our shiny new index and the Application would continue to be dog-ass slow.

Now we get to the meat of this post. Recent versions of Oracle are able to create indexes on function calls on columns. I believe it was added in 8i but wouldn't swear to it. No matter, we are on 11g. The statement I wrote is:

CREATE INDEX FBIDX_MYTAB_NVL_MYCOLUMN ON MYTABLE( NVL(MYCOLUMN, 'some value'));

I named the column FB to indicate Function-Based and IDX so I can know at a glance that it is an Index if I am looking at multiple objects in a list or something. Remember, meaningful names are never a bad idea. The interesting part of this is the NVL function on MYCOLUMN. NVL says, if MYCOLUMN is null, pretend it is 'some value' so I can compare it to other things. So, even when MYCOLUMN is null, NVL(MYCOLUMN, 'some value') is equal to 'some value'. And 'some value' can be indexed. Of course, to fully utilize the Funciton-based index, all of the calls that check the NULL status of the column have to be changed to NVL(MYCOLUMN, 'some value') = 'some value'. But, the bottom line is that the NULL values get indexed and interaction with the table is improved.

Letting the Data Dictionary do some of the leg work

Let's say that you have a column that is on several tables and you need to modify the length of that column. One method of doing that would be to figure out which tables have the column and manually write a shit pot load of alter statements to do what needs to be done. Now, let's suppose you are a shade or two brighter than Corky from Life Goes On. You are going to let the data dictionary do the work for you. Oracle comes with a plethora of tables, views, synonyms and what not that is chock fucking full of information. They are broken up based on item type and user access type. If you are curious, check public synonyms in your schema or if you have read access to SYS, check out the views that look like one of the following:

ALL_%
DBA_%
USER_%

Where the % is some string like TABLES, PROCEDURES, etc. Let's focus on USER_% first. You connect to Oracle as a user and the SYS views that begin with USER_ are the ones that you own. They exist in your schema - the one you logged in with anyway. So, USER_TABLES will list the tables you own, USER_PROCEDURES will list the procedures you own, and so on and so forth. Whereas, the SYS views with ALL_ are the ones that you have access to read, write, execute, etc. The DBA's and a lucky few with elevated privileges will have access to DBA_ views and can see everything in the database. Therefore, DBA_TABLES will list every stinking table in the entire goddamn database.

Now, here is an amazing little trick that will save you a Honey Boo Boo's mom sized chunk of work. You can write queries against those tables where the results are SQL commands. Let's say you have 3 procedures entitled MYPROC1, MYPROC2, and MYPROC3. Now let's suppose you want to compile all of them. You would write select 'ALTER PROCEDURE ' || procedure_name || ' COMPILE;'  as alterallmyprocs from USER_PROCEDURES; and execute that bad boy. Your results will look like:

ALTERALLMYPROCS
ALTER PROCEDURE MYPROC1 COMPILE;
ALTER PROCEDURE MYPROC2 COMPILE;
ALTER PROCEDURE MYPROC3 COMPILE;

So, you can paste those results into a sql window, execute as script and Oracle will compile all of your procedures. Pretty handy right? Well, imagine that you have 25 schemas with similar structures and there are 100 tables in each. Every one of those tables has a column called LAST_UPDATER that tracks via the application which user last updated the record. And now, let's suppose that the USERNAME column is getting enlarged so all of these LAST_UPDATER columns would need to expand to contain the enlarged USERNAME. Ignore for the moment that ideally you would be using the corresponding user id instead of the actual username. This is for instructional purposes, capisce? Would you really want to write the 2500 required statements by hand? Well, maybe you are a masochist. Or maybe you are a contractor. Or maybe you like to sandbag and appear industrious while actually slacking. In those cases, and for you Corky's out there, continue to work hard, not smart. I'll be using sql writing sql to get the same result in a fraction of the time. Just for fun, here is the sql writing sql statement to perform the above task:
select 'alter table ' || owner || '.' || table_name || ' modify ( ' || column_name || ' varchar2(256) );' it from all_tab_cols where column_name in ('USERNAME', 'LAST_UPDATER') and owner like '%MYAPP%';

I was recently tasked with some Year End reports. I needed to run a query in every production database for a specific app or set of apps and I used this technique to meet the narrow deadline. Of course the underlying requirement is that I am able to log into a schema created for me but given read access everywhere for purposes just like this.

Of course, with great power comes great responsibility. Use this for the good of all mankind. Do not fuck with people using this. Do not write something like select 'delete from ' || owner || '.' || table_name || ';' it from all_tables where owner = 'ThatDickThatAggravatesTheFuckOutOfMeAndFoolishlyGaveMePrivsOnHisTables';
Or even worse: select 'delete from ' || owner || '.' || table_name || ' where mod(' || column_name || ', 13) = 0;' from all_cons_columns where constraint_name like '%PK%' and column_name like '%ID%' and owner = 'ThatDickThatAggravatesTheFuckOutOfMeAndFoolishlyGaveMePrivsOnHisTables';
The former is going to delete everything from tables that do not get 'Child Record Found' errors but the latter is wicked nefarious. It will delete every row whose primary key id is evenly divisible by 13 and there are no child record conflicts. Sure, I could work out how to eliminate those conflicts but I don't want to make it that easy for you to really fuck with someone.

All kidding aside, properly used, this can save a ton of time and heartache. Start small, pay attention to syntax, and test, test, test!