Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- case record.col_type
- when dbms_types.TYPCODE_DATE then 'DATE'
- when dbms_types.TYPCODE_NUMBER then 'NUMBER'
- when dbms_types.TYPCODE_RAW then 'RAW'
- when dbms_types.TYPCODE_CHAR then 'CHAR'
- when dbms_types.TYPCODE_VARCHAR then 'VARCHAR2'
- when dbms_types.TYPCODE_VARCHAR then 'VARCHAR'
- when dbms_types.TYPCODE_MLSLABEL then 'MLSLABEL'
- when dbms_types.TYPCODE_BLOB then 'BLOB'
- when dbms_types.TYPCODE_BFILE then 'BFILE'
- when dbms_types.TYPCODE_CLOB then 'CLOB'
- when dbms_types.TYPCODE_CFILE then 'CFILE'
- when dbms_types.TYPCODE_TIMESTAMP then 'TIMESTAMP'
- when dbms_types.TYPCODE_TIMESTAMP_TZ then 'TIMESTAMP_TZ'
- when dbms_types.TYPCODE_TIMESTAMP_LTZ then 'TIMESTAMP_LTZ'
- when dbms_types.TYPCODE_INTERVAL_YM then 'INTERVAL_YM'
- when dbms_types.TYPCODE_INTERVAL_DS then 'INTERVAL_DS'
- when dbms_types.TYPCODE_REF then 'REF'
- when dbms_types.TYPCODE_OBJECT then 'OBJECT'
- when dbms_types.TYPCODE_VARRAY then 'VARRAY'
- when dbms_types.TYPCODE_TABLE then 'TABLE'
- when dbms_types.TYPCODE_NAMEDCOLLECTION then 'NAMEDCOLLECTION'
- when dbms_types.TYPCODE_OPAQUE then 'OPAQUE'
- when dbms_types.TYPCODE_NCHAR then 'NCHAR'
- when dbms_types.TYPCODE_NVARCHAR then 'NVARCHAR2'
- when dbms_types.TYPCODE_NCLOB then 'NCLOB'
- when dbms_types.TYPCODE_BFLOAT then 'BFLOAT'
- when dbms_types.TYPCODE_BDOUBLE then 'BDOUBLE'
- when dbms_types.TYPCODE_UROWID then 'UROWID'
- end case
- select text from all_source
- where owner='SYS'
- and name='DBMS_TYPES'
- and type='PACKAGE';
- PACKAGE dbms_types AS
- TYPECODE_DATE PLS_INTEGER := 12;
- TYPECODE_NUMBER PLS_INTEGER := 2;
- TYPECODE_RAW PLS_INTEGER := 95;
- TYPECODE_CHAR PLS_INTEGER := 96;
- TYPECODE_VARCHAR2 PLS_INTEGER := 9;
- TYPECODE_VARCHAR PLS_INTEGER := 1;
- TYPECODE_MLSLABEL PLS_INTEGER := 105;
- TYPECODE_BLOB PLS_INTEGER := 113;
- TYPECODE_BFILE PLS_INTEGER := 114;
- TYPECODE_CLOB PLS_INTEGER := 112;
- TYPECODE_CFILE PLS_INTEGER := 115;
- TYPECODE_TIMESTAMP PLS_INTEGER := 187;
- TYPECODE_TIMESTAMP_TZ PLS_INTEGER := 188;
- TYPECODE_TIMESTAMP_LTZ PLS_INTEGER := 232;
- TYPECODE_INTERVAL_YM PLS_INTEGER := 189;
- TYPECODE_INTERVAL_DS PLS_INTEGER := 190;
- TYPECODE_REF PLS_INTEGER := 110;
- TYPECODE_OBJECT PLS_INTEGER := 108;
- TYPECODE_VARRAY PLS_INTEGER := 247; /* COLLECTION TYPE */
- TYPECODE_TABLE PLS_INTEGER := 248; /* COLLECTION TYPE */
- TYPECODE_NAMEDCOLLECTION PLS_INTEGER := 122;
- TYPECODE_OPAQUE PLS_INTEGER := 58; /* OPAQUE TYPE */
- /* NOTE: These typecodes are for use in AnyData api only and are short forms
- for the corresponding char typecodes with a charset form of SQLCS_NCHAR.
- */
- TYPECODE_NCHAR PLS_INTEGER := 286;
- TYPECODE_NVARCHAR2 PLS_INTEGER := 287;
- TYPECODE_NCLOB PLS_INTEGER := 288;
- /* Typecodes for Binary Float, Binary Double and Urowid. */
- TYPECODE_BFLOAT PLS_INTEGER := 100;
- TYPECODE_BDOUBLE PLS_INTEGER := 101;
- TYPECODE_UROWID PLS_INTEGER := 104;
- SUCCESS PLS_INTEGER := 0;
- NO_DATA PLS_INTEGER := 100;
- SQL> DECLARE
- 2 v_anydata ANYDATA := ANYDATA.ConvertVarchar2('String');
- 3 v_typename VARCHAR2(128);
- 4 BEGIN
- 5 v_typename := v_anydata.GetTypeName();
- 6 DBMS_OUTPUT.PUT_LINE('Type of ANYDATA instance is [' || v_typename || ']');
- 7 END;
- 8 /
- Type of ANYDATA instance is [SYS.VARCHAR2]
- PL/SQL procedure successfully completed.
- select
- distinct
- c.type# type_code,
- decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
- 2, decode(c.scale, null, decode(c.precision#, null, 'NUMBER', 'FLOAT'), 'NUMBER'),
- 8, 'LONG',
- 9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
- 12, 'DATE',
- 23, 'RAW', 24, 'LONG RAW',
- 69, 'ROWID',
- 96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
- 100, 'BINARY_FLOAT',
- 101, 'BINARY_DOUBLE',
- 105, 'MLSLABEL',
- 106, 'MLSLABEL',
- 111, 'REF',
- 112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
- 113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
- 121, 'USER_TYPE',
- 122, 'USER_TYPE',
- 123, 'USER_TYPE',
- 178, 'TIME(' ||c.scale|| ')',
- 179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
- 180, 'TIMESTAMP(' ||c.scale|| ')',
- 181, 'TIMESTAMP(' ||c.scale|| ')'||' WITH TIME ZONE',
- 231, 'TIMESTAMP(' ||c.scale|| ')'||' WITH LOCAL TIME ZONE',
- 182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
- 183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
- c.scale || ')',
- 208, 'UROWID',
- 'UNDEFINED') type_name from sys.col$ c order by c.type#;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement