SQL Plus - how to pass big string to procedure CLOB's parameter

Question

I have procedure that receives an XML:

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

Now I'm doing a bash script that will download some XML files from a server and, for each one, I will call the above procedure using SQL Plus.

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

It works fine for small file but for big ones, I'm getting the following error:

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

Is there anything I could in order to send those big XML?

Thanks

Answer 1

You can split the file contents into chunks that SQL*Plus will accept, and then recombine them inside an anonymous PL/SQL block; this will also allow value that are longer than a string literal can be. For instance:

#!/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 is a wrapper around a simple anonymous block anyway, so using a heredoc instead of a herestring just lets you expand that to do more. The block declares an empty CLOB and then appends the chunks form the file - which are each converted to look like:

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

When SQL*Plus sees it, the constructed heredoc end up as:

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

Modifying your procedure slightly, partly to verify the length that is passed in, and partly to check the XML hasn't been corrupted in the process:

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;
/

using that script to handle a large file gives output:

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

PL/SQL procedure successfully completed.

Of course, this slows things down a bit; that ~360k file took about 13 seconds on my system. There may well be faster mechanisms than sed, but the principle still applies.

---

The version of sed on macOS (which needs -E instead of GNU's -r flag) seems to be limited to 255 repetitions of a pattern (via RE_DUMP_MAX, set in limits.h and not as far as I know modifiable at runtime).

You can just use a lower limit:

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

which is actually much faster under Linux too, so not a bad option anyway.

---

After further experimentation on macOS (El Cap, but probably the same for Sierra) and trying to get escaped newlines to work without including literal n or \n in the output, which causes PLS-00103, it seems easier to put an actual newline in:

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