Extracting a Specific Stored Procedure

Summary/Question

It is very important to back up the existing stored procedures before making changes (and testing as well). Even one typo (such, as a an accidental comma or incorrect usage of brackets or braces] will cause huge problems for the whole functioning of the inner workings of the database.

When this backup file exists, someone can then refer to the old code or revert a change that didn't go as planned. It's good to have these prepared before changing the procedure so that the reversion time is minimal.

This document assumes the reader has knowledge of Oracle SQL procedures and / or has read the Stored Procedures article by the same author. This document also assumes the reader has a working knowledge of the vi editor (beyond switching back and forth between command mode and insert mode -- you will make things very hard on yourself if you only treat the vi editor like notepad). If you have an editor such as notepad++ or brackets that shows you the line numbers and you have the ability to go straight to line numbers, then more power to you. This may seem complicated, but there are really only a few steps. Written out, however, it seems lengthy.

Topic / Solution

At its heart, the bulk of the database-side happenings in Smart Suite is PL/SQL. This allows for requests from other programs to the database and for a lot of the work to be done inside the database, rather than only using the database for data storage and lookups.

There is a file on the Smart Suite database servers that gathers the data from the stored procedures and spools the data out into a text file. The file is the get_sp.sql script and is called from the sql command line:

SQL> @/home/amcom/schema/install/get_sp

(you don't *need* to put the .sql after it if it ends in .sql, unless there's another dot in the way, such as file.20160125.sql)

This pulls the stored procedures for whichever user you're logged in as. When spooling any data, if you don't pass the extension to the filename, the system will automatically label it with a .lst extension. You can call it a .txt or a .sql or whatever you'd like. It's better to give it an extension in such a way that the file can be opened in Windows later (unless you've already mapped .lst's to open with a text editor on your own Windows machine), since Windows requires a file extension to know which program to use for a given file, and it's likely that you or someone else might open it on their own machine to see the contents of the file.

Running the get_sp (or get_trg) will prompt you for a file name for storage. It's ideal to use something like:
/home/amcom/<user>_sp (to distinguish it from the triggers you can also pull).date(.sql)

such as:
/home/amcom/atms_sp.20160113_01.sql
if you're logged in as atms to get the atms user's stored procedures,
or
/home/amcom/www_user_sp.20160113_01.sql
if you're logged in as www_user,
or
/home/amcom/atms_trg.20160113_01.sql
for a trigger extraction via get_trg.

In this example, the 01 in YYYYMMDD_01 is because this get_sp may need to be run again on the server in the same day (in which case you would do YYYYMMDD_02 and so on). If the file name is the same, it will be overwritten.

Extracting the Package

First, back up the data:

Let's say that we've received a new fax_pkg to load in somewhere. We want to extract only the old fax_pkg from the sp backup (there are also ways to do this other than through the get_sp, but that's what we're talking about here).

Now find where fax_pkg exists in the file.

Here we got lucky because there wasn't a stream of calls to fax_pkg. A better way to do it (you'd have to do this for something like medctr) would be to grep -i | grep CREATE.

This because sometimes the package name is written with many spaces after the word "BODY":

or

Since you'd then have to search through the file for the exact pattern, an even better way would be to use grep's "n" flag to find the line number these occur on:

The first ones listed without "BODY" are the specifications. Those MAY be needed, so keep track of those line numbers too.

Copy the stored procedures backup to a new file, such as fax_pkg.20150824_01.sql. Then you can edit the new file and leave the originals alone.

There are a few different ways to get where we're going on this, but start from the bottom since you have the line numbers. When you start deleting lines from the backup (for example, if you start at the top), the line numbers change.

First go to the last CREATE statement, the BODY creation. We know from the grep -n that this is line 62456 in this file for this example document (so we go to line 62456). Insert a new line above this CREATE statement and add some marker that would not otherwise occur in the file, such as a string of "t"s.

Now find where this CREATE statement ends by searching for the next CREATE.

Delete everything in the file from the next statement's CREATE to the end.

Now go to the line that has the first CREATE statement. Since we used grep -n, we know it's line 3770. As stated before, we could also search for the text as it exists from the grep statement, but it's easier to go to the line.

From the first CREATE line, delete all lines above it (where the cursor is in the next image) to the top.

Then find the end of this CREATE by searching for the next CREATE.

From that line, delete from there to whatever pattern was used to mark the beginning of the BODY, such as tttt.

This deletes all the data in the middle:

Now delete that marker line. Make sure that both the spec and the body start with CREATE and make sure both end with slash (/).
Save the file and exit.

Now you can modify whatever copies you'd like, or use diff or BeyondCompare or anything you'd like to compare the extracted package and another version of it.

"Wait, you said this was only a few steps! This seems like a long document!"

OK, fine, so... what are the steps?

Fluff:   Create a backup of the existing stored procedures.
Fluff:   Copy the backup to a new file for editing.
Fluff:   Find the procedure's spec and body.
Meat:  Delete everything above the spec.
Meat:  Delete everything below the body.
Meat:  Delete everything between the end of the spec and the beginning of the body.
Fluff:   Verify your work.
Fluff:   Save and exit.

If it seems overly complex, take some time and try to identify where you might need some work to smooth this process out for yourself. Don't always expect someone else to have created the backup for you.