Did You Know That About PL/SQL Variables?

In his latest post titled Less is More, More or Less, John Russell, the man behind tahiti.oracle.com, reveals a very interesting fact about the space needed to store PL/SQL variables of varchar2 datatype. He writes:

If you don’t know exactly how much space is needed for a PL/SQL variable, the most space-efficient thing you can do is counter intuitive: declare a gigantic variable… Once you go above 2000 characters, the variable stops being statically allocated to accommodate its maximum size, and starts being dynamically allocated every time it’s assigned, based on the exact size that’s really needed.

He gives an example:

– Rest of 512 characters are wasted empty space
email_address varchar2(512) := ’someone@example.com’;
– Although declared with length 32000, only 24 characters are allocated
email_address varchar2(32000) := ’someone_else@example.com’;

I know that, using DUMP, you can return the length of a variable in bytes, but the question is how do you really calculate the total space allocated to a certain PL/SQL variable?

John also gives an additional tip:

When you want a variable to hold the contents of a table column, there is a simple way to match the right length: var1 my_table.my_column%type;

Using anchored type declarations is one of PL/SQL’s best practices. But, what if you have a variable that is a concatenation of two or more table columns? Here is the trick:

declare
cursor my_cursor is select col1 || col2 concatenated from my_table;
var2 my_cursor.concatenated%type;

We let Oracle figure out the maximum length of the concatenated columns, then piggyback on that result to make a variable of the same type.

John always has interesting tips. Here are 3 Useful SQL*Plus Tips I found on his blog a few weeks ago.

Possibly related:

Tagged , | Post a Comment