Skip to content
SparkyaiPeopleSoft

search_code

search_code searches the text of every PeopleCode program and SQL definition in your database at once, like Application Designer’s Find In across the whole system. It’s how the LLM answers “where is this used?” questions, and how it follows a call from one program to the next.

Use it for

Section titled “Use it for”
  • “Find every PeopleCode program and SQL object that references the VOUCHER_LINE record.”
  • “Where is the ZZ_CALC_FEE function declared, and what calls it?”
  • “Does anything still use the old ZZ_TRN_OLD_VW view?”

Input

Section titled “Input”
Argument Type Required Description
text string Yes The text to find, 3 to 200 characters. Case doesn’t matter.
in array of strings No Where to search: peoplecode, sql, or both. Default: both.
whole_word boolean No Match whole words only, so that JOB doesn’t match JOBCODE. Default: false.
limit integer No The maximum number of matches, from 1 to 200. Default: 50.
Input schema
{
  "type": "object",
  "properties": {
    "text": { "type": "string", "minLength": 3, "maxLength": 200 },
    "in": {
      "type": "array",
      "items": { "enum": ["peoplecode", "sql"] },
      "uniqueItems": true,
      "minItems": 1
    },
    "whole_word": { "type": "boolean", "default": false },
    "limit": {
      "type": "integer",
      "minimum": 1,
      "maximum": 200,
      "default": 50
    }
  },
  "required": ["text"],
  "additionalProperties": false
}

Output

Section titled “Output”
Field Description
matches The matches, grouped by program or SQL definition
matches[].source peoplecode or sql
matches[].kind, path For PeopleCode, the program’s kind and path, ready for get_peoplecode
matches[].sqlId, sqlType For SQL, the definition’s ID and type, ready for get_sql
matches[].appEngine For Application Engine SQL, the step that runs it, as PROGRAM.SECTION.STEP
matches[].line The line number of the match
matches[].snippet The matching line, trimmed
truncated true when there are more matches than limit, or when the search ran out of time
programsWithoutText How many PeopleCode programs couldn’t be searched because their text is missing from PSPCMTXT
Example: text 'zz_train_req'
{
  "matches": [
    {
      "source": "peoplecode",
      "kind": "component",
      "path": "ZZ_TRAINING_REQ.GBL.SavePreChange",
      "line": 4,
      "snippet": "SQLExec(\"SELECT COUNT(*) FROM %Table(ZZ_TRAIN_REQ) WHERE EMPLID = :1 AND ZZ_TRAIN_STATUS = 'P'\", &emplid, &pending);"
    },
    {
      "source": "sql",
      "sqlId": "ZZ_TRN_PEND_VW",
      "sqlType": "view",
      "line": 1,
      "snippet": "SELECT A.EMPLID, A.COURSE, A.REQUEST_DT FROM PS_ZZ_TRAIN_REQ A WHERE A.ZZ_TRAIN_STATUS = 'P'"
    }
  ],
  "truncated": false,
  "programsWithoutText": 0
}

How it works

Section titled “How it works”

The database narrows down the candidates, and the server does the rest:

  1. Find candidates. A LIKE query runs against the PeopleCode text in PSPCMTXT and the SQL text in PSSQLTEXTDEFN. Any % or _ in your text is escaped, so it matches literally.
  2. Catch matches split across rows. Long programs and statements are stored in several rows, ordered by PROGSEQ or SEQNUM, and a match can straddle two of them. The server also checks where each pair of consecutive rows meet, so those matches aren’t missed.
  3. Finish on the server. For each candidate, the server reassembles the full text, applies whole_word, and works out line numbers and snippets.
PeopleCode candidates (Oracle Database)
SELECT OBJECTID1, OBJECTVALUE1, OBJECTID2, OBJECTVALUE2,
       OBJECTID3, OBJECTVALUE3, OBJECTID4, OBJECTVALUE4,
       OBJECTID5, OBJECTVALUE5, OBJECTID6, OBJECTVALUE6,
       OBJECTID7, OBJECTVALUE7, PROGSEQ
  FROM PSPCMTXT
 WHERE UPPER(PCTEXT) LIKE :pattern ESCAPE '\'
SQL candidates (Oracle Database)
SELECT SQLID, SQLTYPE, DBTYPE, EFFDT, SEQNUM
  FROM PSSQLTEXTDEFN
 WHERE UPPER(SQLTEXT) LIKE :pattern ESCAPE '\'

For zz_train_req, :pattern is %ZZ\_TRAIN\_REQ%. For a match in Application Engine SQL (SQLTYPE '1'), the server finds the step that runs the statement through PSAESTMTDEFN.SQLID, and returns it as appEngine.

To report programsWithoutText, the server counts the programs in PSPCMPROG that have no rows in PSPCMTXT. That count is expensive, so the server works it out when it starts and refreshes it every hour.

Limits and errors

Section titled “Limits and errors”
  • Searches need at least 3 characters.
  • A search scans all your PeopleCode and SQL, which can take a while on a large system. It runs under the query timeout, 30 seconds by default. If time runs out, the matches found so far come back with truncated set to true.
  • Programs whose text is missing from PSPCMTXT can’t be searched. programsWithoutText tells the LLM how many there are, so it can say that the answer may be incomplete.
  • Matches in comments are included, because this is a text search.
  • Only PeopleCode and SQL definitions are searched. Other definitions, such as HTML definitions and PeopleSoft Query definitions, aren’t included. find_references finds the query definitions that use a record or field, and the pages, components, and records that refer to it.
Section titled “Related tools”