ORA-22275: How to Restore CLOB Data from an Excel Backup Using Python
I recently ran into this problem myself and was able to work out the following solution with the help of Claude (Anthropic's AI assistant). I'm sharing it here in case it saves someone else the same headache.
The Problem
You exported an Oracle table to Excel as a backup. The table contains CLOB columns — JSON payloads, multi-line text, or both. Now the table is empty and you need to restore the data.
Your first instinct: generate INSERT statements with Toad and run them. The result:
ORA-22275: invalid LOB locator specified
Or Toad simply stops silently after a few hundred rows.
Why This Happens
Oracle CLOBs are not plain strings — internally they are managed via LOB Locators, pointers to the actual storage. When Toad exports CLOB content to Excel, only the raw text is preserved.
The problem arises when Toad re-generates INSERT statements from that data: the CLOB values end up as plain string literals containing real newline characters, which is invalid SQL. On top of that, Oracle limits string literals in SQL context to 4,000 characters — anything longer silently fails or throws an error.
The Solution: Transform the SQL Before Running It
Instead of connecting Python directly to Oracle, the approach is simpler:
- Export the data from Excel as INSERT statements using Toad
- Transform those statements with a Python script
- Run the transformed SQL via SQL*Plus on the server
The transformation wraps each CLOB value in a PL/SQL block using DBMS_LOB.CREATETEMPORARY and DBMS_LOB.APPEND, handling:
- Newlines →
CHR(10) - Single quotes → escaped as
'' - Long values → split into chunks of max. 3,900 characters (safely below Oracle's 4,000-character string literal limit)
What the Transformation Does
A plain Toad INSERT like this:
INSERT INTO myschema.ai_log (id, response, answer)
VALUES (
42,
'{"status": "completed", "text": "Line one
Line two"}',
'First line
Second line
Third line'
);
...becomes a self-contained PL/SQL block:
DECLARE
v_response CLOB;
v_answer CLOB;
BEGIN
DBMS_LOB.CREATETEMPORARY(v_response, TRUE);
DBMS_LOB.APPEND(v_response, TO_CLOB('{"status": "completed", "text": "Line one'));
DBMS_LOB.APPEND(v_response, CHR(10));
DBMS_LOB.APPEND(v_response, TO_CLOB('Line two"}'));
DBMS_LOB.CREATETEMPORARY(v_answer, TRUE);
DBMS_LOB.APPEND(v_answer, TO_CLOB('First line'));
DBMS_LOB.APPEND(v_answer, CHR(10));
DBMS_LOB.APPEND(v_answer, TO_CLOB('Second line'));
DBMS_LOB.APPEND(v_answer, CHR(10));
DBMS_LOB.APPEND(v_answer, TO_CLOB('Third line'));
INSERT INTO myschema.ai_log (id, response, answer)
VALUES (42, v_response, v_answer);
DBMS_LOB.FREETEMPORARY(v_response);
DBMS_LOB.FREETEMPORARY(v_answer);
END;
/
Step-by-Step Guide
Step 1 — Export INSERT statements from Toad
In Toad: right-click the table → Export Data → Insert Statements. Save as input.sql.
Step 2 — Adjust the script
Edit the CLOB_COLUMNS set at the top of the script to match your actual CLOB column names:
CLOB_COLUMNS = {'YOUR_CLOB_COLUMN_1', 'YOUR_CLOB_COLUMN_2'}
Step 3 — Run the transformation
On any machine with Python 3 — no packages needed:
python3 transform_clob_inserts.py input.sql output.sql
Step 4 — Transfer the output to the DB server
Use WinSCP, scp, or any SFTP client:
scp output.sql oracle@dbserver:/home/oracle/output.sql
Step 5 — Run via SQL*Plus
💡 If your data contains special characters or umlauts, set the character encoding before calling SQL*Plus — otherwise they may be imported incorrectly:
export NLS_LANG=.AL32UTF8
💡 Toad can silently stop on large scripts. SQL*Plus is more reliable for batch execution of hundreds of PL/SQL blocks.
sqlplus user/password@service @output.sql
Why Not Run Python Directly Against the Database?
The straightforward approach would be to use oracledb or cx_Oracle to connect from Python and insert rows directly. This is indeed more elegant — but it requires installing packages.
On a production or development Oracle server, installing Python packages as the oracle OS user carries a small but real risk of interfering with Oracle's own scripts and tooling. A Python virtual environment (venv) would isolate this — but even that feels uncomfortable on a shared DB server.
The SQL transformation approach avoids this entirely: Python only reads and writes text files, SQL*Plus does what it was built for.
The Python Script
The complete script — no external libraries required, only Python's built-in re and sys modules:
#!/usr/bin/env python3
"""
transform_clob_inserts.py
Transforms Toad-exported INSERT statements containing CLOB fields
into valid PL/SQL blocks using DBMS_LOB.
Usage:
python3 transform_clob_inserts.py input.sql output.sql
"""
import re
import sys
CHUNK_SIZE = 3900
CLOB_COLUMNS = {'QUESTION', 'PROMPT', 'RESPONSE', 'ANSWER'} # adjust to your table
def escape_oracle_string(text):
return text.replace("'", "''")
def extract_values_section(stmt):
table_match = re.match(
r'insert\s+into\s+([\w.]+)\s*\(', stmt, re.IGNORECASE | re.DOTALL
)
if not table_match:
return None, None, None
table = table_match.group(1)
cols_start = stmt.index('(') + 1
depth = 1
pos = cols_start
while pos < len(stmt) and depth > 0:
if stmt[pos] == '(':
depth += 1
elif stmt[pos] == ')':
depth -= 1
pos += 1
cols_str = stmt[cols_start:pos - 1]
columns = [c.strip() for c in cols_str.split(',')]
values_keyword = re.search(r'\bvalues\b', stmt[pos:], re.IGNORECASE)
if not values_keyword:
return table, columns, None
values_start_pos = pos + values_keyword.end()
while values_start_pos < len(stmt) and stmt[values_start_pos] != '(':
values_start_pos += 1
values_start_pos += 1
values = []
current_val = []
depth = 1
in_str = False
i = values_start_pos
while i < len(stmt) and depth > 0:
ch = stmt[i]
if in_str:
current_val.append(ch)
if ch == "'":
if i + 1 < len(stmt) and stmt[i + 1] == "'":
current_val.append(stmt[i + 1])
i += 2
continue
else:
in_str = False
else:
if ch == "'":
in_str = True
current_val.append(ch)
elif ch == '(':
depth += 1
current_val.append(ch)
elif ch == ')':
depth -= 1
if depth == 0:
values.append(''.join(current_val).strip())
break
else:
current_val.append(ch)
elif ch == ',' and depth == 1:
values.append(''.join(current_val).strip())
current_val = []
else:
current_val.append(ch)
i += 1
return table, columns, values
def extract_string_content(val):
v = val.strip()
if v.startswith("'") and v.endswith("'"):
return v[1:-1].replace("''", "'")
return None
def transform_insert(stmt, clob_columns):
table, columns, values = extract_values_section(stmt)
if not columns or not values or len(columns) != len(values):
return stmt
clob_indices = [
idx for idx, col in enumerate(columns)
if col.upper() in clob_columns
and idx < len(values)
and values[idx].strip().startswith("'")
]
if not clob_indices:
return stmt
lines = ['DECLARE']
for idx in clob_indices:
lines.append(f" v_{columns[idx].lower()} CLOB;")
lines.append('BEGIN')
for idx in clob_indices:
col = columns[idx].lower()
raw = extract_string_content(values[idx])
lines.append(f" DBMS_LOB.CREATETEMPORARY(v_{col}, TRUE);")
text_parts = raw.split('\n')
chunk = ''
for line_idx, part in enumerate(text_parts):
escaped = escape_oracle_string(part)
is_last = (line_idx == len(text_parts) - 1)
if len(chunk) + len(escaped) > CHUNK_SIZE:
if chunk:
lines.append(f" DBMS_LOB.APPEND(v_{col}, TO_CLOB('{chunk}'));")
chunk = escaped
else:
chunk += escaped
if not is_last:
if len(chunk) > CHUNK_SIZE - 10:
lines.append(f" DBMS_LOB.APPEND(v_{col}, TO_CLOB('{chunk}'));")
lines.append(f" DBMS_LOB.APPEND(v_{col}, CHR(10));")
chunk = ''
else:
if chunk:
lines.append(f" DBMS_LOB.APPEND(v_{col}, TO_CLOB('{chunk}'));")
lines.append(f" DBMS_LOB.APPEND(v_{col}, CHR(10));")
chunk = ''
if chunk:
lines.append(f" DBMS_LOB.APPEND(v_{col}, TO_CLOB('{chunk}'));")
new_values = list(values)
for idx in clob_indices:
new_values[idx] = f"v_{columns[idx].lower()}"
lines.append(f" INSERT INTO {table}")
lines.append(f" ({','.join(columns)})")
lines.append(f" VALUES")
lines.append(f" ({','.join(new_values)});")
for idx in clob_indices:
lines.append(f" DBMS_LOB.FREETEMPORARY(v_{columns[idx].lower()});")
lines.append('END;')
lines.append('/')
return '\n'.join(lines)
def parse_sql_file(content):
statements = []
current = []
paren_depth = 0
in_string = False
i = 0
while i < len(content):
ch = content[i]
if in_string:
current.append(ch)
if ch == "'":
if i + 1 < len(content) and content[i + 1] == "'":
current.append(content[i + 1])
i += 2
continue
else:
in_string = False
else:
if ch == "'":
in_string = True
current.append(ch)
elif ch == '(':
paren_depth += 1
current.append(ch)
elif ch == ')':
paren_depth -= 1
current.append(ch)
elif ch == ';' and paren_depth == 0:
current.append(';')
stmt = ''.join(current).strip()
if stmt.upper().startswith('INSERT'):
statements.append(stmt)
current = []
i += 1
continue
else:
current.append(ch)
i += 1
return statements
def main():
if len(sys.argv) != 3:
print("Usage: python3 transform_clob_inserts.py input.sql output.sql")
sys.exit(1)
with open(sys.argv[1], 'r', encoding='utf-8') as f:
content = f.read()
statements = parse_sql_file(content)
print(f"Found {len(statements)} INSERT statements")
transformed = [transform_insert(s, CLOB_COLUMNS) for s in statements]
with open(sys.argv[2], 'w', encoding='utf-8') as f:
f.write('\n\n'.join(transformed) + '\n')
print(f"Done. Output written to {sys.argv[2]}")
if __name__ == '__main__':
main()
⬇ Download transform_clob_inserts.py
A Note on Oracle E-Business Suite
In my case, the table lived in an Oracle E-Business Suite (EBS) environment, which added an extra layer of complexity. I had created the table in a separate schema, granted all necessary privileges to the APPS user, and — as required in EBS — run AD_ZD_TABLE.UPGRADE on it before attempting the import.
This procedure makes a table compatible with Edition-Based Redefinition (EBR), which EBS uses internally. What it actually does is rename the base table and create an Editioning View in its place. Any DML — including imports — then goes through that view, not directly to the table. In my experience, this is what caused the LOB Locator error in the first place.
The solution that ultimately worked for the non-CLOB columns was simpler than expected: I created a fresh table directly in the APPS schema using CREATE TABLE ... AS SELECT ... from the original, without running AD_ZD_TABLE.UPGRADE, and imported into that. The CLOB fields came across without any issues. In hindsight, had I known this earlier, the Python script would not have been necessary at all — the direct import would have worked from the start.
💡 If you are working in an EBS environment and encounter LOB Locator errors on import, check whether AD_ZD_TABLE.UPGRADE has been run on the target table. Importing into a plain table in the APPS schema — without EBR — may resolve the issue entirely.
Caveats
- The script assumes CLOB values are exported as standard SQL string literals by Toad. Other export formats may need adjustments.
- Column names in
CLOB_COLUMNSmust match the exported SQL exactly (case is handled internally via.upper()). - For very large CLOBs (>1 MB per field), consider splitting the output file and running in batches.
- Always test with a small subset before running the full restore.
Summary
| Step | Tool | Notes |
|---|---|---|
| Export INSERT statements | Toad | Standard export, no special settings |
| Transform SQL | Python 3 (built-ins only) | Only standard library modules required |
| Transfer to server | WinSCP / scp | Any SFTP client works |
| Execute | SQL*Plus | More reliable than Toad for large scripts |