Block Names

There are a number of tiny details that I can never remember when I’m sketching out models to test ideas and one of those is the PL/SQL block name. Virtually every piece of PL/SQL I write ends up with variables that have one of two prefixes to their names “M_” or “G_” (for “memory” or “global” respectively) but I probably ought to be more formal than that, so here’s one example of using the option to label pl/sql blocks – specifically labelling anonymous blocks from SQL*Plus – using a trivial and silly bit of code:

rem
rem     Script:         plsql_block_names.sql
rem     Author:         Jonathan Lewis
rem     Dated:          March 2017
rem

create table t1
as
select * from all_objects
;

--
--        Anonymous PL/SQL block with label, and a couple of 
--        uses of the label to qualify variable names.
--

<<my_block>>
declare
        data_object_id  t1.data_object_id%type;
        object_id       t1.object_id%type;
begin

        select  data_object_id
        into    my_block.data_object_id
        from    t1
        where
                data_object_id is not null
        and     rownum = 1
        ;

        select  object_id
        into    my_block.object_id
        from    t1
        where
                data_object_id = my_block.data_object_id
        and     rownum = 1
        ;

        dbms_output.put_line('Object: ' || object_id || ' - ' || my_block.object_id);
        dbms_output.put_line('Data Object: ' || data_object_id || ' - ' || my_block.data_object_id);

end;
/

Object: 16 - 16
Data Object: 6 - 6

The important point, of course, is that with a qualified variable name you eliminate the risk of a problem that appears occasionally on one of the Oracle forums or list-servers where someone has lost track of duplicated variable names or used (as I have above) a variable name that matches a column name, and doesn’t notice that Oracle has captured the “wrong” interpretation of the name at some other point in the code.

You’ll note that when you declare a block name it has to be surrounded by doubled angle brackets – and now that I’ve written about it I might actually remember to use them for a couple of weeks.

Footnote:

This isn’t the only use for labels, by the way, it’s just one that probably ought to be used more frequently in production code. You could/should also follow the same strategy with functions and procedures – when you use a variable declared inside a function or procedure you should qualify it with the function/procedure name.