PeopleSoft - Create Table -Build Script File

I was little curious today to find the series of SQL statements that gets executed from the Application Designer when you try to create a table in PeopleSoft. To setup, I created a record definition TEST_RECORD with three fields in it

1) DESCR100

2) DESCR20

3) DESCR50

I selected the "Build Script File" option and clicked on "Build". Note that the table does not exist in PeopleSoft. In subsequent blog posts, we will try to see some variations. Here we go..

Behind the scenes:-

1) SELECT VERSION, DESCR FROM PSMSGSETDEFN WHERE MESSAGE_SET_NBR = :1

The value passed to this :1 is 76. When queried in the database the value for Version came as "1" and DESCR came as "SQL Build Process". So, basically the system is trying to retrieve the messages that needs to be fired during the build process. These messages should be appearing in the "Build" tab of the application designer.

2) SELECT MESSAGE_NBR, MESSAGE_TEXT, MSG_SEVERITY, DESCRLONG,TO_CHAR(LAST_UPDATE_DTTM,'YYYY-MM-DD-HH24.MI.SS."000000"')

FROM PSMSGCATDEFN WHERE MESSAGE_SET_NBR = :1 ORDER BY MESSAGE_NBR

The value passed to this statement is 76 again. This is the complete set of message catalogue entries that is stored in PeopleSoft, and that get displayed during the build process. Some of them are dynamic as well.

3) SELECT COUNT(*) FROM PSDDLMODEL WHERE PLATFORMID = 2

Not sure why this is executed. May be it is trying to get a count to ensure that there are suffcient rows in the table to construct the SQL statement. This is evident from the next SQL.

4) SELECT STATEMENT_TYPE, PLATFORMID, SIZING_SET, PARMCOUNT, MODEL_STATEMENT FROM PSDDLMODEL WHERE PLATFORMID = 2 ORDER BY STATEMENT_TYPE, PLATFORMID, SIZING_SET

Now, this MODEL_STATEMENT field is holding values like

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;

These three parameters are dynamic and depends on the record

[TBNAME] => substituted from Application designer

[TBCOLLIST] => dynamic substitution

[TBSPCNAME] => dynamic substitution

CREATE [UNIQUE] **BITMAP** INDEX [IDXNAME] ON [TBNAME] ([IDXCOLLIST]) TABLESPACE **INDEXSPC** STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE**; <I have not defined any index on my table >

CREATE TABLESPACE [TBSPCNAME] DATAFILE '**DIR**[TBSPCNAME].DBF' SIZE **SIZE** DEFAULT STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**);

ANALYZE TABLE [TBNAME] ESTIMATE STATISTICS;

ANALYZE TABLE [TBNAME] COMPUTE STATISTICS;

Each of the **<value>** gets dynamically substituted during the build process. This is also evident from the next SQL

SELECT STATEMENT_TYPE, PLATFORMID, SIZING_SET, PARMNAME, PARMVALUE FROM PSDDLDEFPARMS WHERE PLATFORMID = 2 ORDER BY STATEMENT_TYPE, PLATFORMID, SIZING_SET.

The result of this statement in my DB is

1 2 0 INIT 40000

1 2 0 MAXEXT UNLIMITED

1 2 0 NEXT 100000

1 2 0 PCT 0

1 2 0 PCTFREE 10

1 2 0 PCTUSED 80

2 2 0 BITMAP

2 2 0 INDEXSPC PSINDEX

2 2 0 INIT 40000

2 2 0 MAXEXT UNLIMITED

2 2 0 NEXT 100000

2 2 0 PCT 0

2 2 0 PCTFREE 10

3 2 0 DIR /dir/

3 2 0 INIT 40000

3 2 0 MAXEXT UNLIMITED

3 2 0 NEXT 100000

3 2 0 PCT 0

3 2 0 SIZE 30000000

So, the final script statement came out to be

CREATE TABLE PS_TEST_RECORD (DESCR100 VARCHAR2(300) NOT NULL

CHECK(LENGTH(DESCR100)<=100),

DESCR20 VARCHAR2(60) NOT NULL CHECK(LENGTH(DESCR20)<=20),

DESCR50 VARCHAR2(150) NOT NULL CHECK(LENGTH(DESCR50)<=50))

TABLESPACE AMAPP STORAGE (INITIAL 40000 NEXT 100000 MAXEXTENTS

UNLIMITED PCTINCREASE 0) PCTFREE 10 PCTUSED 80

/

COMMIT < I guess PeopleSoft Automatically adds this >

/

The values highlighted in red are directly picked up from the table. The Application designer program "implicitly" substitutes these values as obtained from the table.

5) SELECT 'PS_DOES_TABLE_EXIST' FROM PS_TEST_RECORD

Oracle error occurred: 942 (ORA-00942: table or view does not exist)

This was the last statement in my trace file. Here, it tries to query the database to really see if the table is existing in the first place. In subsequent blog posts, we will see a variation of what would happen, if the table really existed!