Switch to standard view 
  Sybase logo

Supporting Global Temporary Tables for Oracle 8i using PowerDesigner 7 and 8

In Oracle 8i, support for Global Temporary Tables was added. PowerDesigner does not have a built in support for this feature, but it can be implemented via changes in the XDB file and the use of Extended Attributes.  For an overview of how to use PowerDesigner Extended Attributes see one of the following Technical

Tapping the power of PowerDesigner 7.x Extended Attributes

Tapping the power of PowerDesigner 8.0 Extended Attributes

1) Create a new DBMS file
    - Select Tools>Resources>DBMS
    - Select the "new" icon to create a new DBMS file and give it a name such as ORACLE Version 8i Extended
    - Select Oracle Version 8i in the "Copy from" drop down listbox
    - Click OK

2) In the new XDB add a new Extended Attribute Type
    - Expand the Extended Attributes node
    - Right-click on Extended Attributes>Types and select New> Extended Attribute Types
    - Expand Types and select the newly created Extended Attribute Type
    - Rename the Type to True/False
    - Add two values True and False and set the default to False
3) Add a new Extended Attribute
    - Right-click on Extended Attributes>Objects, select Add Items and select Table
    - Right-click on Extended Attributes>Objects>Table and select New>Extended Attribute
    - Expand Table and select the newly created Extended Attribute
    - Rename the Extended Attribute to GlobalTemp
    - Under Data Type, select True/False from the drop down listbox.
    - Select False as the Default Value
NOTE: The Extended Attribute name will be used as a variable in the Create Table section.  This variable name is case sensitive.

4) Modify the Create Table syntax
    The following syntax must be added to the Create Table item:
          [%variable name%=True? global temporary]
     In this example, the variable name = GlobalTemp

    - Select the Create Item under Script>Objects>Table
    - In the Value block, insert [%GlobalTemp%=True? global temporary] between create and table.  This must be done for both create table sections.

Sample code for Create Item:

[%CLASS% ?
[%CLUSTERCOLUMNS%?create cluster  [%QUALIFIER%]C_%TABLE% (
create [%GlobalTemp%=True? global temporary] table [%QUALIFIER%]%TABLE% of [%QUALIFIER%]%CLASS% [%TABLDEFN% ? (
[%CLUSTERCOLUMNS%?create cluster  [%QUALIFIER%]C_%TABLE% (
create [%GlobalTemp%=True? global temporary] table [%QUALIFIER%]%TABLE% [%TABLDEFN% ? (

5) Click OK
6) Save the new XDB file in the \\program files\PowerDesigner 7\DBMS directory using a name like oracle8igt.xdb

To implement the GlobalTemp extended attribute
1) Change the current DBMS to ORACLE Version 8i (8.1.5) Extended
2) Open the Table properties and select the Extended Attributes tab
3) Select the GlobalTemp extended attribute and change the Value to True

4) Click OK to apply the changes
5) Open the Table properties and select preview

Back to Top
© Copyright 2010, Sybase Inc.