#oracle #sqlplus
#Oracle #sqlplus
Вопрос:
Я должен экспортировать таблицу Oracle в виде ИНСТРУКЦИЙ INSERT.
Но инструкции INSERT, созданные таким образом, переопределяют 2500 символов.
Я обязан выполнить их в SQL Plus, поэтому я получаю сообщение об ошибке.
Это моя таблица Oracle:
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)
);
ПРЕДПОЛОЖЕНИЯ:
a) Я ОБЯЗАН экспортировать данные таблицы в виде ИНСТРУКЦИЙ INSERT; мне разрешено использовать инструкции UPDATE, чтобы избежать ошибки SQL * Plus «ввод sp2-0027 слишком длинный (> 2499 символов)».;
б) Я ОБЯЗАН использовать SQL * Plus для выполнения сгенерированного таким образом скрипта.
c) Пожалуйста, предположите, что каждая запись может содержать специальные символы: CHR (10), CHR (13) и так далее;
d) я НЕ МОГУ использовать SQL Loader;
e) Я НЕ МОГУ экспортировать, а затем импортировать таблицу: я могу добавить «дельту» только с помощью инструкций INSERT / UPDATE через SQL Plus.
Комментарии:
1. Один момент неясен, и это важно. Есть ли у вас какие-либо вставки / обновления, где значение c13 превышает 2499 символов? Или у вас просто есть некоторые инструкции, в которых длина инструкции превышает 2499 символов? (Первое сложно, второе легко исправить)
2. Ответ следующий: в моей таблице Oracle под названием SAMPLE_TABLE у меня есть 80 записей, где длина поля C13 типа VARCHAR2 (4000) составляет 3762 символа. Это означает, что, конечно, ЗНАЧЕНИЕ C13 превышает 2499 символов. В этом случае мы можем наблюдать, что длина ИНСТРУКЦИИ также превышает 2499 символов.
3. @Курица на кухне — каков первичный ключ целевой таблицы?
4. Пожалуйста, предположите, что исходная таблица и таблица назначения НЕ ИМЕЮТ первичного ключа.
5. Известно только, что поле с именем C13 иногда имеет значения, достигающие 4000 символов.
Ответ №1:
Вау, эти ограничения довольно ограничены, но я думаю, что, возможно, есть способ обойти это. Я думаю, вам, возможно, придется написать свой собственный небольшой скрипт для этого.
Я бы сам использовал Java с JDBC (но подойдет любой язык, который может подключаться к базе данных и читать ее, а также выводить строки), написав небольшую программу, которая извлекает набор записей из каждой строки в базе данных. Затем для каждой из этих строк:
-
Создайте инструкцию insert с полными данными. Если это меньше 2000 байт, то просто выведите его в файл и переходите к следующей строке.
-
В противном случае создайте инструкцию insert для каждого поля, но оставьте
c13поле как''(пустым). -
Затем, если ваша
c13inputстрока превышает 2000 символов, выведите инструкцию update формы"update tbl set c13 = c13 || '" c13input.substring (0,2000) "' where ..."(добавив следующие 2000 символов), а затем сделайтеc13input = c13input.substring(2000), чтобы удалить эти символы из вашей строки. -
Как только длина
c13inputстанет меньше или равна 2000 символам, просто выведите одно окончательное обновление, чтобы прикрепить его в конце.
Это позволяет вам сохранять ваши отдельные инструкции SQL с отметкой в 2000 символов и эффективно выполнять правильный SQL для повторного заполнения другой таблицы базы данных.
Это то, о чем я говорю (для таблицы, содержащей только первичный ключ c1 и большой гудящий 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
Очевидно, вам может потребоваться преобразовать строки, чтобы разрешить вставку специальных символов — я не уверен, в каком формате Oracle ожидает их, но, надеюсь, будет просто передать строки ( r.get("c13") если длина полной вставки меньше 2000, f.substring(0,2000) и f если вы также создаете обновления) вспомогательной функции для этого.
Если такое преобразование, вероятно, приведет к увеличению размера печатаемой строки, вы можете снизить пороговое значение до 1000 в целях безопасности, чтобы гарантировать, что преобразованная строка не приведет к строке, превышающей ограничение PL / SQL.
Извините, если это кажется запутанным, но ограничения, которые вы указали, немного затрудняют нас. Вполне может быть лучший способ, но я не могу придумать тот, который соответствует всем вашим критериям.
Обновление: Похоже, вы еще более стеснены в средствах, чем первоначально предполагалось: если вам приходится ограничиваться SQL для генерации скрипта, а также для его запуска, есть способ, каким бы мучительным он ни был.
Вы можете использовать SQL для генерации SQL. Используя мою вышеупомянутую таблицу с c1 и c13 , вы можете сделать:
select
'insert into newtable (c1,c13) values ("' ||
c1 ||
'","");'
from oldtable;
# Xlates to: insert into newtable (c1,c13) values ("[c1]","");
Это даст вам все ваши базовые insert инструкции для дублирования всего, кроме c13 столбца.
Затем вам нужно сгенерировать больше инструкций для настройки c13 . Для обновления c13 для всех значений длиной 1000 или меньше (простой набор):
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
Затем в update c13 для всех значений от 1001 до 2000 символов (задайте, затем добавьте):
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.
И так далее для тех, длина которых составляет от 2001 до 3000 и от 3001 до 4000.
Вероятно, потребуется выполнить некоторую настройку. Я рад предложить вам способ решения этой проблемы, но мое желание работать над таким чудовищем до завершения в лучшем случае минимально 🙂
Выполнит ли это задание? ДА. Красиво ли это? Я бы сказал, что это было решительное «НЕТ!», но, учитывая ваши ограничения, это может быть лучшим, на что вы можете надеяться.
В качестве доказательства концепции приведем SQL-скрипт в DB2 (хотя никаких специальных функций, он должен нормально работать в любой СУБД, имеющей эквивалент length и substr ):
# 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;
и это генерирует следующие строки:
> 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';
Разбивая выходные строки, мы получаем:
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';
что должно дать вам исходные строки, хотя и окольным путем.
И это примерно столько усилий, сколько я могу вложить в любой вопрос без того, чтобы мои мозги не поджарились, поэтому я попрощаюсь с вами, если мне не укажут на какие-либо серьезные ошибки.
Удачи с вашим проектом и наилучших пожеланий.
Комментарии:
1. Я написал, что мне нужно решение на PL / SQL. Я не знаю Java. Более того, я не уполномочен запускать классы Java в рабочей среде. Я могу ВЫБРАТЬ / ВСТАВИТЬ / ОБНОВИТЬ только на языке PL / SQL. Я уполномочен создавать пакет JAVA, это означает пакет PL / SQL, в котором использовались классы JAVA. Можете ли вы преобразовать свой скрипт в пакет JAVA, компилируемый в ORACLE PL / SQL?
2. Нет. Если вы также ограничены PL / SQL для создания скрипта, вас, по сути, ждут очень тяжелые времена. Это хороший язык для написания сценариев SQL, но он не настолько хорош для материалов общего назначения. Вы заявили, что «ОБЯЗАНЫ использовать SQL * Plus для выполнения сгенерированного таким образом сценария» и ничего не сказали о требованиях для фактического создания сценария, поэтому я предложил свой подход. Oracle позволяет вам подключаться через JDBC довольно легко, а инструменты Java бесплатны, так что это все еще мой ответ, просто потому, что ограничить себя использованием PL / SQL будет намного сложнее, чем изучать Java / JDBC.
3. Сказав это, я дам вам старт (следите за моей правкой), но впереди будет долгий мучительный путь 🙂
4. Ваше решение кажется таким интересным… Я с нетерпением жду возможности проанализировать и протестировать это: если это сработает, я приму ответ. Всегда приветствуются любые другие подсказки.
5. @Chicken: похоже, у вас есть привычка получать длинные, подробные ответы на свои вопросы. 🙂
Ответ №2:
Вы можете использовать Jailer tool (http://jailer.sf.net) для экспорта табличных данных в виде ИНСТРУКЦИЙ INSERT.
Комментарии:
1. Насколько я понимаю вопрос, у него нет проблем с их экспортом, но импорт / выполнение сгенерированного скрипта вызывает проблемы из-за слишком длинных строк.
2. Вы правы, но инструмент, о котором я упоминал, обертывает строки, чтобы можно было использовать SQL * Plus для импорта данных.
3. Я подтверждаю, что у меня нет проблем с экспортом записей в виде ИНСТРУКЦИЙ INSERT, потому что я использую TOAD (от Quest Software). Во второй половине дня я попытаюсь использовать инструмент Jailer, чтобы посмотреть, что происходит при экспорте данных моей таблицы с помощью Jailer. Цель состоит в том, чтобы инструкции INSERT, сгенерированные с помощью Jailer, не выдавали ошибку SQL * Plus «ввод sp2-0027 слишком длинный (> 2499 символов)».
4. Извините, но инструмент Jailer слишком сложен в использовании, я хотел бы найти решение на PL / SQL, без использования внешних инструментов. Пользователи должны иметь возможность решить эту проблему самостоятельно.
5. Более того, я не могу подключиться к моей базе данных Oracle 10G R2 с помощью этого инструмента…