Stored Procedures

Background

PL/SQL, or Procedural Language extensions to SQL (Structured Query Language), is an extension of SQL that is used in Oracle. This allows for writing programming code in close relation where it would not be available in regular SQL. TSQL, in contrast, is Microsoft and Sybase's proprietary extension to SQL (so that users of this product might also be allowed to write program blocks attached to their SQL).

Description

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.

The functional coding of the database is held in Stored Procedures. A stored procedure is exactly that -- a procedure that is stored. It's defined and compiled as a part of the database. Contrast this to a simple procedure one might store in notepad to run manually on a whim. For our purposes, a stored procedure is set up as a PACKAGE which contains PROCEDURES and FUNCTIONS. A procedure executes commands. It selects data, creates arrays, passes information back and forth, and so on. It does this by having parameters passed to it. A function also executes commands, but its purpose is to return a specific value.

The package body holds the bulk of the functional programming. Different packages do different things, just as different programs do different things. It would be entirely possible to write all of it as one big package, but it would be very confusing to find how any part of code would relate to any other part, and any change would require re-compiling the whole thing.

The act of defining a package is known as creating a package. When a package is created, we first define the specification of the package (the "spec"). The package spec doesn't run any specific data but defines the procedure names and variables for those procedures inside of that package. After the package specification is created, the package body may be created. The package body runs its own code (such as selecting data) and then defines the actual values for a procedure or function. A package's function returns the data requested when called; a package's procedure runs its code when called. These packages' procedures may be called within the package itself or from another package. For example, an error in the MEDCTR package may call the ADMIN_PKG package's procedure "log_system_error".

Any package may be dependent on one or more other packages, so care must be taken when modifying packages / procedures / functions. As a made-up example, let's say that we've changed a procedure "get_name" in a package "Package-X":

CREATE OR REPLACE PROCEDURE get_name (name IN VARCHAR2,
                                      mid  IN VARCHAR2);

so that there's now one more (or less) value.

CREATE OR REPLACE PROCEDURE get_name (name IN VARCHAR2,
                                      lid  IN VARCHAR2,
                                      mid  IN VARCHAR2);

This will result in the STATE of the package changing (the end user will probably see something like "the existing state of the package has changed"). The package may not be valid or may need to be recompiled.

If "Package-X" is changed such as above and "Package-Y" calls on "Package-X", then "Package-Y" will also be invalidated. Although the package may have recompiled without errors, the users and processes who are logged in still see the state of the package as it was when they logged in. These users and processes will have to log out and back in to see the new values.

It is very important to back up the existing stored procedures before making changes (and testing as well). Even one typo (such, as 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.

There is a file on our 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 (atms, atms_archive, www_user, etc).

This 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.

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.