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.
