E-AD: Alter By Table Rename and Create Index Do Not Use DDL Model Defaults To Create Build Script (Doc ID 1534257.1)

Last updated on SEPTEMBER 01, 2016

Applies to:

PeopleSoft Enterprise PT PeopleTools - Version 8.51 and later
Information in this document applies to any platform.

Symptoms

Alter By Table Rename generates a temp table to temporarily hold data, but App Designer does not follow DDL defaults to build that temp table.

You have to override the parameter values manually for it to do so.

Sample Replication:
---------------------

1. Open App Designer
2. Create New Record
3. Insert dummy fields, such as EMPLID and TEXT1
4. Double-click first field (EMPLID) and make it a Search Key
5. Save Record
6. Build > Build Definition
7. Select option "Create Table", and "Execute & Build SQL Script"
8. Click Build
9. Check SQL script and see the DDL matches what's delivered in DDL Model (PeopleTools > Utilities > Administration > DDL Model Defaults).

CREATE TABLE PS_GCSTEST1 (EMPLID VARCHAR2(11) NOT NULL,
   TEXT1 VARCHAR2(15) NOT NULL) TABLESPACE PTAPP STORAGE (INITIAL
 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10
 PCTUSED 80
/
CREATE UNIQUE  iNDEX PS_GCSTEST1 ON PS_GCSTEST1 (EMPLID) TABLESPACE
 PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED
 PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
/


10. Now add another field (e.g. TEXT15) to the Record
11. Save
12. Build > Build Definition
13. In Settings, under Alter, select "Alter by Table Rename", OK
13. Select option "Alter Table" and "Build SQL Script"
14. Click Build
15. Check SQL script and see the DDL does not match what's delivered in DDL Model, but only for the temp table that was created for the Alter By Rename process:

CREATE TABLE PSYGCSTEST1 (EMPLID VARCHAR2(11) NOT NULL,
   TEXT1 VARCHAR2(15) NOT NULL,
   TEXT15 VARCHAR2(15) NOT NULL) PCTFREE 10 PCTUSED 80 INITRANS 1
 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 40960 NEXT 106496 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "PTAPP"
/
INSERT INTO PSYGCSTEST1 (
        EMPLID,
    TEXT1,
    TEXT15)
  SELECT
        EMPLID,
    TEXT1,
    ' '
  FROM PS_GCSTEST1
/
DROP TABLE PS_GCSTEST1
/
RENAME PSYGCSTEST1 TO PS_GCSTEST1
/
CREATE UNIQUE  iNDEX PS_GCSTEST1 ON PS_GCSTEST1 (EMPLID) TABLESPACE
 PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED
 PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
/
ALTER INDEX PS_GCSTEST1 NOPARALLEL LOGGING
/


16. If you create a DDL Override value for the record in App Designer though, then the value does get pushed through in the SQL script:

- With the Record open in App Designer, go to: Tools > Record Administration > Record DDL
- Highlight any of the parameters (for example INIT), and click "Edit Param"
- Enter 40000
- OK, OK
- Save Record
- Build Record again with Alter Table and Alter by Rename.  Check the generated script and see the parameters now follow the DDL specified:

CREATE TABLE PSYGCSTEST1 (EMPLID VARCHAR2(11) NOT NULL,
   TEXT1 VARCHAR2(15) NOT NULL,
   TEXT15 VARCHAR2(15) NOT NULL) TABLESPACE PTAPP STORAGE (INITIAL
 40000 NEXT 100000 MAXEXTENTS UNLIMITED PCTINCREASE 0) PCTFREE 10
 PCTUSED 80
/
INSERT INTO PSYGCSTEST1 (
        EMPLID,
    TEXT1,
    TEXT15)
  SELECT
        EMPLID,
    TEXT1,
    ' '
  FROM PS_GCSTEST1
/
DROP TABLE PS_GCSTEST1
/
RENAME PSYGCSTEST1 TO PS_GCSTEST1
/
CREATE UNIQUE  iNDEX PS_GCSTEST1 ON PS_GCSTEST1 (EMPLID) TABLESPACE
 PSINDEX STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS UNLIMITED
 PCTINCREASE 0) PCTFREE 10 PARALLEL NOLOGGING
/
ALTER INDEX PS_GCSTEST1 NOPARALLEL LOGGING
/

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms