Oracle PL/SQL Tutorial/PL SQL Data Types/LONG
LONG type display length is default to 80
<source lang="sql">
SQL> By default, this value is 80. SQL> If you try to query a LONG column, only the first 80 characters would be displayed. SQL> SQL> select text
2 from all_views 3 where view_name = "USER_TABLES";
TEXT
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name), decode((bitand(t.property, 512)+bitand(t.flags, 536870912)), 0, null, co.name), decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"), decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)), decode(bitand(t.property, 32), 0, t.initrans, null), decode(bitand(t.property, 32), 0, t.maxtrans, null), s.iniexts * ts.blocksize, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize), s.minexts, s.maxexts, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extpct), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))), decode(bitand(t.property, 32+64), 0, decode(bitand(t.flags, 32), 0, "YES", "NO"), null), decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"), t.rowcnt, decode(bitand(t.property, 64), 0, t.blkcnt, null), decode(bitand(t.property, 64), 0, t.empcnt, null), decode(bitand(t.property, 64), 0, t.avgspc, null), t.chncnt, t.avgrln, t.avgspc_flb, decode(bitand(t.property, 64), 0, t.flbcnt, null), lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10), lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10), lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5), decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"), t.samplesize, t.analyzetime, decode(bitand(t.property, 32), 32, "YES",
SQL> / TEXT
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name), decode((bitand(t.property, 512)+bitand(t.flags, 536870912)), 0, null, co.name), decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"), decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)), decode(bitand(t.property, 32), 0, t.initrans, null), decode(bitand(t.property, 32), 0, t.maxtrans, null), s.iniexts * ts.blocksize, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize), s.minexts, s.maxexts, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extpct), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))), decode(bitand(t.property, 32+64), 0, decode(bitand(t.flags, 32), 0, "YES", "NO"), null), decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"), t.rowcnt, decode(bitand(t.property, 64), 0, t.blkcnt, null), decode(bitand(t.property, 64), 0, t.empcnt, null), decode(bitand(t.property, 64), 0, t.avgspc, null), t.chncnt, t.avgrln, t.avgspc_flb, decode(bitand(t.property, 64), 0, t.flbcnt, null), lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10), lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10), lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5), decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"), t.samplesize, t.analyzetime, decode(bitand(t.property, 32), 32, "YES",
SQL> set pagesize 1000 SQL> set long 10000 SQL> / TEXT
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name), decode((bitand(t.property, 512)+bitand(t.flags, 536870912)), 0, null, co.name), decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"), decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)), decode(bitand(t.property, 32), 0, t.initrans, null), decode(bitand(t.property, 32), 0, t.maxtrans, null), s.iniexts * ts.blocksize, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize), s.minexts, s.maxexts, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extpct), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))), decode(bitand(t.property, 32+64), 0, decode(bitand(t.flags, 32), 0, "YES", "NO"), null), decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"), t.rowcnt, decode(bitand(t.property, 64), 0, t.blkcnt, null), decode(bitand(t.property, 64), 0, t.empcnt, null), decode(bitand(t.property, 64), 0, t.avgspc, null), t.chncnt, t.avgrln, t.avgspc_flb, decode(bitand(t.property, 64), 0, t.flbcnt, null), lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10), lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10), lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5), decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"), t.samplesize, t.analyzetime, decode(bitand(t.property, 32), 32, "YES", "NO"), decode(bitand(t.property, 64), 64, "IOT", decode(bitand(t.property, 512), 512, "IOT_OVERFLOW", decode(bitand(t.flags, 536870912), 536870912, "IOT_MAPPING", null
))),
decode(bitand(o.flags, 2), 0, "N", 2, "Y", "N"), decode(bitand(o.flags, 16), 0, "N", 16, "Y", "N"), decode(bitand(t.property, 8192), 8192, "YES", decode(bitand(t.property, 1), 0, "NO", "YES")), decode(bitand(o.flags, 2), 2, "DEFAULT", decode(s.cachehint, 0, "DEFAULT", 1, "KEEP", 2, "RECYCLE", NULL)), decode(bitand(t.flags, 131072), 131072, "ENABLED", "DISABLED"), decode(bitand(t.flags, 512), 0, "NO", "YES"), decode(bitand(t.flags, 256), 0, "NO", "YES"), decode(bitand(o.flags, 2), 0, NULL, decode(bitand(t.property, 8388608), 8388608, "SYS$SESSION", "SYS$TRANSACTION")), decode(bitand(t.flags, 1024), 1024, "ENABLED", "DISABLED"), decode(bitand(o.flags, 2), 2, "NO", decode(bitand(t.property, 2147483648), 2147483648, "NO", decode(ksppcv.ksppstvl, "TRUE", "YES", "NO"))), decode(bitand(t.property, 1024), 0, null, cu.name), decode(bitand(t.flags, 8388608), 8388608, "ENABLED", "DISABLED"), decode(bitand(t.property, 32), 32, null, decode(bitand(s.spare1, 2048), 2048, "ENABLED", "DISABLED")), decode(bitand(o.flags, 128), 128, "YES", "NO")
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv("SCHEMAID")
and o.obj# = t.obj# and bitand(t.property, 1) = 0 and bitand(o.flags, 128) = 0 and t.bobj# = co.obj# (+) and t.ts# = ts.ts# and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and t.dataobj# = cx.obj# (+) and cx.owner# = cu.user# (+) and ksppi.indx = ksppcv.indx and ksppi.ksppinm = "_dml_monitoring_enabled"
SQL> select text
2 from all_views 3 where view_name = "USER_TABLES";
TEXT
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name), decode((bitand(t.property, 512)+bitand(t.flags, 536870912)), 0, null, co.name), decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"), decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)), decode(bitand(t.property, 32), 0, t.initrans, null), decode(bitand(t.property, 32), 0, t.maxtrans, null), s.iniexts * ts.blocksize, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize), s.minexts, s.maxexts, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extpct), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))), decode(bitand(t.property, 32+64), 0, decode(bitand(t.flags, 32), 0, "YES", "NO"), null), decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"), t.rowcnt, decode(bitand(t.property, 64), 0, t.blkcnt, null), decode(bitand(t.property, 64), 0, t.empcnt, null), decode(bitand(t.property, 64), 0, t.avgspc, null), t.chncnt, t.avgrln, t.avgspc_flb, decode(bitand(t.property, 64), 0, t.flbcnt, null), lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10), lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10), lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5), decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"), t.samplesize, t.analyzetime, decode(bitand(t.property, 32), 32, "YES", "NO"), decode(bitand(t.property, 64), 64, "IOT", decode(bitand(t.property, 512), 512, "IOT_OVERFLOW", decode(bitand(t.flags, 536870912), 536870912, "IOT_MAPPING", null
))),
decode(bitand(o.flags, 2), 0, "N", 2, "Y", "N"), decode(bitand(o.flags, 16), 0, "N", 16, "Y", "N"), decode(bitand(t.property, 8192), 8192, "YES", decode(bitand(t.property, 1), 0, "NO", "YES")), decode(bitand(o.flags, 2), 2, "DEFAULT", decode(s.cachehint, 0, "DEFAULT", 1, "KEEP", 2, "RECYCLE", NULL)), decode(bitand(t.flags, 131072), 131072, "ENABLED", "DISABLED"), decode(bitand(t.flags, 512), 0, "NO", "YES"), decode(bitand(t.flags, 256), 0, "NO", "YES"), decode(bitand(o.flags, 2), 0, NULL, decode(bitand(t.property, 8388608), 8388608, "SYS$SESSION", "SYS$TRANSACTION")), decode(bitand(t.flags, 1024), 1024, "ENABLED", "DISABLED"), decode(bitand(o.flags, 2), 2, "NO", decode(bitand(t.property, 2147483648), 2147483648, "NO", decode(ksppcv.ksppstvl, "TRUE", "YES", "NO"))), decode(bitand(t.property, 1024), 0, null, cu.name), decode(bitand(t.flags, 8388608), 8388608, "ENABLED", "DISABLED"), decode(bitand(t.property, 32), 32, null, decode(bitand(s.spare1, 2048), 2048, "ENABLED", "DISABLED")), decode(bitand(o.flags, 128), 128, "YES", "NO")
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv("SCHEMAID")
and o.obj# = t.obj# and bitand(t.property, 1) = 0 and bitand(o.flags, 128) = 0 and t.bobj# = co.obj# (+) and t.ts# = ts.ts# and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and t.dataobj# = cx.obj# (+) and cx.owner# = cu.user# (+) and ksppi.indx = ksppcv.indx and ksppi.ksppinm = "_dml_monitoring_enabled"
SQL> / TEXT
select o.name, decode(bitand(t.property, 2151678048), 0, ts.name, null),
decode(bitand(t.property, 1024), 0, null, co.name), decode((bitand(t.property, 512)+bitand(t.flags, 536870912)), 0, null, co.name), decode(bitand(t.trigflag, 1073741824), 1073741824, "UNUSABLE", "VALID"), decode(bitand(t.property, 32+64), 0, mod(t.pctfree$, 100), 64, 0, null), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(t.property, 32+64), 0, t.pctused$, 64, 0, null)), decode(bitand(t.property, 32), 0, t.initrans, null), decode(bitand(t.property, 32), 0, t.maxtrans, null), s.iniexts * ts.blocksize, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extsize * ts.blocksize), s.minexts, s.maxexts, decode(bitand(ts.flags, 3), 1, to_number(NULL), s.extpct), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.lists, 0, 1, s.lists))), decode(bitand(ts.flags, 32), 32, to_number(NULL), decode(bitand(o.flags, 2), 2, 1, decode(s.groups, 0, 1, s.groups))), decode(bitand(t.property, 32+64), 0, decode(bitand(t.flags, 32), 0, "YES", "NO"), null), decode(bitand(t.flags,1), 0, "Y", 1, "N", "?"), t.rowcnt, decode(bitand(t.property, 64), 0, t.blkcnt, null), decode(bitand(t.property, 64), 0, t.empcnt, null), decode(bitand(t.property, 64), 0, t.avgspc, null), t.chncnt, t.avgrln, t.avgspc_flb, decode(bitand(t.property, 64), 0, t.flbcnt, null), lpad(decode(t.degree, 32767, "DEFAULT", nvl(t.degree,1)),10), lpad(decode(t.instances, 32767, "DEFAULT", nvl(t.instances,1)),10), lpad(decode(bitand(t.flags, 8), 8, "Y", "N"),5), decode(bitand(t.flags, 6), 0, "ENABLED", "DISABLED"), t.samplesize, t.analyzetime, decode(bitand(t.property, 32), 32, "YES", "NO"), decode(bitand(t.property, 64), 64, "IOT", decode(bitand(t.property, 512), 512, "IOT_OVERFLOW", decode(bitand(t.flags, 536870912), 536870912, "IOT_MAPPING", null
))),
decode(bitand(o.flags, 2), 0, "N", 2, "Y", "N"), decode(bitand(o.flags, 16), 0, "N", 16, "Y", "N"), decode(bitand(t.property, 8192), 8192, "YES", decode(bitand(t.property, 1), 0, "NO", "YES")), decode(bitand(o.flags, 2), 2, "DEFAULT", decode(s.cachehint, 0, "DEFAULT", 1, "KEEP", 2, "RECYCLE", NULL)), decode(bitand(t.flags, 131072), 131072, "ENABLED", "DISABLED"), decode(bitand(t.flags, 512), 0, "NO", "YES"), decode(bitand(t.flags, 256), 0, "NO", "YES"), decode(bitand(o.flags, 2), 0, NULL, decode(bitand(t.property, 8388608), 8388608, "SYS$SESSION", "SYS$TRANSACTION")), decode(bitand(t.flags, 1024), 1024, "ENABLED", "DISABLED"), decode(bitand(o.flags, 2), 2, "NO", decode(bitand(t.property, 2147483648), 2147483648, "NO", decode(ksppcv.ksppstvl, "TRUE", "YES", "NO"))), decode(bitand(t.property, 1024), 0, null, cu.name), decode(bitand(t.flags, 8388608), 8388608, "ENABLED", "DISABLED"), decode(bitand(t.property, 32), 32, null, decode(bitand(s.spare1, 2048), 2048, "ENABLED", "DISABLED")), decode(bitand(o.flags, 128), 128, "YES", "NO")
from sys.ts$ ts, sys.seg$ s, sys.obj$ co, sys.tab$ t, sys.obj$ o,
sys.obj$ cx, sys.user$ cu, x$ksppcv ksppcv, x$ksppi ksppi
where o.owner# = userenv("SCHEMAID")
and o.obj# = t.obj# and bitand(t.property, 1) = 0 and bitand(o.flags, 128) = 0 and t.bobj# = co.obj# (+) and t.ts# = ts.ts# and t.file# = s.file# (+) and t.block# = s.block# (+) and t.ts# = s.ts# (+) and t.dataobj# = cx.obj# (+) and cx.owner# = cu.user# (+) and ksppi.indx = ksppcv.indx and ksppi.ksppinm = "_dml_monitoring_enabled"
SQL> SQL></source>
What is a LONG type
The LONG datatype in PL/SQL is just like VARCHAR2.
The LONG datatype can store a maximum of 32760 bytes instead of 32767.
The LONG datatype is actually 7 bytes less than the VARCHAR2 type.
The Syntax for the LONG Datatype
<source lang="sql">
variable_name LONG(size);</source>