Oracle PL/SQL Tutorial/PL SQL Data Types/LONG — различия между версиями

Материал из SQL эксперт
Перейти к: навигация, поиск
м (1 версия)
 
м (1 версия)
 
(нет различий)

Текущая версия на 13:07, 26 мая 2010

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>

Источник — «http://sqle.ru/index.php?title=Oracle_PL/SQL_Tutorial/PL_SQL_Data_Types/LONG&oldid=3652»