Hi,
We need to extarct DDL for few tables like column name, data type, size, primary key , foreign key constraints .
We tried sql query approach but due to data type size limitation not able to achieve it using sql query.
Like below query is giving appropriate result for PER_PEOPLE_GROUPS but failing for PER_ALL_ASSIGNMENTS_M.
WITH pk_columns AS (
SELECT acc.column_name
FROM all_cons_columns acc
JOIN all_constraints ac ON acc.constraint_name = ac.constraint_name
WHERE ac.constraint_type = 'P'
AND UPPER(ac.table_name) = UPPER(:p_table_name)
AND UPPER(ac.owner) = UPPER(:p_owner)
)
SELECT 'CREATE TABLE ' || :p_owner || '.' || :p_table_name || ' (' ||
RTRIM(
DBMS_LOB.SUBSTR(
XMLAGG(
XMLELEMENT(e,
atc.column_name || ' ' || atc.data_type ||
CASE
WHEN (atc.CHAR_LENGTH > 0) THEN '(' || atc.CHAR_LENGTH || ')'
ELSE ''
END || ', '
).EXTRACT('//text()') ORDER BY atc.column_id
).GETCLOBVAL(), 4000, 1
), ', '
) ||
CASE
WHEN COUNT(pk.column_name) > 0 THEN
', PRIMARY KEY (' ||
RTRIM(
DBMS_LOB.SUBSTR(
XMLAGG(
XMLELEMENT(e, pk.column_name || ', ')
.EXTRACT('//text()') ORDER BY pk.column_name
).GETCLOBVAL(), 4000, 1
), ', '
) ||
')'
ELSE ''
END || ');' AS Table_ddl
FROM all_tab_columns atc
LEFT JOIN pk_columns pk ON atc.column_name = pk.column_name
WHERE UPPER(atc.table_name) = UPPER(:p_table_name)
AND UPPER(atc.owner) = UPPER(:p_owner)
GROUP BY atc.table_name, atc.owner
Need your help here by providing some alternatives to how we can achieve this requirement i.e mean if we can use sql or webservice or any other method.