'ORA-06502: PL/SQL: numeric or value error: character string buffer too small' when running Toad generated Function statement in Script Runner (4242862)

'ORA-06502: PL/SQL: numeric or value error: character string buffer too small' when running Toad generated Function statement in Script Runner

When you right-click a Function in the Schema Browser, click Execute, and then in the Set Parameters window that shows up, if you press the 'Run in Script Runner' button, the following error shows:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at line 29

It seems to be due to the line which is generated at the end:

:1 := l_RetVal;  
If this line is commented out, or if l_RetVal is an integer, there is no error

Toad needs to declare the bind variable at the top of the script so that it does not cause errors in Toad Script Runner (TSR) or SQLPlus.

In this example, it would look like:
var "1" varchar2(32767)

WORKAROUND:
There are some possible workarounds for the issue:

  1) In the 'Set Parameters' window, where you have your arguments etc, just click OK here, instead of the 'Run in TSR' button.

  2) Manually add "var" declaration at the top of the script      

**Please Note** - if you add "var" declaration, the word VAR and the var declaration (double quoted) must be on same line as the example above.  
If there are multiple vars, make one line for each.
Also, if you add "var" declarations, the SQL will not be valid for running by clicking OK, or for running in the Editor with F9.  This is for TSR and SQL*Plus only.

  3) Comment out the output variable section if running in TSR.

STATUS:
Waiting for fix in a future release of Toad for Oracle

TMB-1609