Deploying packages to Oracle
-
Ok, so someone left and I seem to have caught some of his hot potato projects; various bits of code hacked together over the years on the whim of various clients, with comments like—I shit you not—“it's not like I'm proud of this unplanned abomination”, signed by people who left long before I got here.
The one in question contains four very nearly identical 1500 line sql packages (each a .pkh with something like create or update package and a .pkb with create or replace package body) whose purpose is to collect various documents to send to a client that absolutely needs to print everything1. Our deployment process for these currently involves cut&pasting code into sql developer. The guy before me at least put copies of the code into source control, so those are reasonably fresh. Naturally, there are differences between the packages currently deployed to integration, UAT, and production. Theoretically, I could even be responsible for one of those changes (commenting out some troublesome validation), though of course, there is no way to tell what changes are made by whom, so you will never know.
The projects are deprecated, so I'm not cleared to spend too much time or resources on them, but I'd still like to know what the best practises are, here.
What I think I want is to save, for each server, a copy of the package as it was previously deployed to the server's schema, then during the install process get the old version out of the db and check for edits before deploying the new version. Assuming that is acceptable, how do I go about doing so?
1 Maybe I'm conflating some things here. Maybe I can't even tell any more.
-
Are you looking for
select dbms_metadata.get_ddl('PACKAGE','MY_PACKAGE') from dual;
-
That sounds right, thanks.
I tried google first, but it seemed like I was using the wrong search terms so I shelved it pending some feedback on whether this was even a sensible process.
-
I'd be careful. I've never used this on packages, but we've found in the past that virtual column definitions and view definitions are only stored in the data dictionary in an optimized form, so you won't get back the exact string you put in. I don't know if/how that applies to DBMS_METADATA.
-
Ok looks not too bad. The package comes out with the schema name prepended, but otherwise no changes detected compared to what was uploaded. I'm thinking of getting the old version out, comparing it to expected, then if they match upload the new version and re export it to compare against next time. That should hopefully make the process as seamless as possible; the idea is to get something that can be reliably scripted, so I don't need to worry about getting something working in test, then forgetting a step when it comes time to deploy it for real.
It's just—and I'm sorry to pollute coding help with off-topic rants like this, but—is this all there is? Like, oracle obviously stores the actual code that was uploaded somewhere: you can see it in sql developer so why does the installation process involve piping queries into and out of files?
Honestly I blame wtdwtf for this whole mess: If I hadn't been hanging out here for, well, however long it's been now, I wouldn't have given two shits about being asked to upload stuff to a production db, using tools I don't even know all the ins and outs of yet. I could have had this boring maintenance-type stuff half-assed and out of the way already, and be on to the actual fun part of my job: writing new code.
-
I wouldn't be surprised if sql developer uses the same builtin package. Everything seems to be a table in oracle.
