Hello All,
I am running below procedure which will generate select statements dynamically for all table names in table MT.
Though sql script is working fine , however I am facing ORA-06550 when trying to execute sql block with a procedure as below.
Error is at line 18 "WITH r.table_name AS (...".
Any work around?
declare
sql_str clob;
begin
for r in (
select name as table_name
from MT
)
loop
WITH r.table_name AS (
SELECT COLUMN_NAME,
COLUMN_ID,
CASE
WHEN COLUMN_NAME LIKE 'FC%' THEN 2
WHEN COLUMN_NAME LIKE 'SV%' THEN 3
ELSE 1
END GROUP_ID
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = r.table_name
UNION ALL
SELECT NULL,
NULL,
4 GROUP_ID
FROM DUAL
UNION ALL
SELECT COLUMN_NAME,
COLUMN_ID,
5 GROUP_ID
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = r.table_name
AND COLUMN_NAME LIKE 'FC%'
UNION ALL
SELECT NULL,
NULL,
6 GROUP_ID
FROM DUAL
UNION ALL
SELECT COLUMN_NAME,
COLUMN_ID,
CASE
WHEN COLUMN_NAME LIKE 'FC%' THEN 8
ELSE 7
END GROUP_ID
FROM USER_TAB_COLUMNS
WHERE TABLE_NAME = r.table_name
AND COLUMN_NAME NOT LIKE 'SV%'
),
T2 AS (
SELECT COLUMN_NAME,
GROUP_ID,
ROW_NUMBER() OVER(
PARTITION BY GROUP_ID
ORDER BY COLUMN_ID
) RN
FROM r.table_name
),
T3 AS (
SELECT GROUP_ID,
RN,
CASE GROUP_ID
WHEN 1 THEN ' ' || COLUMN_NAME || ','
WHEN 2 THEN ' ALIAS' || RN || '.CODE,' || CHR(10) ||
' ALIAS' || RN || '.DESCRIPTION,'
WHEN 3 THEN ' LISTAGG(' || COLUMN_NAME || Q'[,',') WITHIN GROUP(ORDER BY ]' ||
COLUMN_NAME || '),'
WHEN 4 THEN ' FROM '|| r.table_name
WHEN 5 THEN ' LEFT OUTER JOIN T2 ALIAS' || RN || ' ON '||r.table_name||'.' || COLUMN_NAME
WHEN 6 THEN ' GROUP BY'
WHEN 7 THEN ' ' || COLUMN_NAME || ','
ELSE ' ALIAS' || RN || '.CODE,' || CHR(10) ||
' ALIAS' || RN || '.DESCRIPTION,'
END CHUNK
FROM T2
)
sql_str := SELECT 'SELECT' || SUBSTR(
RTRIM(
REPLACE(
REPLACE(
RTRIM(
XMLCAST(
XMLAGG(
XMLELEMENT(E,CHUNK,CHR(10))
ORDER BY GROUP_ID,RN
)
AS CLOB
),
','
),
',' || CHR(10) || ' FROM '|| r.table_name,
CHR(10) || ' FROM '|| r.table_name
),
' GROUP BY' || CHR(10) || ' ',
' GROUP BY '
),
CHR(10) || ','
),
7
) SQL
FROM T3;
dbms_output.put_line(sql_str);
end loop;
end;