P_name := null - can't safely return anythingĭbms_output.put_line ( 'v_name3=' ) ĭbms_output.put_line ( 'v_name4=' ) Īn actual practical use for this function came up during a Forms project (many years after I wrote varlen). V_long_name varchar2 ( 10 ) := 'DanClamage' Now I can use it in my procedure to handle the potential size limitation gracefully: WHEN OTHERS THEN - some error occurred restore string RETURN( v_mid_len ) - last known good length WHEN VALUE_ERROR THEN - exceeded string size
P_str := RPAD( 'A', v_mid_len, 'A' ) - try this length V_mid_len := FLOOR(( v_hi_len + v_lo_len ) / 2 ) - average hi-lo V_lst_len := v_mid_len - last mid before change V_save_str VARCHAR2 ( 32767 ) - save string for non-destructive read V_lst_len PLS_INTEGER := - 1 - last mid, exit check when no change V_mid_len PLS_INTEGER := 0 - midpoint between lo-hi V_hi_len PLS_INTEGER := 32768 - max+1 for any varchar2 V_lo_len PLS_INTEGER := NVL(LENGTH( p_str ), 1 ) - min for this varchar2 returns max length allowable for given VARCHAR2 variableįUNCTION varlen ( p_str IN OUT VARCHAR2 ) Here is my function to determine the size of the parameter: This is a simple binary search algorithm. So I can try a smaller or larger value iteratively, and zoom in pretty quickly on the precise size.
If a VALUE_ERROR is thrown, then the string is too big. I realized one way to tell is to simply try to write a string to the parameter, and see what happens. So how can we tell how many bytes we can safely return on the parameter list, without causing an exception? The only difference is the size of the variable being provided on the parameter list. ORA-06502: PL/SQL: numeric or value error: character string buffer too small Here is a simple illustration:Ĭreate or replace procedure my_name ( p_name in out varchar2 )ĭbms_output.put_line ( 'v_name1=' ) ĭbms_output.put_line ( 'v_name2=' ) But if the procedure returns a string value back to a variable that’s smaller than the string, a VALUE_ERROR exception is thrown. Potentially, the unconstrained definition of VARCHAR2 can return a value up to 32,767 bytes. Somebody once asked me, in regards to a subprogram that writes back a string via the parameter list, how can the called subprogram know how big is the provided string buffer from the calling context? The parameter only defines the data type, not its size. Therefore a single-database query will always be more efficiently executed with a direct client connection to the target database. Once from the remote database to Intelligence Central, and again from Intelligence Central to the Toad client. So, what could go wrong? For starters, even a simple SQL query that only fetches data from one database would incur the overhead of copying the query’s result data across the network twice.
You can of course always contact Quest Software to get a new freeware or trial key, or possibly even convert a commercial version key.
In addition these freeware are good until Oct 31 st 2013 (at which time their trial key license will expire). These freeware offer a useful subset of their commercial product counterparts.
The Toad Freeware Amazon Machine Image (AMI) is a relatively simple virtual machine that’s running a minimalist Windows Server 2008 R2 operating system and which has three Quest Software (now part of Dell) Toad freeware apps preinstalled and placed on the desktop: Toad for Oracle, Toad for SQL Server and Toad for MySQL.