Oracle Query Logic Declare and With

Question

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);

Answer 1

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.

Answer 2

Is there a way to put the maximum size "automaticcaly" like "VARCHAR2(MAX_STRING_SIZE) does it work ?

No. And no.

The maximum size of varchar2 in PL/SQL is 32767. If you want to hedge against that changing at some point in the future you can declare a user-defined subtype in a shared package ...

create or replace package my_subtypes as

  subtype max_string_size is varchar2(32767);

end my_subtypes;
/

... and reference that in your program...

DECLARE
  l_sql_query       my_subtypes.max_string_size;
  l_sql_queryFinal  my_subtypes.max_string_size;
...

So if Oracle subsequently raises the maximum permitted size of a VARCHAR2 in PL/SQL you need only change the definition of my_subtypes.max_string_size for the bounds to be raised wherever you used that subtype.

Alternatively, just use a CLOB. Oracle is pretty clever about treating a CLOB as a VARCHAR2 when its size is <= 32k.

To solve your other problem you need to treat the WITH clause as a string and assign it to your query variable.

l_sql_query       my_subtypes.max_string_size := q'[
with cntp as (select distinct 
cnt.code code_container,
*STUFF*
FROM container cnt
WHERE cnt.status !='DESTROYED'
order by cnt.code) ]';

Note the use of the special quote syntax q'[ ... ]' to avoid the need to escape the quotation marks in your query snippet.

---

A dynamic string query do not access a temp table ?

Dynamic SQL is a string containing a DML or DDL statement which we execute with EXECUTE IMMEDIATE or DBMS_SQL commands. Otherwise it is exactly the same as static SQL, it doesn't behave any differently. In fact the best way to write dynamic SQL is to start by writing the static statement in a worksheet, make it correct and then figure out which bits need to be dynamic (variables, placeholders) and which bits remain static (boilerplate). In your case the WITH clause is a static part of the statement.

Answer 3

As has already been pointed out, you seem to have misunderstood what a with clause is: it's a clause of a SQL statement, not a procedural declaration. My definition, it must be followed by select.

But also, as a general rule, I would recommend avoiding dynamic SQL when possible. In this case, if you can simulate a table with the range of years you want, you can join instead of having to run the same query multiple times.

The easy trick to doing that is to use Oracle's connect by syntax to use a recursive query to produce the expected number of rows.

Once you've done that, adding this table as a join pretty trivially:

WITH cntp AS
(
                SELECT DISTINCT code code_container,
                                [additional columns]
                FROM            container
                WHERE           status !='DESTROYED') cntc,
(
       SELECT to_date('01/01/'
                     || (LEVEL+2019), 'dd/mm/yyyy') AS start_date,
              to_date('31/12/'
                     || (LEVEL+2019), 'dd/mm/yyyy') AS end_date,
              (LEVEL+2019)                          AS year
       FROM   dual
              CONNECT BY LEVEL <= 11) year_table
SELECT   cntp.code_container,
         [additional columns]
FROM     cntp
join     year_table
ON       cntp.years = year_table.year
GROUP BY [additional columns],
         years,
         year_table.start_date,
         year_table.end_date
HAVING   max(to_date(cntp.disposition_date,''dd/mm/yyyy'')) BETWEEN year_table.start_date AND year_table.end_date
AND      SUM(cntp.isa)=0
AND      SUM(cntp.isb)=0

(This query is totally untested and may not actually fulfill your needs; I am providing my best approximation based on the information available.)