Find everywhere a record is used

Adding a field to a record looks harmless, but anything that depends on the record’s exact columns can break: a SELECT * in an Application Engine program, or an INSERT without a column list. Before you make the change, find everything that uses the record, and check each one. AI Assist finds the uses, and the LLM reads each one to decide whether the change affects it.

You’ll use: find_references, search_code, describe_app_engine, and describe_record.

Ask

We're adding a ZZ_TRAIN_HOURS field to the ZZ_TRAIN_REQ record.
Find everything that uses the record, and tell me what would need
to change.

What happens

find_references with {"type": "record", "name": "ZZ_TRAIN_REQ"} lists the definitions that refer to the record: two pages, and three queries, one of them public. The empty groups matter too: no component uses the record as its search record, and no record uses it as a prompt table or subrecord.

{
  "type": "record",
  "name": "ZZ_TRAIN_REQ",
  "pages": ["ZZ_TRAIN_APPR", "ZZ_TRAIN_REQ"],
  "searchRecordFor": [],
  "promptTableFor": [],
  "subrecordOf": [],
  "relatedLanguageRecordFor": [],
  "auditRecordFor": [],
  "queries": {
    "public": ["ZZ_TRAIN_REQ_STATUS"],
    "privateCount": 2
  },
  "truncated": false
}

search_code with {"text": "ZZ_TRAIN_REQ"} finds the record’s name in code, in seven places: two PeopleCode programs, the ZZ_TRN_PEND_VW view, and four statements in two Application Engine programs. One of them is in ZZ_TRN_ARCH, a batch program that’s easy to forget when you change the component:
One of the matches (abbreviated)
```
{
  "source": "sql",
  "sqlType": "app_engine",
  "appEngine": "ZZ_TRN_ARCH.MAIN.Step01",
  "line": 2,
  "snippet": "SELECT * FROM PS_ZZ_TRAIN_REQ"
}
```
describe_app_engine with {"program": "ZZ_TRN_ARCH"} shows the whole statement. The LLM needs it because the snippet is only the matching line, and a SELECT * can be harmless on its own. The statement copies closed requests into a history table:
ZZ_TRN_ARCH.MAIN.Step01
```
INSERT INTO PS_ZZ_TRAIN_HIST
SELECT * FROM PS_ZZ_TRAIN_REQ
 WHERE ZZ_TRAIN_STATUS IN ('C', 'D')
```
describe_record for ZZ_TRAIN_HIST shows that it has the same fields as ZZ_TRAIN_REQ, in the same order, which is why the statement works today.

What you get

Adding ZZ_TRAIN_HOURS to ZZ_TRAIN_REQ

One statement would fail: ZZ_TRN_ARCH.MAIN.Step01. It copies rows with INSERT INTO PS_ZZ_TRAIN_HIST SELECT * FROM PS_ZZ_TRAIN_REQ, which works only while the two tables have the same columns in the same order. With the new column, the select returns one more value than the history table has columns, and Oracle Database rejects the insert with ORA-00913: too many values.

The fix: rewrite the statement with the %InsertSelect meta-SQL construct. It builds the column list from the fields of the insert record, ZZ_TRAIN_HIST, and selects the fields of the same name from ZZ_TRAIN_REQ, so the statement keeps working when either record gains a field. If the history should keep the hours too, add ZZ_TRAIN_HOURS to ZZ_TRAIN_HIST as well, and %InsertSelect copies it without further changes.

No change needed for the other uses:

Pages ZZ_TRAIN_REQ and ZZ_TRAIN_APPR display the record’s fields. Add the new field to them only if users should see the hours.

Queries select fields by name, so the public query ZZ_TRAIN_REQ_STATUS and the two private queries aren’t affected.

ZZ_TRAINING_REQ.GBL.SavePreChange counts an employee’s pending requests.

ZZ_TRAIN_REQ.COURSE.FieldChange sets the request’s fee from the course table.

The ZZ_TRN_PEND_VW view selects three columns by name.

ZZ_TRN_LOAD.MAIN.Step01 selects approved requests, by column name.

ZZ_TRN_LOAD.LOAD_ROW.Step02 sets a request’s status to C.

ZZ_TRN_ARCH.MAIN.Step02 deletes the requests that Step01 copied.

When you add the field, build ZZ_TRAIN_REQ with the Alter Tables option in every database you move it to. The component reads every field of the record, so it fails until the table has the new column.

The LLM also drafts the new statement:

%InsertSelect(ZZ_TRAIN_HIST, ZZ_TRAIN_REQ A)
  FROM %Table(ZZ_TRAIN_REQ) A
 WHERE A.ZZ_TRAIN_STATUS IN ('C', 'D')

%InsertSelect writes the INSERT and SELECT parts of the statement, but not the FROM clause, so the statement still names the table it selects from.

Check the answer

Compare with Find Definition References. Open ZZ_TRAIN_REQ in Application Designer, and choose Edit > Find Definition References. It lists the definitions that refer to the record, so you can check that the answer missed nothing.
Test in development. Add the field, build the record, and then save a request in the component and run ZZ_TRN_ARCH, before you migrate anything.

Go further

“Show me every SELECT * in our custom Application Engine programs.”
“List the definitions to add to the migration project for this change.”
“What would break if we removed the FEE_AMT field from ZZ_TRAIN_REQ?”