Cacher is the code snippet organizer for pro developers

      sql_text := 'SELECT data_type || CASE
               WHEN data_precision IS NOT NULL
                    AND nvl(data_scale, 0) > 0 THEN
                ''('' || data_precision || '','' || data_scale || '')''
               WHEN data_precision IS NOT NULL
                    AND nvl(data_scale, 0) = 0 THEN
                ''('' || data_precision || '')''
               WHEN data_precision IS NULL
                    AND data_scale IS NOT NULL THEN
                ''(*,'' || data_scale || '')''
               WHEN char_length > 0 THEN
                ''('' || char_length || CASE char_used
                  WHEN ''B'' THEN
                   '' Byte''
                  WHEN ''C'' THEN
                   '' Char''
                  ELSE
                   NULL
                END || '')''
             END
        FROM dba_tab_cols
       WHERE owner = :in_table_owner
         AND table_name = UPPER(:in_table_name)
         AND column_name = UPPER(:in_pk_field)';
    
      EXECUTE IMMEDIATE sql_text
        INTO pk_type
        USING in_table_owner, in_table_name, in_pk_field;