Oracle Query Logic Declare and With

April 22, 2022

I want to printout a Dynamic built query. Yet I am stuck at variable declaration; Error At line 2.
I need the maximum size for these VARCHAR2 variables.

Do I have a good overall structure ?

I use the result of the WITH inside the dynamic query.

DECLARE l_sql_query VARCHAR2(2000);
        l_sql_queryFinal VARCHAR2(2000);
        
    
with cntp as (select distinct 
cnt.code code_container,
*STUFF*
FROM container cnt

WHERE 

cnt.status !='DESTROYED'
order by cnt.code)

BEGIN
FOR l_counter IN 2022..2032
LOOP
    l_sql_query := l_sql_query || 'SELECT cntp.code_container *STUFF*
FROM cntp 
GROUP BY cntp.code_container ,cntp.label_container, cntp.Plan_Classement, Years
HAVING
cntp.Years=' || l_counter ||'
AND
/*stuff*/ TO_DATE(''31/12/' || l_counter ||''',''DD/MM/YYYY'')
AND SUM(cntp.IsA)=0
AND SUM(cntp.IsB)=0

UNION
';

END LOOP;
END;

l_sql_queryFinal := SUBSTR(l_sql_query,  0,  LENGTH (l_sql_query) – 5);
l_sql_queryFinal := l_sql_queryFinal||';'

dbms_output.put_line(l_sql_queryFinal);

>Solution :

The code you posted has quite a few issues, among them:

you’ve got the with (CTE) as a standlone fragment in the declare section, which isn’t valid. If you want it to be part of the dynamic string then put it in the string;
your END; is in the wrong place;
you have – instead of -;
you remove the last 5 characters, but you end with a new line, so you need to remove 6 to include the U of the last UNION;
the line that appens a semicolon is itself missing one (though for dynamic SQL you usually don’t want a semicolon, so the whole line can probably be removed);
2000 characters is too small for your example, but it’s OK with the actual maximum of 32767.

DECLARE
  l_sql_query VARCHAR2(32767);
  l_sql_queryFinal VARCHAR2(32767);
BEGIN
  -- initial SQL which just declares the CTE
  l_sql_query := q'^
with cntp as (select distinct 
cnt.code code_container,
*STUFF*
FROM container cnt

WHERE 

cnt.status !='DESTROYED'
order by cnt.code)

^';

  -- loop around each year...
  FOR l_counter IN 2022..2032
  LOOP
    l_sql_query := l_sql_query || 'SELECT cntp.code_container *STUFF*
FROM cntp 
GROUP BY cntp.code_container ,cntp.label_container, cntp.Plan_Classement, Years
HAVING
cntp.Years=' || l_counter ||'
AND
MAX(TO_DATE(cntp.DISPOSITION_DATE,''DD/MM/YYYY'')) BETWEEN TO_DATE(''01/01/'|| l_counter ||''',''DD/MM/YYYY'') AND TO_DATE(''31/12/' || l_counter ||''',''DD/MM/YYYY'')
AND SUM(cntp.IsA)=0
AND SUM(cntp.IsB)=0

UNION
';

  END LOOP;

  l_sql_queryFinal := SUBSTR(l_sql_query,  0,  LENGTH (l_sql_query) - 6);
  l_sql_queryFinal := l_sql_queryFinal||';';

  dbms_output.put_line(l_sql_queryFinal);
END;
/

db<>fiddle

The q[^...^] in the first assignment is the alternative quoting mechanism, which means you don’t have to escape (by doubling-up) the quotes within that string, around 'DESTYORED'. Notice the ^ delimiters do not appear in the final generated query.

Whether the generated query actually does what you want is another matter… The cntp.Years= part should probably be in a where clause, not having; and you might be able to simplify this to a single query instead of lots of unions, as you’re already aggregating. All of that is a bit beyond the scope of your question though.