Hidden features of PL/SQL [closed]

Question

You can override variables, you can name anonymous blocks, and you can still refer to the overridden variables by name:

PROCEDURE myproc IS
   n NUMBER;
BEGIN
   n := 1;
   <<anon>>
   DECLARE
      n NUMBER;
   BEGIN
      n := 2;
      dbms_output.put_line('n=' || n);
      dbms_output.put_line('anon.n=' || anon.n);
      dbms_output.put_line('myproc.n=' || myproc.n);
   END anon;
END myproc;

You can index pl/sql tables by other types besides integers. This way you can create "dictionary" like structures, which can make your code much easier to read:

Example:

DECLARE
  TYPE dictionary IS TABLE OF VARCHAR2(200) INDEX BY VARCHAR2(100);
  dict dictionary;
BEGIN
  dict('NAME') := 'John Doe';
  dict('CITY') := 'New York';

  dbms_output.put_line('Name:' || dict('NAME'));
END;

The truly hidden oracle function is the OVERLAPS function, but it is probably not very wise to use any unsupported features.

select 'yes' from dual where (sysdate-5,sysdate) overlaps (sysdate-2,sysdate-1);

One little-known feature I have had great success with is the ability to insert into a table using a variable declared as its %ROWTYPE. For example:

CREATE TABLE CUSTOMERS (
    id NUMBER,
    name VARCHAR2(100),
    birth DATE,
    death DATE
)

PROCEDURE insert_customer IS
    customer CUSTOMERS%ROWTYPE;
BEGIN
    customer.id := 45;
    customer.name := 'John Smith';
    customer.birth := TO_DATE('1978/04/03', 'YYYY/MM/DD');

    INSERT INTO CUSTOMERS VALUES customer;
END;

Although it chews up a bit more redo tablespace, it certainly makes inserting data (especially into larger tables) much clearer. It also avoids the multitude of variables needed to store each column's value you wish to insert.

Procedures and functions may be defined within DECLARE blocks:

DECLARE

    PROCEDURE print(text VARCHAR2) IS
    BEGIN
        DBMS_OUTPUT.put_line(text);
    END;

BEGIN

    print('Yay!');
    print('Woo hoo!');

END;

This is handy for creating stand-alone scripts.