Oracle Scratchpad

April 3, 2017

Block Names

Filed under: Oracle,Philosophy — Jonathan Lewis @ 1:04 pm BST Apr 3,2017

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.

4 Comments »

  1. I’m assuming you know about q quoted string literals.. but’ll comment here for all of your followers..


    select q'{all quotes like ' and " and ignored until the closing brace/quote pair which is here ->}'
    from dual;

    its in the oracle documentation under “String Literals”

    Comment by David Pyke Le Brun — April 3, 2017 @ 3:15 pm BST Apr 3,2017 | Reply

    • actually i guess your other options was escaping wildcards in LIKE clauses such as

      select * from dual
      where '1234%6789' like '%\%%' ESCAPE '\'; -- find string with an actual percentage sing in it

      Comment by David Pyke Le Brun — April 3, 2017 @ 3:18 pm BST Apr 3,2017 | Reply

  2. […] from Franck Pachot [July 2021] about fully qualified names in Postgres prompted me to highlight a note I wrote a few years ago about using the label mechanism in Oracle’s PL/SQL to avoid collisions […]

    Pingback by PL/SQL Labels | Oracle Scratchpad — October 24, 2022 @ 9:06 am BST Oct 24,2022 | Reply

  3. […] PL/SQL labels (Apr 2017): reducing errors by using “block” labels to qualify pl/sql variable names. […]

    Pingback by Troubleshooting catalogue | Oracle Scratchpad — October 24, 2022 @ 9:18 am BST Oct 24,2022 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by WordPress.com.

%d bloggers like this: