VARCHAR2 and NVARCHAR2¶

The naming scheme¶

Domain	Constraint	Use for
`oracle.varchar_<N>b`	`octet_length(VALUE) <= N`	`VARCHAR2(N)` under Oracle's default `NLS_LENGTH_SEMANTICS=BYTE`
`oracle.varchar_<N>c`	`char_length(VALUE) <= N`	`VARCHAR2(N)` under `NLS_LENGTH_SEMANTICS=CHAR`
`oracle.nvarchar_<N>c`	`char_length(VALUE) <= N`	`NVARCHAR2(N)` — always character-length in real Oracle, regardless of `NLS_LENGTH_SEMANTICS`

b counts bytes, c counts characters — they only diverge on multi-byte text. A 5-character string that's 6 bytes long (say, one accented character) fits _5c but not _5b.

Length coverage: every length 1–300 individually, then 310–4000 in steps of 10 — 670 distinct lengths, matching Oracle's own 4000-character VARCHAR2 column cap. A declared length above 300 that isn't an exact multiple of 10 gets rounded up to the next available domain by the rewrite tool below — e.g. VARCHAR2(847) becomes oracle.varchar_850b. This never rejects previously-valid data; it's only ever marginally looser than the original constraint. See Customizing the Type Family if you want a different range or step.

Finding existing usage¶

Three places VARCHAR2/NVARCHAR2 can turn up, each needing its own check:

1. Inside PL/pgSQL functions/procedures — use oracle.orafce_audit():

SELECT * FROM oracle.orafce_audit(p_summary => false)
WHERE target_object IN ('varchar2', 'nvarchar2') OR matched_text ~* 'n?varchar2';

2. Table columns — the audit function doesn't cover DDL, so query the catalog directly:

SELECT c.relname AS table_name,
       a.attname AS column_name,
       format_type(a.atttypid, a.atttypmod) AS declared_type
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
JOIN pg_type t ON t.oid = a.atttypid
JOIN pg_namespace n ON n.oid = t.typnamespace
WHERE n.nspname = 'oracle'
  AND t.typname IN ('varchar2', 'nvarchar2')
  AND a.attnum > 0
  AND NOT a.attisdropped;

declared_type comes back as e.g. varchar2(50) — information_schema.columns hides the length for custom types (character_maximum_length comes back NULL), but format_type() recovers it.

3. Source you haven't run anywhere yet — a plain search works, and is exactly the pattern the rewrite tool below looks for:

grep -rniE '\b(N?VARCHAR2)\s*\(' path/to/your/source/

Rewriting your code¶

audit/rewrite_varchar2_declarations.pl mechanically rewrites VARCHAR2(n)/NVARCHAR2(n) text in any SQL/PL-SQL source file to point at the matching domain. It's a plain text rewrite — it works identically on CREATE TABLE column definitions, PL/pgSQL variable declarations, and function return types, since all three are just VARCHAR2(n) appearing as a type name in source text.

# Dry-run (default): prints a diff, writes nothing.
perl audit/rewrite_varchar2_declarations.pl path/to/your_file.sql

# Byte-length mode is the default (matches Oracle's own default
# NLS_LENGTH_SEMANTICS=BYTE). Use --mode=c if your source database ran
# with NLS_LENGTH_SEMANTICS=CHAR. NVARCHAR2 always maps to the _c domain
# regardless of this flag.
perl audit/rewrite_varchar2_declarations.pl --mode=c path/to/your_file.sql

# Once you've reviewed the diff, write the changes back:
perl audit/rewrite_varchar2_declarations.pl --mode=c --apply path/to/your_file.sql

Example rewrite (--mode=b, the default):

-- before
v_name VARCHAR2(50);
v_note NVARCHAR2(847);

-- after
v_name oracle.varchar_50b;
v_note oracle.nvarchar_850c;   -- 847 rounded up to the next available length

Lengths over 4000 are reported as a warning and left completely untouched — never silently mapped to something wrong. The script exits non-zero if any file had an out-of-range length, so it's safe to use as a gate in a migration pipeline.

Columns already live in a database, not in a source file¶

The rewrite tool operates on text files, not a running database's catalog. If you found usage via the catalog query above rather than in source you control, alter the column directly instead:

ALTER TABLE my_table
  ALTER COLUMN my_column TYPE oracle.varchar_50b USING my_column::text;

Every domain in the family wraps plain text, so USING my_column::text is always sufficient. The ALTER fails immediately, before touching any data, if an existing value exceeds the target domain's constraint — the correct, safe failure mode.

Continue to Why These Types Error Instead of Truncating — read this before you migrate anything that relies on VARCHAR2's cast-truncation behavior.