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, by default, 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.

Lengths over 4000¶

If you genuinely need a length over 4000 — Oracle's PL/SQL-only variables can go up to 32767, even though that's a buffer-sizing convention rather than a meaningful storage constraint — pass --out-of-range:

perl audit/rewrite_varchar2_declarations.pl --out-of-range --apply path/to/your_file.sql

This rewrites lengths over 4000 to an exact-length domain name — no rounding, since there's no fixed step tier above 4000 the way there is for 301–4000 (VARCHAR2(5000) becomes oracle.varchar_5000b, not a rounded value). None of these domains exist in sql/11_oracle_varchar2_domains.sql, so at the end of the run the script prints the exact CREATE DOMAIN statements needed for every out-of-range length actually used — deduplicated across every file you passed:

==============================================================================
-- The rewrite above used 2 domain(s) longer than the standard 1-4000 family covers.
-- None of these exist in sql/11_oracle_varchar2_domains.sql. Run the
-- statements below against your target database BEFORE installing the
-- rewritten PL/pgSQL source (requires the 'oracle' schema to already
-- exist -- see INSTALL.md):
==============================================================================
CREATE DOMAIN oracle.nvarchar_6000c AS text CHECK (char_length(VALUE) <= 6000);
CREATE DOMAIN oracle.varchar_5000b AS text CHECK (octet_length(VALUE) <= 5000);
==============================================================================

Copy that block and run it against your target database before installing the rewritten source — otherwise the rewritten code fails with type "oracle.varchar_5000b" does not exist.

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.