CDC 10gR2 column list
- From: Michael Dinh <mdinh@xxxxxxxxx>
- To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 17 Sep 2010 16:00:12 -0700
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