EXPORT AS INSERT STATEMENTS: But in SQL Plus the line overrides 2500 characters!

Question

I have to export an Oracle table as INSERT STATEMENTS.

But the INSERT STATEMENTS so generated, override 2500 characters.

I am obliged to execute them in SQL Plus, so I receive an error message.

This is my Oracle table:

CREATE TABLE SAMPLE_TABLE
(
   C01   VARCHAR2 (5 BYTE) NOT NULL,
   C02   NUMBER (10) NOT NULL,
   C03   NUMBER (5) NOT NULL,
   C04   NUMBER (5) NOT NULL,
   C05   VARCHAR2 (20 BYTE) NOT NULL,
   c06   VARCHAR2 (200 BYTE) NOT NULL,
   c07   VARCHAR2 (200 BYTE) NOT NULL,
   c08   NUMBER (5) NOT NULL,
   c09   NUMBER (10) NOT NULL,
   c10   VARCHAR2 (80 BYTE),
   c11   VARCHAR2 (200 BYTE),
   c12   VARCHAR2 (200 BYTE),
   c13   VARCHAR2 (4000 BYTE),
   c14   VARCHAR2 (1 BYTE) DEFAULT 'N' NOT NULL,
   c15   CHAR (1 BYTE),
   c16   CHAR (1 BYTE)
);

ASSUMPTIONS:

a) I am OBLIGED to export table data as INSERT STATEMENTS; I am allowed to use UPDATE statements, in order to avoid the SQL*Plus error "sp2-0027 input is too long(>2499 characters)";

b) I am OBLIGED to use SQL*Plus to execute the script so generated.

c) Please assume that every record can contain special characters: CHR(10), CHR(13), and so on;

d) I CAN'T use SQL Loader;

e) I CAN'T export and then import the table: I can only add the "delta" using INSERT / UPDATE statements through SQL Plus.

Answer 1

Wow, those constraints are quite limiting but I think there may be a way around it. I think you may well have to write your own little script for this.

I would use Java with JDBC myself (but any language that can connect to and read the database, and output strings, will do), writing a little program which retrieved a record set of every row in the database. Then, for every one of those rows:

• Construct an insert statement with the full data. If this is less than 2,000 bytes, then just output it to the file and move on to the next row.

• Otherwise create an insert statement for every field, but leave the c13 field as '' (empty).

• Then, as long as your c13input string is greater than 2000 characters, output an update statement of the form "update tbl set c13 = c13 || '" + c13input.substring (0,2000) + "' where ..." (appending the next 2000 characters) and then do c13input = c13input.substring(2000) to strip off those characters from your string.

• Once c13input is less than or equal to 2000 characters in length, just output one final update to tack it on the end.

This allows you to keep your individual SQL statements around the 2000-character mark and efficiently execute the correct SQL to repopulate another database table.

This is the type of thing I'm talking about (for a table containing just a primary key c1 and a big honkin' varchar c13):

rowset r = db.exec ("select * from oldtable");
while r.next != NO_MORE_ROWS:
    string s = "insert into newtable (c1,c13) values ('" +
        r.get("c1") + "','" + r.get("c13") + "')"
    if s.len() < 2000:
        print s
    else:
        s = "insert into newtable (c1,c13) values ('" + r.get("c1") + "','')"
        print s
        f = r.get("c13")
        while f.len() > 2000:
            s = "update newtable set c13 = c13 || '" + f.substring(0,2000) + ')"
            f = f.substring(2000)
            print s
        endwhile
        s = "update newtable set c13 = c13 || '" + f + ')"
        print s
    endif
endwhile

Obviously, you may need to morph the strings to allow inserts of special characters - I'm not sure what format Oracle expects these in, but it would hopefully be a simple matter of passing the strings (r.get("c13") if the length of the full insert is less than 2000, f.substring(0,2000) and f if you're constructing updates as well) to a helper function to do this.

If that morphing is likely to increase the size of the line printed, you may want to drop the threshold back to 1000 to be safe, to ensure the morphed string doesn't result in a line greater than the PL/SQL limit.

Sorry if that seems convoluted but the restrictions you've stated hamstring us a little bit. There may well be a better way but I can't think of one that meets all your criteria.

---

Update: It appears you're even more hamstrung than originally thought: if you have to limit yourself to SQL for generating the script as well as running it, there is a way, torturous though it is.

You can use SQL to generate SQL. Using my afore-mentioned table with c1 and c13, you can do:

select
    'insert into newtable (c1,c13) values ("' ||
    c1 ||
    '","");'
from oldtable;
# Xlates to: insert into newtable (c1,c13) values ("[c1]","");

That will give you all your baseline insert statements for duplicating everything but the c13 column.

What you then need to do is generate more statements for setting c13. To update c13 for all values of length 1000 or less (simple set):

select
    'update newtable set c13 = "' ||
    c13 ||
    '" where c1 = "' ||
    c1 ||
    '";'
from oldtable where length(c13) <= 1000;
# Xlates to: update newtable set c13 = "[c13]" where c1 = "[c1]";
#   but only for rows where length([c13]) <= 1000

Then, to update c13 for all values between 1001 and 2000 characters (set then append):

select
    'update newtable set c13 = "' ||
    substring(c13,1,1000) ||
    '" where c1 = "' ||
    c1 ||
    '";'
from oldtable where length(c13) > 1000 and length(c13) <= 2000;
select
    'update newtable set c13 = c13 || "' ||
    substring(c13,1001,1000) ||
    '" where c1 = "' ||
    c1 ||
    '";'
from oldtable where length(c13) > 1000 and length(c13) <= 2000;
# Xlates to: update newtable set c13 =        "[c13a]" where c1 = "[c1]";
#            update newtable set c13 = c13 || "[c13b]" where c1 = "[c1]";
#   but only for rows where length([c13]) > 1000 and <= 2000
#   and [c13a]/[c13b] are the first/second thousand chars of c13.

And so on for the ones that are 2001-to-3000 and 3001-to-4000 in length.

There'll likely need to be some tweaking done. I'm happy to give you a way of solving it but my desire to work on such a monstrosity through to completion is minimal at best :-)

Will it get the job done? Yes. Is it pretty? I'd say that was a resounding "NO!" but, given your constraints, that may be the best you can hope for.

---

As a proof of concept, here's an SQL script in DB2 (no special features though, it should work fine in any DBMS that has a length and substr equivalent):

# Create table and populate.

DROP TABLE XYZ;
COMMIT;
CREATE TABLE XYZ (F1 VARCHAR(1),F2 VARCHAR(20));
COMMIT;
INSERT INTO XYZ VALUES ('1','PAX');
INSERT INTO XYZ VALUES ('2','GEORGE');
INSERT INTO XYZ VALUES ('3','VLADIMIR');
INSERT INTO XYZ VALUES ('4','ALEXANDRETTA');
SELECT * FROM XYZ ORDER BY F1;

# Create initial insert statem,ents.

SELECT 'INSERT INTO XYZ (F1,F2) VALUES (' || F1 ','''');' 
    FROM XYZ;

# Updates for 1-5 character F2 fields.

SELECT 'UPDATE XYZ SET F2 = ''' || F2 ||
    ''' WHERE F1 = ''' || F1 || ''';'
    FROM XYZ WHERE LENGTH(F2) <= 5;

# Updates for 6-10 character F2 fields.

SELECT 'UPDATE XYZ SET F2 = ''' || SUBSTR(F2,1,5) ||
    ''' WHERE F1 = ''' || F1 || ''';'
    FROM XYZ WHERE LENGTH(F2) > 5 AND LENGTH(F2) <= 10;

SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,6) ||
    ''' WHERE F1 = ''' || F1 || ''';'
    FROM XYZ WHERE LENGTH(F2) > 5 AND LENGTH(F2) <= 10;

# Updates for 11-15 character F2 fields.

SELECT 'UPDATE XYZ SET F2 = ''' || SUBSTR(F2,1,5) ||
    ''' WHERE F1 = ''' || F1 || ''';'
    FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;

SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,6,5) ||
    ''' WHERE F1 = ''' || F1 || ''';'
  FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;

SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,11) || 
    ''' WHERE F1 = ''' || F1 || ''';'
    FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;

and this generates the following lines:

> DROP TABLE XYZ;
> COMMIT;
> CREATE TABLE XYZ (F1 VARCHAR(1),F2 VARCHAR(20));
> COMMIT;
> INSERT INTO XYZ VALUES ('1','PAX');
> INSERT INTO XYZ VALUES ('2','GEORGE');
> INSERT INTO XYZ VALUES ('3','VLADIMIR');
> INSERT INTO XYZ VALUES ('4','ALEXANDRETTA');
> SELECT * FROM XYZ;
    F1  F2
    --  ------------
    1   PAX
    2   GEORGE
    3   VLADIMIR
    4   ALEXANDRETTA

> SELECT 'INSERT INTO XYZ (F1,F2) VALUES (' || F1 || ','''');'
> FROM XYZ;
    INSERT INTO XYZ (F1,F2) VALUES (1,'');
    INSERT INTO XYZ (F1,F2) VALUES (2,'');
    INSERT INTO XYZ (F1,F2) VALUES (3,'');
    INSERT INTO XYZ (F1,F2) VALUES (4,'');

> SELECT 'UPDATE XYZ SET F2 = ''' || F2 ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) <= 5;
    UPDATE XYZ SET F2 = 'PAX' WHERE F1 = '1';

> SELECT 'UPDATE XYZ SET F2 = ''' || SUBSTR(F2,1,5) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 5 AND LENGTH(F2) <= 10;
    UPDATE XYZ SET F2 = 'GEORG' WHERE F1 = '2';
    UPDATE XYZ SET F2 = 'VLADI' WHERE F1 = '3';

> SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,6) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 5 AND LENGTH(F2) <= 10;
    UPDATE XYZ SET F2 = F2 || 'E' WHERE F1 = '2';
    UPDATE XYZ SET F2 = F2 || 'MIR' WHERE F1 = '3';

> SELECT 'UPDATE XYZ SET F2 = ''' || SUBSTR(F2,1,5) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
    UPDATE XYZ SET F2 = 'ALEXA' WHERE F1 = '4';

> SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,6,5) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
    UPDATE XYZ SET F2 = F2 || 'NDRET' WHERE F1 = '4';

> SELECT 'UPDATE XYZ SET F2 = F2 || ''' || SUBSTR(F2,11) ||
> ''' WHERE F1 = ''' || F1 || ''';'
> FROM XYZ WHERE LENGTH(F2) > 10 AND LENGTH(F2) <= 15;
    UPDATE XYZ SET F2 = F2 || 'TA' WHERE F1 = '4';

Breaking out the output lines, we get:

INSERT INTO XYZ (F1,F2) VALUES (1,'');
INSERT INTO XYZ (F1,F2) VALUES (2,'');
INSERT INTO XYZ (F1,F2) VALUES (3,'');
INSERT INTO XYZ (F1,F2) VALUES (4,'');
UPDATE XYZ SET F2 = 'PAX' WHERE F1 = '1';
UPDATE XYZ SET F2 = 'GEORG' WHERE F1 = '2';
UPDATE XYZ SET F2 = 'VLADI' WHERE F1 = '3';
UPDATE XYZ SET F2 = F2 || 'E' WHERE F1 = '2';
UPDATE XYZ SET F2 = F2 || 'MIR' WHERE F1 = '3';
UPDATE XYZ SET F2 = 'ALEXA' WHERE F1 = '4';
UPDATE XYZ SET F2 = F2 || 'NDRET' WHERE F1 = '4';
UPDATE XYZ SET F2 = F2 || 'TA' WHERE F1 = '4';

which should give you the original rows, albeit in a roundabout way.

---

And that's about as much effort as I can put into any one question without my brain frying, so I'll bid you adieu unless any serious errors are pointed out to me.

Good luck with your project, and best wishes.

Answer 2

You may use Jailer tool (http://jailer.sf.net) to export table data as INSERT STATEMENTS.