CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Mike Christensen
Mike Christensen

Reputation: 91598

Running into string constant size limits in insert statements with Oracle

I'm trying to copy a row from our production DB to my own little personal Oracle Express DB to repro a bug, since I can't really step into the code on production. Unfortunately, this row involves a column that serializes some sort of data structure into a blob column type, laughing in the face of the normalization gods. Here's the INSERT:

INSERT INTO TPM_VIEWS VALUES(
5,
'Test Repro View',
665,
1,
'0001000000ffffffff01000000000000000c020000003a44414c2c205... //About 7600 characters
);

I've tried running this in Aqua Data Studio 10 and I get:

ORA-01704: string literal too long

Next I tried pasting it into SQL*Plus, which gives me:

SP2-0027: Input is too long (> 2499 characters) - line ignored

Lastly, I tried pasting the whole thing into foo.sql and ran @foo.sql which gives me:

SQL> @c:\foo.sql
Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored
ERROR:
ORA-01756: quoted string not properly terminated

What's the super secret Oracle expert way to do this? And no, I don't have access to the Oracle server itself so I can't run any command line backup or export utilities. Thanks!

UPDATE:

I also tried splitting apart the string by sprinkling some ' || ''s around randomly, which gives me the error:

 ORA-01489: result of string concatenation is too long

Upvotes: 2

Views: 4521

Answers (1)

Justin Cave
Justin Cave

Reputation: 231661

Since you can access the production database, the simplest solution is probably to create a database link from your local XE database to the production database.

CREATE DATABASE LINK link_to_prod
  CONNECT TO <<your user name in prod>> 
  IDENTIFIED BY <<your password in prod>>
  USING <<TNS alias for prod database>>

Then, you can copy the data from prod to your local database

INSERT INTO tpm_views
  SELECT <<list of columns including BLOB>>
    FROM tpm_views@link_to_prod
   WHERE <<some key>> = 5

Upvotes: 4

Related Questions