Peopletools 8.51 : “whenever sqlerror exit” is still missing

Whether a lot of new features are coming within the latest Peopletools version 8.51 working closer from Oracle database, one is still missing. That one I’m awaiting for the last 10 years I’m working on Peoplesoft.
When you build a table, there no clause “whenever sqlerror exit” automatically added on top of the generated script, and that could save us from data loss. Here’s a simple example.
First of all, for the need of the test, I created two fields – MYSTRING VARCHAR2(30) and MYNUM NUMBER(8,3).
Then I create a record as following :
 
I put that record in a very small tablespace CUAUDIT which has been set in noextend mode :
SQL> alter database datafile '/u01/app/oracle/oradata/H91TMPLT/cuaudit.dbf' autoextend off;

Database altered.
Then I build that record, as usual :
 
The generated script is very simple, as follow :
 
Then, running the script in a SQL*Plus session :
 
So far, so good.
Now, populate the table with whatever you like (here with random values, that does not matter), but it fulfill the tablespace :
 
Now, I’d like to reduce the size of the column MYNUM from 8,3 to 7,3. As we all know, that’s not possible to achieve this by an ALTER TABLE when the column is not empty (ORA-01440 : column to be modified must be empty to decrease precision or scale) :
 
So, let’s go in AppDesigner, modifying the column length and rebuild the table, not through ALTER TABLES (that won’t work) but through CREATE TABLES (with a staging table) :



And the script is now :
 
As far as we can see, some efforts have been made in the script on the comments to warn people reading the script about the table drop command, but from my point of view this is far not enough.
Return in SQL*Plus session, and run it :

There’s obviously not enough space in my tablespace for the staging table, but the script does not care error, it continues and drop my source table. It is just gone !!
If you are lucky enough, or rather a “data safe guardian”, you have RECYCLEBIN feature turned on on your database, otherwise, a restore will be needed (sigh) :

And ultimately, you can recover the table from the recyclebin with flashback :
 
So, you have to set the famous clause “whenever sqlerror exit” in your SQL*Plus session and you’ll be able to run safely the script :

Now the source table still exists, and you are able to fix the issue before running it again. But before the next run, don’t forget to drop the staging table, otherwise it will fail again !
On a simple script, we can see easily the errors, if needed, what to recover, and how to fix it. But most of the time, during project migration, there’s much more to build (e.g. Peopletools upgrade), and very bad surprise are not rare.

As I said at the beginning of this blog entry, this case is not new, it has always been like that (at least from Peopletools 8.18 I started on), but seeing Peopletools working closer and closer from the Oracle Database, I sincerely hope that will be included in the next Peopletools release.
Awaiting that fix, take care !