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 which have one of two prefixes in their names “M_” or “G_” (for memory or global, respectively) but I probably ought to be formal than that, so here’s an example of labelling 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 nologging as select * from all_objects ; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); end; / create index t1_i1 on t1(object_id) nologging; -- -- Anonymous PL/SQL block with label, and a couple of -- uses of the label as the qualifier for variables -- <
> 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 the block name has to be surrounded by doubled angle brackets – and now that I’ve written about it I might actually remember that for a couple of weeks ;)
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.
The other thing I can never remember is how to escape quote marks – so I invariably end up using the old “double the quotes” method when I want to quote quotes.