Removing 'union all' at end of last record in dynamic query

Question

For my question I have set up a simple example to illustrate my problem.

Let's say you have a dynamic query that generates several select statements with a UNION ALL between them. Is there a way to prevent the 'UNION ALL' at the end of the last record from appearing?

My example:

CREATE OR REPLACE PROCEDURE PROC_TEST AS 
BEGIN
    DECLARE 
        DDL_STRING CLOB;
    BEGIN
        FOR x IN (SELECT TABLE_NAME FROM HLP_TABLES WHERE ENABLED = 1)
        LOOP
            DDL_STRING := 'SELECT ID FROM ' || x.TABLE_NAME || ' UNION ALL ';
            DBMS_OUTPUT.PUT_LINE(DDL_STRING);
        END LOOP;
    END;
END PROC_TEST;

offtopic: why did you embed an anonymous block in this procedure? — Jeffrey Kemp, Nov 27 '18 at 02:53

score 3 · Answer 1 · answered Nov 14 '18 at 13:19

If you are sure that the total length of ddl doesn't exceed 4000 characters, you may use LISTAGG and avoid loops.

CREATE OR REPLACE PROCEDURE proc_test
     AS
     DECLARE
          ddl_string   CLOB;
     BEGIN
          SELECT
               LISTAGG('SELECT ID FROM ' || table_name,' UNION ALL ') WITHIN GROUP(
                    ORDER BY table_name)
          INTO ddl_string 
          FROM HLP_TABLES
     WHERE enabled = 1;
          dbms_output.put_line(ddl_string);
END proc_test;
/

score 2 · Accepted Answer · answered Nov 14 '18 at 12:27

2

Do this:

CREATE OR REPLACE PROCEDURE PROC_TEST AS 
   DDL_STRING CLOB;
BEGIN
    FOR x IN (SELECT TABLE_NAME FROM HLP_TABLES WHERE ENABLED = 1)
    LOOP
        DDL_STRING := DDL_STRING||' SELECT ID FROM ' || x.TABLE_NAME || ' UNION ALL';
    END LOOP;
    DDL_STRING := REGEXP_REPLACE(DDL_STRING, ' UNION ALL$');
    DBMS_OUTPUT.PUT_LINE(DDL_STRING);
END PROC_TEST;

answered Nov 14 '18 at 12:27

Wernfried Domscheit

54,457
9
76
110

Brilliant! Simple and effective :) Thank you! – brother Nov 14 '18 at 13:31

score 1 · Answer 3 · answered Nov 14 '18 at 13:33

You can use counter in the loop to determine whether you need UNION ALL or not. try this :

  CREATE OR REPLACE PROCEDURE PROC_TEST AS
  BEGIN
    DECLARE
      v_counter  number := 1;
      DDL_STRING CLOB;
    BEGIN
      FOR x IN (SELECT TABLE_NAME FROM HLP_TABLES WHERE ENABLED = 1) LOOP

        DDL_STRING := case
                        when v_counter > 1 then
                         ' UNION ALL ' end || ' SELECT ID FROM ' || x.TABLE_NAME;
        DBMS_OUTPUT.PUT_LINE(DDL_STRING);
        v_counter := v_counter + 1;
      END LOOP;
    END;
  END PROC_TEST;

score 0 · Answer 4 · answered Nov 27 '18 at 02:56

When generating code like this I generally use a simple if statement, e.g.:

create or replace procedure proc_test as 
  ddl_string clob;
begin

  for x in (select table_name from hlp_tables where enabled = 1)
  loop

    if ddl_string is not null then
      ddl_string := ddl_string || ' union all ';
    end if;

    ddl_string := ddl_string || ' select id from ' || x.table_name;

  end loop;

  dbms_output.put_line(ddl_string);
end proc_test;

Removing 'union all' at end of last record in dynamic query

4 Answers4