2. sqlplus -S SYSADM/SYSADM@10.138.1.2:1521/HR92UAT <<EOF>>oracle_to_ods_field_info.sql
    3.       SELECT 'var ' || '$' ||'{sync_table}' || ':tablepro=tablepro("' || '$' || '{sync_table}",Array(' " " FROM dual
    4.       UNION ALL
    5.       select  '"' || t.column_name || '"' || '->' || '"' || case
    6.                  when c.DATA_TYPE in ('CHAR',
    7.                                       'NCHAR',
    8.                                       'VARCHAR',
    9.                                       'VARCHAR2',
    10.                                       'NVARCHAR2',
    11.                                       'DATE',
    12.                                       'TIMESTAMP',
    13.                                       'TIMESTAMP WITH TIME ZONE',
    14.                                       'TIMESTAMP WITH LOCAL TIME ZONE',
    15.                                       'INTERVAL YEAR TO MOTH',
    16.                                       'INTERVAL DAY TO SECOND',
    17.                                       'BLOB',
    18.                                       'CLOB',
    19.                                       'NCLOB',
    20.                                       'BFILE',
    21.                                       'RAW',
    22.                                       'LONG RAW') then
    23.                   ' STRING '
    24.                  WHEN C.DATA_TYPE = 'INTEGER' THEN
    25.                   ' BIGINT '
    26.                  WHEN C.DATA_TYPE = 'NUMBER' THEN
    27.                   (CASE
    28.                     WHEN C.DATA_SCALE IS NOT NULL and c.DATA_SCALE<>0 THEN
    29.                      ' DECIMAL(' || C.DATA_PRECISION || ',' || C.DATA_SCALE || ') '
    30.                     WHEN C.DATA_PRECISION < 3 THEN
    31.                      ' TINYINT '
    32.                     WHEN C.DATA_PRECISION < 5 THEN
    33.                      ' SMALLINT '
    34.                     WHEN C.DATA_PRECISION < 10 THEN
    35.                      ' INT '
    36.                     ELSE
    37.                      ' BIGINT '
    38.                   END)
    39.                  WHEN C.DATA_TYPE IN
    40.                       ('BINARY_FLOAT', 'BINARY_DOUBLE', 'FLOAT') THEN
    41.                   ' DOUBLE '
    42.                  ELSE
    43.                   ' STRING '
    44.                END ||'"' ||
    45.                regexp_replace(t.comments,
    46.                               '[' || chr(10) || chr(13) || chr(9) || chr(32) || ']',
    47.                               '') || ','  " "
    48.           from all_COL_COMMENTS t, all_TAB_COLUMNS c
    49.          where c.column_name = t.column_name
    50.            and c.owner = t.owner
    51.            and c.TABLE_NAME = t.TABLE_NAME
    52.            and c.owner = 'SYSADM'
    53.            and c.TABLE_NAME = '${sync_table}'
    54.          UNION ALL 
    55.          SELECT '))' FROM dual;
    56. EOF