How to get the length of VARCHAR2 attribute declared in Oracle Object Type using OracleTypeCHAR metadata, regardless of database character set

Question

My Java application depends on the metadata of Oracle object types. I am using a class oracle.jdbc.oracore.OracleTypeCHARto access this metadata using JDBC. After converting the database to charset, the AL32UTF8method OracleTypeCHAR#getLength()returns 4 times times the results declared in the Oracle Object Type - for example:

some_attribute varchar2(10)

The length will appear 40when the length was returned with the previous encoding 10. Is there a way to get the source value from PL / SQL without parsing the OOT source code?

Answer 1

user_tab_columns.CHAR_LENGTH,

select column_name, comments from all_col_comments where table_name = 'USER_TAB_COLUMNS';

COLUMN_NAME          COMMENTS

TABLE_NAME           Table, view or cluster name
COLUMN_NAME          Column name
DATA_TYPE            Datatype of the column
DATA_TYPE_MOD        Datatype modifier of the column
DATA_TYPE_OWNER      Owner of the datatype of the column
DATA_LENGTH          Length of the column in bytes
DATA_PRECISION       Length: decimal digits (NUMBER) or binary digits (FLOAT)
DATA_SCALE           Digits to right of decimal point in a number
NULLABLE             Does column allow NULL values?
COLUMN_ID            Sequence number of the column as created
DEFAULT_LENGTH       Length of default value for the column
DATA_DEFAULT         Default value for the column
NUM_DISTINCT         The number of distinct values in the column
LOW_VALUE            The low value in the column
HIGH_VALUE           The high value in the column
DENSITY              The density of the column
NUM_NULLS            The number of nulls in the column
NUM_BUCKETS          The number of buckets in histogram for the column
LAST_ANALYZED        The date of the most recent time this column was analyzed
SAMPLE_SIZE          The sample size used in analyzing this column
CHARACTER_SET_NAME   Character set name
CHAR_COL_DECL_LENGTH Declaration length of character type column
GLOBAL_STATS         Are the statistics calculated without merging underlying partitions?
USER_STATS           Were the statistics entered directly by the user?
AVG_COL_LEN          The average length of the column in bytes
CHAR_LENGTH          The maximum length of the column in characters
CHAR_USED            C is maximum length given in characters, B if in bytes
V80_FMT_IMAGE        Is column data in 8.0 image format?
DATA_UPGRADED        Has column data been upgraded to the latest type version format?
HISTOGRAM

, ,

select
  char_length
from
  user_tab_columns
where
  table_name = 'T' and column_name = 'SOME_ATTRIBUTE';