PII Detection in SQL*Plus

Pii detection in SQL*Plus is not about guesswork. It is about knowing exactly how your data is shaped, where it lives, and how it moves. Running raw queries without safeguards risks exposing sensitive names, addresses, emails, phone numbers, and identification numbers. Once exposed, these fields can create compliance failures and security breaches.

To detect PII in SQL*Plus, start with structured scanning. Apply REGEXP_LIKE to search for common PII formats directly in your query output. For example:

SELECT customer_id, email
FROM customers
WHERE REGEXP_LIKE(email, '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,}$', 'i');

Pattern-based detection should be paired with column-level audits. Query ALL_TAB_COLUMNS to inspect field names and data types that frequently store PII, such as VARCHAR2 with names containing “email”, “phone”, “dob”, “ssn”, or “address.” This approach makes PII detection repeatable and scalable.

Export processes in SQL*Plus need the same discipline. Use SPOOL only when necessary. If spooling query results containing PII, ensure the output file is encrypted and stored securely. This prevents leaks at the layer between query execution and downstream processing.

Automated detection scripts can extend SQL*Plus by scanning results for regex matches, masking sensitive content before writing to disk, and throwing alerts if certain thresholds are met. For regulated environments, log every detection event with timestamps and query origins.

Compliance frameworks like GDPR and CCPA demand that PII detection in databases is precise, fast, and verifiable. SQL*Plus offers direct, low-level access — which means every operator must enforce strict controls during detection runs. Always test detection scripts in non-production mirrors before scanning live systems.

PII is not invisible. SQL*Plus gives you the power to see it. Use that power with intent.

See it live in minutes with hoop.dev — build secure PII detection workflows, integrate with your existing SQL*Plus scripts, and run them safely without the guesswork.