Feuertip #38: Well, I declare: declarations in PL/SQL

I can’t remember the last time I wrote a PL/SQL procedure or function or package and did not declare a variable, constant, or type. We all write declarations, but do we all do it in ways that lead to maximum readability/maintainability and top-quality code? Probably not. So here are some tips.

Don’t skimp on the names

Skimp: expend or use less time, money, or material on something than is necessary in an attempt to economize. (Oxford Languages)

Less material as in fewer characters. I say this for two reasons:

1. We no longer have the 30-character limit for identifiers, so (a) don’t go crazy with 100-character names, but also (b) don’t be afraid to be fully descriptive and step right over the old 30-character (now psychological) limit. I find this very liberating.
2. Don’t be lazy. Don’t use “i” for the loop index. Don’t use var1, var2, var3 for variable names. don’t write code as though it is for university homework. 🙂

Instead of skimping….

• Standardize naming conventions: here at Insum, we use a variation of the wonderful Trivadis guidelines.
• Take the time to come up with names that accurately and clearly reflect what those variables or constants contain.

Use subtypes to give names to application “types”

What’s an application type? It’s an application-specific variant on a pre-defined type. Suppose, for example, I work a lot with prices in my sales application. A price of a product always has the form  99999.99. So I can do this, over and over again:

declare
   l_price number (7,2);

But I’d rather avoid doing that since, at some point, someone might decide that we will, say, track prices down to the 1000th of a dollar.

Wouldn’t it be nice if I could instead declare the variable as follows:

declare
   l_price price_t;

or perhaps

declare
   l_price sales_config.price_t;

Here’s a more elaborate example. I need to write a package body to keep track of multiple lists of strings that have already been “used” in some way in my session. No persistent data, so I will rely on collections. Here’s a version of part of the package body that does not take advantage of subtypes:

create or replace package body string_tracker
is
   type booleans_t is table of boolean index by varchar2(32767);

   type two_fields_t is record (
      case_sensitive boolean,
      list_of_values booleans_t
    );

   type lots_of_two_fields is
      table of two_fields_t index by varchar2(32767);

   g_list_of_lists lots_of_two_fields;

And here’s a version of that which does use subtypes:

create or replace package body string_tracker
is  
   subtype list_name_t is varchar2(100);
   subtype used_name_t is varchar2(1000);
   
   subtype only_the_index_matters_t is boolean;
   
   type used_aat is table of only_the_index_matters_t index by used_name_t;
      
   type list_rt is record (
      case_sensitive boolean,
      list_of_values used_aat
   );
   
   type list_of_lists_aat is
      table of list_rt index by list_name_t;
      
   g_list_of_lists list_of_lists_aat;

Avoid hard-coding limits (like varchar2(N))

Suppose I need to declare a string variable to hold the largest possible value. It might look like this:

declare
   l_big_string varchar2(32767);

But this declaration then hard-codes the current maximum length. What if Oracle makes it bigger in the future? You could take this approach instead:

create or replace package app_types authid definer
is
   subtype maxvarchar2_t is varchar2(32767);
end;
/

declare
   l_big_string app_types.maxvarchar2_t;

Another use for subtypes! 🙂

Use %rowtype to consolidate declarations

Ever see a procedure with a declaration section like this?

procedure review_account ( id_in in integer ) is
   l_id                  sg_account.id%type;
   l_acc_name            sg_account.name%type;
   l_description         sg_account.description%type;
   l_driver_type_id      sg_account.driver_type_id%type;
   l_engine_type_id      sg_account.engine_type_id%type;
   l_output_type_id      sg_account.output_type_id%type;
   l_is_static           sg_account.is_static%type;
   l_sa_object_type_id   sg_account.sa_object_type_id%type;
   l_author              sg_account.author%type;
   l_column_position     sg_account.column_position%type;
   l_output_prefix       sg_account.output_prefix%type;
   l_output_suffix       sg_account.output_suffix%type;
   l_file_extension      sg_account.file_extension%type;
   l_source_file_name    sg_account.source_file_name%type;
   l_created_on          sg_account.created_on%type;
   l_created_by          sg_account.created_by%type;
   l_changed_on          sg_account.changed_on%type;
   l_changed_by          sg_account.changed_by%type;
   l_in_context_name     sg_account.in_context_name%type;
   l_is_locked           sg_account.is_locked%type;
   l_locked_by           sg_account.locked_by%type;
   l_locked_password     sg_account.locked_password%type;
   l_universal_id        sg_account.universal_id%type;
   l_is_top_level        sg_account.is_top_level%type;
   l_app_id              sa_application.id%type;
   l_app_name            sa_application.name%type;
   l_app_description     sa_application.description%type;
   l_refresh_frequency   sa_application.refresh_frequency%type;
   l_def_roadmap_dir     sa_application.def_roadmap_dir%type;
   l_def_script_dir      sa_application.def_script_dir%type;
   l_def_code_dir        sa_application.def_code_dir%type;
   l_def_sequence_prefix sa_application.def_sequence_prefix%type;
   l_def_sequence_suffix sa_application.def_sequence_suffix%type;
   l_def_pky_column_name sa_application.def_pky_column_name%type;
   l_rae_error_code      sa_application.rae_error_code%type;
   l_deploy_dir          sa_application.deploy_dir%type;
   l_use_qda             sa_application.use_qda%type;

You see the pattern, right? Let’s work some magic:

procedure review_account ( id_in in integer ) is
   l_account     sg_account%rowtype;
   l_application sa_application%rowtype;

Ah….so much better. No need to declare each variable individually – and if a column is added to the table, it is automatically available as a field in the record after recompilation.

You might ask: “I only need 10 of the columns, and the table has 500 columns. Won’t that use a bunch of extra memory?”

And I would answer:

• Maybe, but it probably won’t matter.
• You can avoid this problem by declaring an explicit cursor with your 10 columns and then %rowtype against that.

create or replace procedure review_account (
   id_in in integer
) is
   l_account sg_account%rowtype;
   
   cursor sa_application_cols_cur is 
      select id,name,description from sa_application;
   l_application sa_application_cols_cur%rowtype;

You might not even fetch a row from this cursor; it’s just to provide a structure for %rowtype.

All righty, I hope that gives you some ideas to apply to your next new program – or refactoring of an existing one.