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):
TEXT
DROP VIEW "F_VIEW"
DROP VIEW "VAR_ZYKLEN"
DROP VIEW "VAR_ZYK_BUKRS"
DROP VIEW "VAR_ZYK_MAX"
DROP VIEW "VAR_ZYK_MAX_BUKRS"
DROP VIEW "VAR_ZYK_VERSION"
DROP VIEW "VZYKLEN"
DROP VIEW "VZYKLUS_MAX"
DROP VIEW "VZYKLUS_MAX_BUKRS"
DROP VIEW "VZYK_BUKRS"
DROP VIEW "VZYK_VERSION"
|
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:
select 'DROP VIEW "' || view_name || '";' as TEXT from user_views
|
Leading to a result like this:
TEXT
DROP VIEW "F_VIEW";
DROP VIEW "VAR_ZYKLEN";
DROP VIEW "VAR_ZYK_BUKRS";
DROP VIEW "VAR_ZYK_MAX";
DROP VIEW "VAR_ZYK_MAX_BUKRS";
DROP VIEW "VAR_ZYK_VERSION";
DROP VIEW "VZYKLEN";
DROP VIEW "VZYKLUS_MAX";
DROP VIEW "VZYKLUS_MAX_BUKRS";
DROP VIEW "VZYK_BUKRS";
DROP VIEW "VZYK_VERSION";
|
In MaxDB the end-of-command delimiter is somewhat more complicated: it's the sequence of 'newline' + '//' ('newline' can be read as ASCII value 13)
So how do we get a newline with two slashes after each SQL command?
Pretty much straight forward!
First of all we need to select a line containing '//' for each command. This would look like this than:
select '//' as TEXT from user_views
|
Giving us the exact same number of rows as our original query:
TEXT
//
//
//
//
//
//
//
//
//
//
//
|
Now we need to have both the command lines and the slash lines together - we use the UNION ALL command for this and get the following result:
select 'DROP VIEW "' & view_name & '" ' as TEXT from user_views
union all
select '//' as TEXT from user_views
|
TEXT
//
//
//
//
//
//
//
//
//
//
//
DROP VIEW "F_VIEW"
DROP VIEW "VZYKLEN"
DROP VIEW "VAR_ZYKLEN"
DROP VIEW "VZYK_BUKRS"
DROP VIEW "VAR_ZYK_MAX"
DROP VIEW "VZYKLUS_MAX"
DROP VIEW "VZYK_VERSION"
DROP VIEW "VAR_ZYK_BUKRS"
DROP VIEW "VAR_ZYK_VERSION"
DROP VIEW "VAR_ZYK_MAX_BUKRS"
DROP VIEW "VZYKLUS_MAX_BUKRS"
|
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:
select rowno as line, 'DROP VIEW "' & view_name & '" ' as TEXT from user_views
union all
select rowno as line, '//' as TEXT from user_views
|
Running this statement unfortunately leads to an error message:
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 |
This gives us the following result:
ROWNO LINE TEXT
1 1 //
12 1 DROP VIEW "F_VIEW"
4 2 //
14 2 DROP VIEW "VAR_ZYKLEN"
5 3 //
19 3 DROP VIEW "VAR_ZYK_BUKRS"
6 4 //
16 4 DROP VIEW "VAR_ZYK_MAX"
7 5 //
21 5 DROP VIEW "VAR_ZYK_MAX_BUKRS"
8 6 //
20 6 DROP VIEW "VAR_ZYK_VERSION"
9 7 //
13 7 DROP VIEW "VZYKLEN"
10 8 //
18 8 DROP VIEW "VZYKLUS_MAX"
11 9 //
22 9 DROP VIEW "VZYKLUS_MAX_BUKRS"
2 10 //
15 10 DROP VIEW "VZYK_BUKRS"
3 11 //
17 11 DROP VIEW "VZYK_VERSION"
|
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 |
TEXT
DROP VIEW "F_VIEW"
//
DROP VIEW "VAR_ZYKLEN"
//
DROP VIEW "VAR_ZYK_BUKRS"
//
DROP VIEW "VAR_ZYK_MAX"
//
DROP VIEW "VAR_ZYK_MAX_BUKRS"
//
DROP VIEW "VAR_ZYK_VERSION"
//
DROP VIEW "VZYKLEN"
//
DROP VIEW "VZYKLUS_MAX"
//
DROP VIEW "VZYKLUS_MAX_BUKRS"
//
DROP VIEW "VZYK_BUKRS"
//
DROP VIEW "VZYK_VERSION"
//
|
Although it does not look obvious at first sight, it's not too difficult to adapt established DBA/Developer habits from Oracle to MaxDB.
Best regards,
Lars
