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 the public fora 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 a little further down the code Oracle has captured the “wrong” interpretation of the name.

You’ll note that when you declare the 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.

 

3 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. […] notice, by the way , that this is one of those rare cases where I’ve remembered to use a label to name the PL/SQL block, and then used the label to qualify a variable I’ve used inside the […]

    Pingback by Preferences | Oracle Scratchpad — August 9, 2021 @ 12:59 pm BST Aug 9,2021 | 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: