Question is related to Application Engine in PeopleSoft Q4 A

Question is related to Application Engine in PeopleSoft

Q4 Assume there are 2 tables ABC and XYZ. Both the tables are having ROW_NUM filed with length of 3 digits. ABC is having the 5 values 699, 799, 899, 999, 299. And we are trying to insert these 5 values into XYZ table by adding 1 to each number

Main Section

Step1

DoSelect

%Select(ROW_NUM)

SELECT ROW_NUM

FROM ABC

PeopleCode

Here I will write the Commit only for 3rd Example.

SQL

INSERT INTO XYZ

VALUES (%Bind(ROW_NUM)+1)

Step2

PeopleCode

SQLExec(\"COMMIT\");

What would be the program flow when

Section Level - Auto Commit – After Step is unchecked,

Step Level – Commit After is Later.

Step Level – OnError is Ignore.

Scenario is same as above A, Assume due to some Code error Step2 – PeopleCode got errored out then what will happen?

Q5 Assume there are 2 tables ABC and XYZ. Both the tables are having ROW_NUM filed with length of 3 digits. ABC is having the 5 values 699, 799, 899, 999, 299. And we are trying to insert these 5 values into XYZ table by adding 1 to each number

Main Section

Step1

DoSelect

%Select(ROW_NUM)

SELECT ROW_NUM

FROM ABC

PeopleCode

Here I will write the Commit only for 3rd Example.

SQL

INSERT INTO XYZ

VALUES (%Bind(ROW_NUM)+1)

Step2

PeopleCode

SQLExec(\"COMMIT\");

What would be the program flow when

Section Level - Auto Commit – After Step is unchecked,

Step Level – Commit After is Later.

Step Level – OnError is Ignore.

Scenario is same as above A, Assume due to some Code error Step2 – PeopleCode got errored out then what will happen?

Scenario is same as above A&B, but OnError option is Suppress then what will happen?

Q6 Assume there are 2 tables ABC and XYZ. Both the tables are having ROW_NUM filed with length of 3 digits. ABC is having the 5 values 699, 799, 899, 999, 299. And we are trying to insert these 5 values into XYZ table by adding 1 to each number

Main Section

Step1

DoSelect

%Select(ROW_NUM)

SELECT ROW_NUM

FROM ABC

PeopleCode

Here I will write the Commit only for 3rd Example.

SQL

INSERT INTO XYZ

VALUES (%Bind(ROW_NUM)+1)

Step2

PeopleCode

SQLExec(\"COMMIT\");

What would be the program flow when

Section Level - Auto Commit – After Step is unchecked,

Step Level – Commit After is Default.

Step Level – OnError is Abort.

What would be the program flow when

Section Level - Auto Commit – After Step is unchecked,

Step Level – Commit After is After Step.

Step Level – OnError is Abort.

And there is Commit statement in Step1 - PeopleCode.

SQLExec(\"COMMIT\");

Solution

Q4 1) First 3 rows will be inserted. Commit will not execute.

AE will throw and error while inserting 4th row.

Since we selected the Ignore option, AE will ignores that error and Issues the Commit, so first 3 rows will be get committed.

And then continues with the process and inserts the 5th row.

And it will not Commit after the Step1 since we selected Later option for Step1. So 5th row will be in buffer.

Then Step2 – PeopleCode will execute and issues the Commit.

So finally there would be 4 rows 700, 800, 900, 300 in XYZ.

2) First 3 rows will be inserted. Commit will not execute.

AE will throw and error while inserting 4th row.

Since we selected the Ignore option, AE will ignores that error and Issues the Commit, so first 3 rows will be get committed.

And then continues with the process and inserts the 5th row.

And it will not Commit after the Step1 since we selected Later option for Step1. So 5th row will be in buffer.

Then Step2 – PeopleCode will execute and it will throw an error. So it will not issue the commit. 5th row in buffer will not be get committed in XYZ Table.

So finally there would be 3 rows 700, 800, 900 in XYZ.

Q5 1) First 3 rows will be inserted. Commit will not execute.

AE will throw and error while inserting 4th row.

Since we selected the Ignore option, AE will ignores that error and Issues the Commit, so first 3 rows will be get committed.

And then continues with the process and inserts the 5th row.

And it will not Commit after the Step1 since we selected Later option for Step1. So 5th row will be in buffer.

Then Step2 – PeopleCode will execute and issues the Commit.

So finally there would be 4 rows 700, 800, 900, 300 in XYZ.

2) First 3 rows will be inserted. Commit will not execute.

AE will throw and error while inserting 4th row.

Since we selected the Ignore option, AE will ignores that error and Issues the Commit, so first 3 rows will be get committed.

And then continues with the process and inserts the 5th row.

And it will not Commit after the Step1 since we selected Later option for Step1. So 5th row will be in buffer.

Then Step2 – PeopleCode will execute and it will throw an error. So it will not issue the commit. 5th row in buffer will not be get committed in XYZ Table.

3) So finally there would be 3 rows 700, 800, 900 in XYZ.

First 3 rows will be inserted. Commit will not execute.

AE ignores the 4th row and will not issue the Commit.

Then inserts the 5th row. Commit will not execute.

Executes the Step2 – PeopleCode. AE will throw an error (as per the scenario). Commit will not execute.

So finally there will be not be any row in XYZ table.

Q6 1) In DoSelect 5 rows will be selected from ABC Table.

In 1st iteration 699 + 1 = 700 will be inserted into XYZ.

In 2nd iteration 799 + 1 = 800 will be inserted into XYZ.

In 3rd iteration 899 + 1 = 900 will be inserted into XYZ.

In 4th iteration it will try to insert 999 + 1 = 1000 which is of 4 digits length. Since field length is 3, AE will throw an error.

All the 3 inserted rows will be rolled back.

Commit will not execute so no row will be inserted into XYZ.

2) First 3 rows will be Inserted and each row will be Committed and AE will throw and error while inserting 4th row. So finally there would 3 rows in XYZ Table.