CDC 10gR2 column list

Some time ago, I had a question on how to pass all the columns and data type to 
DBMS-CDC-PUBLISH.CREATE-CHANGE-TABLE.

Mark Van de Wiel had suggested writing PL/SQL and so I did.

Just thought I share in the event someone is struggling with the same issue. It 
sure helps when you have tables with 100 columns or so.

Need to GRANT SELECT ON DBA_TAB_COLS TO CDC
CREATE OR REPLACE FUNCTION get_tab_cols (p_owner VARCHAR2, p_table VARCHAR2) 
RETURN VARCHAR2
IS
 l_string     VARCHAR2 (4000);
 l_delimiter  VARCHAR(1):=',';
BEGIN
 FOR x IN (
    SELECT column_name || ' '
       || CASE WHEN data_type = 'DATE' THEN data_type
               WHEN data_type = 'NUMBER' AND data_precision IS NULL THEN 
data_type
               ELSE data_type || '('
          END
       || CASE WHEN data_type = 'VARCHAR2' THEN TO_CHAR (data_length)
               WHEN data_type = 'NUMBER' AND data_scale = 0 THEN TO_CHAR 
(data_precision)
               WHEN data_type = 'NUMBER' AND data_scale <> 0 THEN 
data_precision || ',' || data_scale
          END
       || CASE
             WHEN data_type = 'DATE' THEN NULL
             WHEN data_type = 'NUMBER' AND data_precision IS NULL THEN NULL
             ELSE ')'
          END col
    FROM dba_tab_cols c
    WHERE table_name = UPPER(p_table) AND owner = UPPER(p_owner)
    ORDER BY column_id asc)
  LOOP
      l_string := l_string||l_delimiter||x.col;
  END LOOP;
  RETURN LTRIM (l_string,l_delimiter);
END;
/

Michael Dinh : XIFIN

NOTICE OF CONFIDENTIALITY - This material is intended for the use of the 
individual or entity to which it is addressed, and may contain information that 
is privileged, confidential and exempt from disclosure under applicable laws.  
BE FURTHER ADVISED THAT THIS EMAIL MAY CONTAIN PROTECTED HEALTH INFORMATION 
(PHI). BY ACCEPTING THIS MESSAGE, YOU ACKNOWLEDGE THE FOREGOING, AND AGREE AS 
FOLLOWS: YOU AGREE TO NOT DISCLOSE TO ANY THIRD PARTY ANY PHI CONTAINED HEREIN, 
EXCEPT AS EXPRESSLY PERMITTED AND ONLY TO THE EXTENT NECESSARY TO PERFORM YOUR 
OBLIGATIONS RELATING TO THE RECEIPT OF THIS MESSAGE.  If the reader of this 
email (and attachments) is not the intended recipient, you are hereby notified 
that any dissemination, distribution or copying of this communication is 
strictly prohibited. Please notify the sender of the error and delete the 
e-mail you received. Thank you.

Other related posts:

  • » CDC 10gR2 column list - Michael Dinh