Reasons why PeopleSoft does not recomd SQLEXEC()

PS does not recommend sqlexec(), to avoid direct database calls. As the main reason for 3 tier architecture was to execute the business logic on the application server so that the application can be made database independent. This means if you want to change the database of any application from one vendor to another, you should be able to do it easily.

If you use sqlexec() calls, this purpose is not met as you may use sql which is very specific to the database. However if you use Peoplesoft way of creating a record, for example

RC_CASE is record with key as CASE_ID

Local record &lrec = createrecord(record.rc_case);
&lrec.case_id.value = 123;

Now if you want to select from RC_CASE, you would use sqlexec() as sqlexec("select case_closed from ps_rc_case where case_id=:1",123,&caseclos);

Instead you can also use the record instance as
&lrec.selectbykey();

The above will populate the rc_case record with all the values and you can use it. Also in this case Peoplesoft forms the sql based on the database, so if the organization using this application decides to change the database from Oracle to MS-SQL Server, you will not have to change the code for above.