A customer was facing a problem when generating SQL scripts by using SQL. As this is a common habit in the Oracle world he tried to apply it to his MaxDB.

The script in this specific case should drop views from a specified schema. Anyhow the same technique may be used to change the default sample sizes of tables or perform update statistics on tables etc.

So the customers query looked like this:

select 'DROP VIEW "' || view_name || '" ' as TEXT from user_views

Running this command gives the following result (on my testdb of course):

Unfortunately to run this script there needs to be an end-of-command delimiter after each single DROP command. In Oracle this is the ‘;’ character and can be placed just behind the statement.
So the correct and easy statement in Oracle would look like this then:

Not too bad – but we need to have the slashes right after the SQL commands… so we need to order them. For that I employ the rowno (or rownum in SQL mode “ORACLE”) pseudo column. It adds a number to each row found, when it’s found.
As we have basically two queries here, both queries will get their own result row numbering:

General error;-7036 POS(90) ROWNO specification not allowed in this context

This error seems to occur because the two statements are unrestricted – they have no where clause. I could not yet figure out why this is an issue here, but the ROWNO implementation of MaxDB is a bit odd in general. So let’s workaround this by adding a dummy where clause:

select rowno, line, TEXT FROM
select rowno as line, 'DROP VIEW "' & view_name & '" ' as TEXT from user_views
where view_name is not null
union all
select rowno as line, '//' as TEXT from user_views
where view_name is not null
) order by line

As you can see, each sub-select got its own rowno a.k.a. line number so that we can use it to order the rows. For the UNION ALL the both single result set had to be materialized internally to deliver the rows for the outer query where I selected rowno again.
It’s pretty easy to spot, that the result rows of the outer query got their rowno before the resultset was sorted.

Leving out the ‘line’ and the ‘rowno’ fields and adding another sort by argument finally delivers a useable SQL script:

SELECT TEXT FROM (
select rowno as line, 'DROP VIEW "' & view_name & '" ' as TEXT from user_views
where view_name is not null
union all
select rowno as line, '//' as TEXT from user_views
where view_name is not null
) order by line, text desc