Pulling File Attachments Out of the PeopleSoft Database

Years ago, I wrote about PeopleSoft file attachment storage options and why I prefer keeping attachments in the database. That approach keeps the files with the data during refreshes and backups, and avoids managing a separate set of storage paths.

The question I hear most often is how to get those files back out. You could download them through the browser or write a PeopleTools program, but sometimes you need to retrieve them from outside PeopleSoft—for a migration, an archive, or another service.

The storage is easier to inspect than it might first appear. PeopleTools keeps a reference to each file and stores its contents in ordered chunks.

How PeopleSoft Stores and Chunks Attachments

An attachment typically involves a business record, a reference record, and a storage record. The business record contains the application’s keys. The reference record connects those keys to the user’s filename (ATTACHUSERFILE) and the system filename (ATTACHSYSFILENAME). The storage record holds the file contents.

When database storage is configured, the storage record uses the delivered FILE_ATTDET_SBR subrecord.

The file is stored as a sequence of rows rather than as one large value. Each row contains a chunk of the file; chunk sizes are typically around 28–32 KB. The subrecord includes:

ATTACHSYSFILENAME identifies the file, FILE_SEQ gives each chunk its sequence number (starting at 0), and VERSION identifies the revision. FILE_SIZE records the chunk size, while FILE_DATA contains the binary data.

To find all tables across your PeopleSoft database configured to store attachment chunks, you can query PSRECFIELD against the data dictionary:

SELECT 
  A.RECNAME,
  'PS_' || CASE WHEN A.SQLTABLENAME <> ' ' THEN A.SQLTABLENAME ELSE A.RECNAME END AS TABLE_NAME,
  T.NUM_ROWS
FROM PSRECDEFN A
JOIN PSRECFIELD B 
  ON B.RECNAME = A.RECNAME
JOIN USER_TABLES T 
  ON T.TABLE_NAME = 'PS_' || CASE WHEN A.SQLTABLENAME <> ' ' THEN A.SQLTABLENAME ELSE A.RECNAME END
WHERE A.RECTYPE = 0
  AND B.SUBRECORD = 'Y'
  AND B.FIELDNAME = 'FILE_ATTDET_SBR'
ORDER BY T.NUM_ROWS DESC;

Extracting and Reassembling the File

Inside PeopleSoft, the delivered PeopleCode functions GetAttachment, ViewAttachment, and DetachAttachment handle attachments. GetAttachment reassembles a file on the application or batch server, making it the natural choice for an Application Engine program.

For work outside PeopleSoft, such as a migration or an archive, you can query the storage table directly. The basic steps are:

1. Find the latest VERSION for the ATTACHSYSFILENAME.
2. Select that version’s rows in FILE_SEQ order.
3. Write each FILE_DATA value to the output in sequence.

Here’s an example in Go using go-ora , a pure-Go Oracle driver:

package main

import (
  "database/sql"
  "fmt"
  "io"
  "os"

  go_ora "github.com/sijms/go-ora/v2"
)

// ExtractAttachment queries chunked BLOBs from any FILE_ATTDET_SBR storage table
// and writes the reassembled binary stream directly into the target file path.
func ExtractAttachment(db *sql.DB, tableName, sysFilename, targetPath string) (int64, error) {
  // Step 1: Resolve the latest version
  verQuery := fmt.Sprintf(`
    SELECT NVL(MAX(VERSION), 1)
    FROM %s
    WHERE ATTACHSYSFILENAME = :1`, tableName)

  var version int64
  if err := db.QueryRow(verQuery, sysFilename).Scan(&version); err != nil {
    return 0, fmt.Errorf("failed to retrieve version for %s: %w", sysFilename, err)
  }

  // Step 2: Query chunked rows in sequence order
  chunkQuery := fmt.Sprintf(`
    SELECT FILE_SEQ, FILE_SIZE, FILE_DATA
    FROM %s
    WHERE ATTACHSYSFILENAME = :1 AND VERSION = :2
    ORDER BY FILE_SEQ ASC`, tableName)

  rows, err := db.Query(chunkQuery, sysFilename, version)
  if err != nil {
    return 0, fmt.Errorf("querying attachment chunks failed: %w", err)
  }
  defer rows.Close()

  // Step 3: Stream chunks directly into the output file
  outFile, err := os.Create(targetPath)
  if err != nil {
    return 0, fmt.Errorf("cannot create target file: %w", err)
  }
  defer outFile.Close()

  var totalBytes int64
  for rows.Next() {
    var seq int64
    var chunkSize int64
    var blob go_ora.Blob

    if err := rows.Scan(&seq, &chunkSize, &blob); err != nil {
      return totalBytes, fmt.Errorf("error reading chunk %d: %w", seq, err)
    }

    n, err := outFile.Write(blob.Data)
    if err != nil {
      return totalBytes, fmt.Errorf("error writing chunk %d: %w", seq, err)
    }
    totalBytes += int64(n)
  }

  if err := rows.Err(); err != nil {
    return totalBytes, fmt.Errorf("chunk iteration error: %w", err)
  }

  return totalBytes, nil
}

Writing each chunk as it is read avoids loading the whole attachment into memory. That matters for large files, and keeps memory use tied to the chunk size rather than the total file size.

When Direct Database Access Helps

Direct access is useful when another system needs the files without going through a PeopleSoft page or batch process. For example, a background job could retrieve expense receipts or vendor invoices and send them to an LLM with vision capabilities to extract merchant names, dates, line items, and totals. The resulting data could then be checked and passed back to PeopleSoft for review or processing. The same pattern can be used to analyze resumes or transcripts, or to scan new uploads with an antivirus tool. For older data, it can copy attachments to object storage as part of an archive or migration.

This is not a replacement for PeopleTools attachment functions in application code. It is another option when an external process needs the file bytes and can safely connect to the database.