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.
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 |
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 |
[…] 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 |
[…] 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 |