SQL Plus - как передать большую строку в параметр процедуры CLOB

Question

У меня есть процедура, которая получает XML:

CREATE OR REPLACE PROCEDURE PROCESS_XML(xml IN CLOB) AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('XML processing started');
END;

Сейчас я делаю сценарий bash, который загружает некоторые XML-файлы с сервера, и для каждого из них я буду вызывать описанную выше процедуру с использованием SQL Plus.

#!/bin/bash
file=$(curl -s "http://example.com/someFile.xml");
sqlplus myuser/mypass@myhost:1521/myscheme <<< "EXECUTE PROCESS_XML('$file')";

Он отлично работает для маленьких файлов, но для больших, я получаю следующую ошибку:

SQL*Plus: Release 12.1.0.2.0 Production on Thu Jun 8 18:28:19 2017
Copyright (c) 1982, 2016, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP
and Data Mining options
SQL> Input truncated to 7499 characters
SP2-0027: Input is too long (> 2499 characters) - line ignored

Могу ли я что-нибудь сделать, чтобы отправить эти большие XML?

Спасибо

Answer 1

Вы можете разбить содержимое файла на куски, которые будет принимать SQL*Plus, а затем рекомбинировать их внутри анонимного блока PL/SQL; это также позволит значение, которое длиннее строкового литерала. Например:

#!/bin/bash

file=$(curl -s "http://example.com/someFile.xml" | sed -r "s/(.{1,2000})/l_clob := l_clob || '\1';\n/g")

sqlplus -s -l myuser/mypass@myhost:1521/myscheme <<!EOF
set serveroutput on
declare
 l_clob clob := '';
begin
${file}
 PROCESS_XML(l_clob);
end;
/
exit
!EOF

EXECUTE в любом случае это обертка вокруг простого анонимного блока, поэтому использование heredoc вместо herestring просто позволяет расширить это, чтобы сделать больше. Блок объявляет пустой CLOB, а затем добавляет куски из файла - каждый из которых преобразуется в следующий вид:

 l_clob := l_clob || '<up to 2000 chars>';

Когда SQL*Plus видит это, сконструированный heredoc в конечном итоге выглядит так:

set serveroutput on
declare
 l_clob clob := '';
begin
 l_clob := l_clob || '<first 2000 chars>';
 l_clob := l_clob || '<next 2000 chars>';
 l_clob := l_clob || '<next 2000 chars>';
 ...
 l_clob := l_clob || '<last 2000 chars>';
 PROCESS_XML(l_clob);
end;
/
exit

Немного изменив вашу процедуру, частично для проверки переданной длины и частично для проверки того, что XML не был поврежден в процессе:

CREATE OR REPLACE PROCEDURE PROCESS_XML(xml IN CLOB) AS
BEGIN
  DBMS_OUTPUT.PUT_LINE('XML processing started; CLOB length: '
    || length(xml));
  DBMS_OUTPUT.PUT_LINE('XML processing started; converted XML length: '
    || length(xmltype(xml).getclobval()));
END;
/

использование этого скрипта для обработки большого файла дает вывод:

XML processing started; CLOB length: 368104
XML processing started; converted XML length: 368104

PL/SQL procedure successfully completed.

Конечно, это немного тормозит; этот файл ~360k занял в моей системе около 13 секунд. Там могут быть более быстрые механизмы, чем sed, но принцип все еще применяется.

---

Версия sed на MacOS (который нуждается -E вместо GNU -r флаг), кажется, ограничен 255 повторениями шаблона (через RE_DUMP_MAXустановить в limits.h и не настолько, насколько я знаю, модифицируемое во время выполнения).

Вы можете просто использовать нижний предел:

file=$(curl -s "http://example.com/someFile.xml" | sed -E "s/(.{1,255})/l_clob := l_clob || '\1';\n/g")

что на самом деле намного быстрее и под Linux, так что в любом случае это неплохой вариант.

---

После дальнейших экспериментов на macOS (El Cap, но, вероятно, то же самое для Sierra) и попыток заставить работать экранированные символы новой строки без включения буквального n или же \n в выводе, который вызывает PLS-00103, кажется, легче вставить фактическую новую строку в:

file=$(curl -s "http://example.com/someFile.xml" | sed -E "s/(.{1,255})/ l_clob := l_clob || '\1';\
/g")

Answer 2

Вы можете попытаться перебрать входной файл, добавляя по 2,4 куска за раз, вероятно:

variable l_var clob;
exec :l_var := '';

-- loop here 
exec :l_var := :l_var || '$chunk';
---

exec process_xml(:l_var);

И вместо сценариев оболочки вы также можете формировать clob в Java, например, построчно читая XML, у которого нет ограничения на размер переменной.

Answer 3

Не могли бы вы попробовать сжать данные файла в bash, а затем разархивировать в PLSQL с помощью utl_compress?

Что-то вроде:

#!/bin/bash
file=$(curl -s "http://example.com/someFile.xml" | gzip -f);
sqlplus myuser/mypass@myhost:1521/myscheme <<< "EXECUTE PROCESS_XML('$file')";

В plsql:

CREATE OR REPLACE PROCEDURE PROCESS_XML(xml IN CLOB) AS
   uncomp CLOB;
BEGIN
  UTL_COMPRESS.lz_uncompress(src => xml, dst => uncomp);
  DBMS_OUTPUT.PUT_LINE('XML processing started');
END;