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")

Answer 2

You can try to loop through the input file appending 2.4k chunks at a time, probably:

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

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

exec process_xml(:l_var);

And instead of shell scripting, you can also form the clob in Java, for instance, reading the XML line-by-line, which doesn't have a limit of the size of the variable.

Answer 3

Could you try zipping the file data in bash, then unzipping in PLSQL using utl_compress?

Something like:

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

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