How to generate DDL wrapped in PL/SQL?

[Former Member]

My company is looking to do continuous integration / continuous deployment of database code.  For example, when we generate the DDL for, say, a CREATE TABLE statement, we want to make the code smart enough to issue a DROP only if the table already exists in the database.  That is, want to generate the following for a table DROP:

DECLARE

  TABLE_EXISTS NUMBER;

BEGIN

SELECT COUNT(*)

  INTO TABLE_EXISTS

  FROM ALL_TABLES

  WHERE TABLE_NAME = UPPER('MY_TABLE')

  AND ' '||OWNER||'.' = nvl(UPPER(' SCOTT.'),' '||OWNER||'.');

IF TABLE_EXISTS > 0 THEN

  DBMS_OUTPUT.PUT_LINE('-dropping table         SCOTT.MY_TABLE');

  EXECUTE IMMEDIATE 'drop table SCOTT.MY_TABLE cascade constraints';

END IF;

END;

/

As a prototype, we have customized the DROP TABLE statement in the Oracle 11g DBMS file in C:\Program Files (x86)\Sybase\PowerDesigner 16\Resource Files\DBMS\ora11g.xdb to generate PL/SQL code as a wrapper to test whether the table already exists.  This works fine. However it means our logic is wired to a specific DBMS file.  Any ideas about alternate ways of wrapping PL/SQL code around DDL statements?

[c_baker]

Writing the DDL all in one place will disable the capability of PD to separate the drop and create statements that you can select in the DBMS generation options.

It will also disable proper reverse-engineering of DBMS code via the GTL and, most likely, the ability of PD to 'apply model changes to database' as there will be no control over the table creation phase (you could accidentally drop a table containing critical data without intending to).

You couid easily alter the 'Script\Objects\Table\Drop' script in the Oracle 11g DBMS definition to check for table existence before dropping, using your code above as a template.  (This is done on the ASE definitions if you need an example of how to wrap the 'drop' statement).

All you then need to do is select the 'drop' and 'create' DBMS options when generating code, per your standards, to get the required DDL.

Of course you are always free to change a copy of the DBMS defintion to meet your requirements, as long as you understand the way it could affect the behaviour of PD operations.

Chris