Skip to Content
author's profile photo Former Member
Former Member

How to generate DDL wrapped in PL/SQL?

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?

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Posted on Jan 13, 2016 at 01:17 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.