Biggest

Specify a table and a column name, to find the longest value held in that column. Can be useful when analyzing usage patterns or diagnosing "value too large" errors.

-- biggest.sql
-- William Robertson, www.williamrobertson.net
--
-- Find value with greatest length
-- &1 = column name
-- &2 = table name
--
-- SQL> @biggest object_type dba_objects
--
-- OBJECT_TYPE
-- ------------------
-- EVALUATION CONTEXT

-- "&1" can be any expression which is valid within a LENGTH() expression.
-- "&2" can be any expression which is valid in a FROM clause.
-- See also most.sql, count.sql, percent.sql, cardinality.sql etc

def column = &1
def table = &2
col l new_value l noprint

set term off
store set sqlplus_settings.sql replace
set term on feed off

select distinct &column, to_char(length(&column)) as l
from   &table
where  length(&column) =
       ( select max(length(&column)) from &table )
order by 1
/

prompt Length: &l

col l clear

@sqlplus_settings.sql
set term on
prompt