News, views, tips and tricks on Oracle and other fun stuff

Did You Know That About PL/SQL Variables?

In his latest post titled Less is More, More or Less, John Russell, the man behind, 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) := ‘’;
— Although declared with length 32000, only 24 characters are allocated
email_address varchar2(32000) := ‘’;

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:

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.

Filed in Oracle, Tips on 31 May 07 | Tags: ,

Reader's Comments

  1. |

    Shouldn’t that be “… once you go above 1999 characters?”

    Sounds like a recipe for unnecessary performance issues to me. Worry less about the few M of your users address space and more about many cycles of your cpu, since the latter will suddenly enhance any potential latching issues, but when do you have to search any wasted user space?

  2. |

    The limit is actually >4000 in 10g. I know because I spotted it and raised it as a bug (4330467) on metalink, only to be told it was a “feature” of 10g.



  3. |

    And here is an excerpt from the PL/SQL User’s Guide and Reference 10g Release 2: You might need to allocate large VARCHAR2 variables when you are not sure how big an expression result will be. You can actually conserve memory by declaring VARCHAR2 variables with large sizes, such as 32000, rather than estimating just a little on the high side, such as by specifying 256 or 1000. PL/SQL has an optimization that makes it easy to avoid overflow problems and still conserve memory. Specify a size of more than 4000 characters for the VARCHAR2 variable; PL/SQL waits until you assign the variable, then only allocates as much storage as needed.

  4. |

    I have a question…are people still interested in getting certified? Cerebral Solutions is offering a 6 day certification and can’t fill classes. It’s unbelievable. Has certification lost its shine?

  5. |

    There is a drawback to this approach. If the actual usage varies significantly in the lifetime of your session, then dynamic allocation can result in multiple child cursors for the same statement.

    On a highly concurrent system, this may be something you would prefer to avoid, since all three versions will be held on the same library cache latch, and multiple versions means longer hold times.

    There is a short note about this type of thing, with some follow-up discussion, at

  6. |

    Regarding Documentation:

    Yes, the bug I raised was against the documentation. It originally quoted the old 2000 byte limit.

    I’ve not checked recently, but the old 10.1 documentation was left with the incorrect value.