Hidden features of PL/SQL

Question

In light of the "Hidden features of..." series of questions, what little-known features of PL/SQL have become useful to you?

Edit: Features specific to PL/SQL are preferred over features of Oracle's SQL syntax. However, because PL/SQL can use most of Oracle's SQL constructs, they may be included if they make programming in PL/SQL easier.

Answer 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.

Answer 2

Maybe not hidden enough , but I love the Merge statement that allow make upserts (insert or update)

MERGE <hint> INTO <table_name>
USING <table_view_or_query>
ON (<condition>)
WHEN MATCHED THEN <update_clause>
DELETE <where_clause>
WHEN NOT MATCHED THEN <insert_clause>
[LOG ERRORS <log_errors_clause> <reject limit <integer | unlimited>];

Answer 3

The truly hidden oracle function is the OVERLAPS function, but is properly not very wise to use any unsupported futures

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

Answer 4

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;

Answer 5

This a PL/SQL procedural construct i use a lot (credits to Steven Feuerstein and Chen Shapira). An Associative array used for chaching, but it does not pre load all data but gets data from database if needed and puts it in the Associative array.

create or replace
PACKAGE justonce
IS
  FUNCTION hair (code_in IN hairstyles.code%TYPE)
    RETURN hairstyles%ROWTYPE;
  TYPE hair_t IS TABLE OF hairstyles%ROWTYPE
    INDEX BY BINARY_INTEGER;
  hairs          hair_t;
END justonce;

create or replace 
PACKAGE BODY justonce
IS
  FUNCTION hair (code_in IN hairstyles.code%TYPE) RETURN hairstyles%ROWTYPE
  IS
    return_value   hairstyles%ROWTYPE;
    FUNCTION hair_from_database RETURN hairstyles%ROWTYPE
    IS
      CURSOR hair_cur IS
      SELECT * FROM hairstyles WHERE code = code_in;
    BEGIN
      OPEN hair_cur;
      FETCH hair_cur INTO return_value;
      CLOSE hair_cur;
      RETURN return_value;
    END hair_from_database;
  BEGIN
    IF NOT (hairs.exists(code_in))
    THEN
      dbms_output.put_line('Get record from database');
      hairs (code_in) := hair_from_database;
    END IF;
    RETURN hairs (code_in);
  END hair;
END justonce;

Test it :

declare
    h hairstyles%ROWTYPE;
begin
   for i in 1000..1004
   loop
      h := justonce.hair(i);
      dbms_output.put_line(h.description);
   end loop;
   for i in 1000..1004
   loop
      h := justonce.hair(i);
      dbms_output.put_line(h.description||' '||h.price);
   end loop;

end;
/

Get record from database
CREWCUT
Get record from database
BOB
Get record from database
SHAG
Get record from database
BOUFFANT
Get record from database
PAGEBOY
CREWCUT 10
BOB 20
SHAG 21
BOUFFANT 11
PAGEBOY 44

Answer 6

1. An undocumented function: dbms_system.ksdwrt (writes to alert/trace files)
2. DBMS_SQL package (as an example of its use see this question
3. AUTHID CURRENT_USER clause
4. Conditional compilation

Answer 7

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;

Answer 8

Dynamic PL/SQL is ugly, but can do some interesting stuff. For example, names can be treated as variables, which I've used earlier to traverse %rowtype variables like arrays, and to create a function which will, for a given table name, return a cursor which selects a single row with the default values of each column. Both are useful workarounds for denormalized tables.

Answer 9

My answer to Hidden Features in Oracle is relevant here:

Since Apex is now part of every Oracle database, these Apex utility functions are useful even if you aren't using Apex:

SQL> declare
  2    v_array apex_application_global.vc_arr2;
  3    v_string varchar2(2000);
  4  begin
  5  
  6    -- Convert delimited string to array
  7    v_array := apex_util.string_to_table('alpha,beta,gamma,delta', ',');
  8    for i in 1..v_array.count
  9    loop
 10      dbms_output.put_line(v_array(i));
 11    end loop;
 12  
 13    -- Convert array to delimited string
 14    v_string := apex_util.table_to_string(v_array,'|');
 15    dbms_output.put_line(v_string);
 16  end;
 17  /
alpha
beta
gamma
delta
alpha|beta|gamma|delta

PL/SQL procedure successfully completed.

Answer 10

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.

Answer 11

Do you know that with the option SAMPLE (K) you can SELECT only a sample composed by up to K percent of an Oracle table?

SELECT *
  FROM MASSIVE_TABLE SAMPLE (5);

The previous statement retrieves a randomic set composed by up to 5% of the records stored in the massive table called MASSIVE_TABLE.